Excell Changed after Update version 1703 for Windows 10 - Not Happy

[Aaron BARR]

So not sure if I can turn this off or not. Microsoft has done gone and broke which worked fine for years until today! I am beyond frustrated, to say the least.

Basically I do construction estimating and had an excel template set up to import my take off into predetermined cells. I would use the import Text option and the cells would populate with the information and then I would apply the formulas to those cells to give me the correct costs to install the products. No issues, no complications and it worked flawlessly for over 10 years until today after updating to the newest Windows 10 build.

Now when using the Data>From Text/CSV function it wants to create a table with headers and dumb ass orange colors for the table layout. What the hell??? I now have so many extra steps to even get it to look right plus now my formulas, while they do work, has an extra argument inserted which was not there before and sometimes screws it up. 

For Example: Autosum outside of table is =SUM(D3:G4) its fine
After Update and table is now inserted its =SUBTOTAL(109,G2:G21)

Which sometimes screws with the other formulas that have been created to be used in this template. Does anyone know how to disable this stupid freakin table import option when importing data from a CSV file? Cause honestly I am going nuts with this totally stupid update where they broke something that was working fine. Thanks Microsoft, the only good thing about your updates is that it gives me a job as an IT person to fix what you break each and every time like networking. Good job.

Aaron

[#2]

Ok figured out how to fix the issue, although I will say this should not have happened to begin with, or at the very least give me the option to do either or instead of burying it.

To get Data> From Text/CSV to import like before go to File>Options>Data then select the box From Text (Legacy) then press ok. Once you have your file open and are under the data tab, Next press the Get Data>Legacy Wizards from the drop down menu then press >From Text (Legacy) and boom imports like it did previously.

Honestly, if they want to introduce something new please do, just give us the option to either enable it or disable it. Especially when it comes to data import options. I literally almost missed a $30,000 project because of this.

[#3]

After it loads as a table, look at the ribbon and choose convert to range.  It takes it out of the table.  See attached.

Personally, I love working with tables.  There are a lot of positives with using them.  You may want to try them out sometime.

Also, the subtotal formula is only summing whatever is in the table column.  It changes when data is filtered, but unfiltered data is still a complete total.

[#4]

Jean,

Thanks for the reply and yea I tried that and it did nothing. The table still remains. If the formula's worked each and every time it would not be an issue so much but the new import option does not allow the formula to function properly each time.

The table option is really nice and I do use it when it's called for, however, the way I needed it to function was completely changed. That was the primary issue. I was able to figure out how to revert it to the legacy process but still caused me to loose alot of time and like I mentioned, it almost cost me a $30,000 contract in the process.

[#5 · Accepted solution]

Hi Aaron,

To prevent creating the table uncheck in File->Options->Data the option "Prefer the Excel Data model...":

Alternatively, when you import text file

on final stage you most probably see like with checked "Add this data..." and when it creates the table

Just uncheck it

and your data will be imported in "old style", without the table

[#6]

I have to agree that this change to importing data is very, very poor. I was totally unaware of the change and so when I followed the fairly intuitive steps to import the data I found that I could not create a graph - I thought that I had gone mad!!

Whilst I would not claim to be an expert, I thought I was reasonably proficient, but now when I try and create a graph all the data went into the Chart title. I tried selecting the data, moving the highlighted areas, changing the format, design, etc., but I could not seem to get any of the data into the graph. Finally, I just clicked on my .csv file and opened Excel directly and found that I could create the graphs as normal. Then I saw this post and thread and now see how to import the data, but really this has been terribly implemented. What sort of users did you try it out on first? My I suggest that you need to involve a much more diverse group?

I still don't understand why I can't create a graph from my data imported the 'new' way, and I can't see why you have made the change. Very frustrating.

[#7]

Hi Dominic,

I guess i understand you but can't agree.

1) I guess Microsoft has a lot of feedback before introduce any changes to wide range of users, and they hear it. That's user voice, insiders fast ring, insiders slow ring, forums like this. Only after that O365 current channel, then deferred channel, after that not subscribers.

2) The question to shift fully on new connectors or not was discussed in particular on this site few months ago. Final decision is to keep legacy connectors in parallel, you always may use them.

3) G&T (new connectors) is much more powerful tool compare to legacy connectors. Yes, it requires to update a bit the skills to use them, but that's usual story with any new technology.

4) As for your particular case the only thing to do is to remove first (empty) row and after that promote first row as the header, when you may build your graphs without any problems. In general above is done automatically if proper settings are ON in query options. However, you have to have at least some initial knowledge how to work with new queries.

In brief, new queries are not poor things, they are quite powerful things, but we have to know how to cook. Legacy connectors are just small subset of new ones from functionality point of view.

[#8]

Hi Sergei,

Firstly, thanks for your original reply to Aaron which I found very helpful in getting round the problem.

Whilst I appreciate that lots of additional features have been added and software will obviously evolve, I have to disagree with the implementation (not that it has been added, but how it it has been added) of the data import. I would guess that the vast majority of users don't come to these forums until they have a problem, hence a discussion on this site doesn't reach the majority (until they have a problem).

From an engineering perspective there are a lot of us who use equipement that generates csv files and we manipulate these in Excel. The new 'queries' might well be very useful, but it should be clearer to the average user how to implement them is all that I am saying.

If you want to use a cooking analogy it is like putting sugar in the salt pot and not telling anyone, they just wonder why everything tastes wrong! Sugar is good, salt is good, but you want to know which you are using :)

[#9]

Dominic, with this I agree, lack of information is the main issue. People don't go on forums and What's New button is not quite informative even if someone clicks on it after the update.

Perhaps for such particular case that was worth to add the balloon on first using of new connector which informs the legacy connector is still here as well; or something like this.

[#10]

@Aaron BARR Thank you!!! I was so frustrated with the new data import feature. It worked great before and I have no idea why they changed it. I was so relieved when I followed your instructions and it worked like before.