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.
Financial Modeling in Excel for Dummies (Wiley, 2017)
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.
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.
Making Subtotals Bold
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.
- Select the whole range involved.
- Use the grouping button 2 top left corner. See image below.
- Then hold the Alt key down and press the ; (semicolon key) – this selects just the visible cells.
- Then press Ctrl + b to bold it.
- Click another cell to reset the range and you are done.
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.