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.
Category Archives: Excel Blog
Automatically Populating a Column with Text in a Formatted Table
A formula trick
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.
Deleting Duplicated Names in Excel
Names scoped at Worksheet level are often duplicates
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.
Some Observations on Structure in Excel
Tables rule
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.
Excel Subtotal Sort Magic
Sorting by subtotals is possible
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.
Count Sundays between two dates
SUMPRODUCT shows it flexibility
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?
Getting Date Headings Right
Formulas rule
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.
Do You Have Excel Inquire?
Its a built-in audit add-in
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 Has a Unique Trick
Its customisable
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.
Trailing Minus For Negatives in CSV vs TXT Files
You can control TXT files much better
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.
Alt Key Shortcuts
Two more
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones.
Export a sheet as a PDF
It takes a few clicks but it is possible
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.)
Excel Filter Trick
Saves a few mouse clicks
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.
Converting a Tricky Date Format
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.
There are lots of Excel blogs out there.
This page lists a lot of the best ones (unfortunately my site isn’t listed yet – but you never know).