Some Accounting systems (I think SAP is one) downloads negative values with a trailing minus sign. Excel doesn’t recognise this as a number. When you import TXT files, negatives are handled correctly. CSV files don’t.
When you use the SUBTOTAL feature in the Data ribbon tab it automatically inserts subtotals in your list – see blog post on it here.
One problem with this is that is only makes the cell with the word Total bold – it doesn’t make the whole row bold.
If you want the whole row to be bold it isn’t hard to fix.
Do you use the “Filter by Selected Cell’s Value” option? If you do then you will be pleased to know there is a Quick Access Toolbar icon that applies it in one click.
This Excel keyboard shortcut is powerful, but it has a few quirks you need to be aware of. Its quick to use because the A and Ctrl keys are so close together.
I have been recently working with some very large (500,000+ rows) tables. As part of the process I had to filter one Formatted Table, copy it and then paste it in another Formatted Table. Excel would sit there processing for a long time – but I found a technique to speed up the process.
After you copy the filtered list, simply paste it in a blank sheet. This is virtually instantaneous. Then copy that interim list and paste in the other Formatted Table – again almost instantaneous. Two quick pastes is a lot quicker than paste and wait.
In case you didn’t know, when you copy a filtered list, you only copy the visible cells – the filtered ones. The hidden cells are omitted from the copy.
So if you are experiencing delays in the pasting of a filtered list, just use an interim paste and then another copy to speed up your copy and paste.
For more information on Formatted Tables, check out the links below.
I was recently working with a large Formatted Table in excess of 100,000 rows with Power Query.
I was copying in new data to a temporary workings table and then manipulating it with Power Query to get the required output. The data was varying lengths. I found that if you pasted data into a Formatted Table that was a lot longer than the Formatted Table it can take a long time for Excel to process the paste (I am talking tens of thousands of extra rows).
To get around this delay I found that if you first expanded the Formatted Table using Insert Rows, the paste was virtually instantaneous. Inserting the extra rows was also very quick.
So if your Table has sufficient rows the paste is quick, if Excel needs to expand the table to fit the new data, it can be slow for large data sets. Make sure you insert sufficient blank rows to speed up the paste.
You can learn more about Formatted Tables at the two blog posts below. I have also covered the topic in numerous free webinars.
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.