Power Query can easily combine data from multiple Excel files. A problem can arise if one or more of the files is open. Power Query will generate an error and the import will fail. The solution involves an old school Excel feature.
Category Archives: Excel Blog
SUM or COUNT based on code length in Excel
SUMPRODUCT solution
Let’s say you have codes that have differing numbers of characters and you need to analyse them based on how many characters a code has. There is one function that can SUM and COUNT based on the number of characters in a code.
Good Excel Ettiquette
Common sense
If you change a formula in someone else’s file you should tell them. In fact there are a few things you should do to make it easier. This can apply to changes to your own files as well.
Another date solution
Formula to the rescue
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.
Creating a table of all combinations in Excel [Video]
Power Query solution
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.
Expand to Fit to One Page in Excel
Macro solution
Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do it.
Unable to Unhide Columns or Rows in Excel
It may not be a protection issue
There are certain situations when you can’t “unhide” columns on the left and/or rows at the top and it is not a sheet protection issue.
Copying a Date Down a Long List in Excel
Two techniques make it quick and easy
There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.
Towards a Simpler IF Function
New function in Excel 2016
Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS.
Handling DR and CR at the end of numbers in Excel
Sorting out debits and credits
Some systems add DR and CR to the end of numbers when they export into Excel. This renders the values useless for normal calculations. You can use data cleansing techniques to remove the characters using formulas or Power Query. There is one function however that can perform calculations on these types of entries.
Hide and Seek with Excel Charts
Hiding rows and columns
Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart.
Excel VBA to Clear Manual Page Breaks
Be careful how you do it
When clearing page breaks in Excel VBA you need to be careful. There is one command that will clear page breaks but it will also affect other print settings.
Dates are Easy with Power Query
Getting the date right and save time
You can create complex functions with Excel to handle dates. But it makes more sense to get your data structured correctly and then you can use simpler date formulas. Power Query allows you to fix your data so that you can use those simpler formulas.
Following Links in Excel
A Name Box trick
Excel has a shortcut that allows you to follow a link to its source but it has a limitation find out how to get around that limitation.
How to SUM Text Numbers in Excel
SUMPRODUCT to the rescue
When data is imported into Excel sometimes the values come in as text rather than values. Most functions can’t perform any calculations with text numbers, but one can. See how easy it is to add up text values.
Power Query Privacy Setting Problem
Excel 2016 solution
Privacy settings allow you to control who sees the Power Query data. There seems to be a bug that remembers your response to a dialog and this ignores any changes to the Privacy settings. Find out the VBA line of code that can fix it.
Old Article
Some articles make a greater impression than others. I was recently asked about a magazine article from August 2011.
Someone had changed jobs and needed to be reminded of the rolling 13 month technique.
The congratulations relate to 15 years of Excel Yourself!
How to Handle a Formatted Table Limitation
Choose from simple or advanced
Formatted Tables are great but there is an issue when it comes to copying formula that use the table names (Structured References). There are two techniques that cope with this limitation.
Declaring Variables in Excel VBA
Using the Dim statement
In a Linkedin Excel Group recently there was a discussion about whether or not you should use the Dim statement to declare your variables. The argument was that you don’t have to and someone had managed to successfully create some code without declaring variables.
Converting Text Numbers and Reversing Signs in Excel
Paste Special to the rescue
Converting multiple text numbers into real numbers or reversing the sign on multiple numbers is easy in Excel if you know how to use Paste Special.