When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
Here’s the problem, we have four separate tables with the same layout. They hold four different metrics: Actuals, Budget, Forecast and Last Year. A column called Type is used to hold the metric name. We need to populate the Type column.
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
My consulting work recently highlighted a stark contrast in different Excel models and the effort it takes to create or change them. I make some recommendations to make things easier for yourself at the end of this post.
Sometimes Excel surprises me. In this case it sorts in a way I didn’t expect, but in a good way. Thanks to Mr Excel for the tip.
We’ve all heard the term “A month of Sundays” to describe a long time. Well what if you wanted to count how many Sundays between two dates?
If you are using date-based headings in your reporting models please consider using dates in the headings rather than text. I’ll explain why.
Inquire is a new add-in in some versions of Excel 2013 and later versions It is an auditing Add-in that can analyse and report on your Excel files.
The NETWORKDAYS.INTL function was added in Excel 2010. It allows to calculate how may work days between two dates using non-standard weekends. Some countries don’t have Saturday/Sunday weekends.
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.
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones.
I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)
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.
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.