July 5, 2016
Hi fellow Excel Addict,
Thanks to everyone who wished me luck for the Kelligrews Soiree 8K race last Saturday. It was a perfect morning for running...cool and no wind. I've run this same race three times in the past five years and this year I had a personal best time of 39:05.
Now I've got two weeks to prepare for the Tely 10. This is the race I was training for in 2014 when I injured my knee. If all goes well, this will be my first Tely since 2013. I've already run 12K a couple of times in the past month, so I think I'm almost ready.
I hope you'll find today's tip on quickly formatting subtotal rows helpful. Please feel free to share it with anyone you think could use a little more Excel smarts.
If you missed last Thursday's Excel in Minutes' tip, 'Quickly Merge Multiple Ranges of Cells Across Or Down', you can read it here now.
Wishing you a great week end keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
Make Your Subtotal Rows REALLY Stand Out
When you insert subtotals (Data, Subtotal) in a list, it is difficult to distinguish the subtotal rows from all the other rows in the list.
Here's a quick way to make them REALLY stand out.
1) Once your data has been subtotaled, click the small outline button (2) on the top left corner of your worksheet to collapse your list and show only the subtotals.
2) Drag to highlight your subtotal rows. Even though the data rows are not visible, they are still included with the cells you have selected;
3) To select only the visible (unhidden) cells, hold down the Alt key and press the semicolon (;) key (i.e. ALT+;);
4) Now that only the cells in the subtotal rows are selected, make them stand out by changing any of their formatting such as fill, font, borders, row height, vertical alignment, etc... If you add a top border to your subtotal rows, you will also need to add Inside Horizontal borders as well to account for the Grand Total row which is immediately below the last subtotal and therefore doesn't have a top border;
4) Finally, click the Outline 3 button to show all of your data.
Now, doesn't that looks much better?
You can see that the formatting has been applied only to the subtotal rows.
Remember that if you remove the subtotals, the formatting will be lost when the subtotal rows are removed and you will need to apply it again.
If the Outline controls are not visible on your screen, click the File, Options and click the Advanced tab. Scroll down to 'Display options for this worksheet' and check the option for 'Show outline symbols if an outline is applied'.
| 'Spreadsheets Tips
Excel Addict' and 'Excel in Seconds' are publications of
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630