Excel Number Format Shortcuts
August 9, 2016
Number formatting shortcuts, and more, in this week's Excel news. During the summer months I'll send Excel news once every two weeks, so you will get the next news on August 23rd. My Excel website is always open though!
Did you know that you can show custom subtotals in a pivot table? It's a handy way to include key data, without adding more columns.
By default, Excel shows a subtotal with the same function that's used in the column, e.g. Sum or a Count. You can show a different function, or additional ones. Right-click one of the Row labels, such as "East Total" in the screen shot below. Then click Field Settings. Click the Custom button, and click on one or more of the functions, to show those as subtotals. Click OK, to see the results.
There's more information on my website, and a sample file that you can download, to try this space-saving trick.
Remember to attend one of Mynda Treacy's free Excel Dashboard webinars (they end tomorrow), and you'll pick up some amazing tips. Mynda has also opened registration for her highly-rated Excel dashboard course. The discount ends soon -- get 20% off if you sign up by Thursday August 11th.
Also, as a bonus with the dashboard course, I'll send you a free copy of my Data Entry Popup (DVMSP) kit -- just enter DebraD in the Coupon Code box when paying for Mynda's dashboard course. Then, email me your receipt, to let me know that you've signed up. (Note: this bonus is for the full course, not the free webinars)
In the Excel tips article that I linked to below, bonus tip #2 is to use Ctrl+Shift+4 to quickly format the selected numbers as Currency. There are more shortcuts like that one, and the keyboard can help you remember them!
For example, there's a $ on the 4 key, so it's easy to remember that's the currency shortcut. Your keyboard might be slightly different, but here are the number keys on my laptop, and the formattng that they apply with Ctrl+Shift. Try them on your computer, to see what happens.
There are more data entry tips on my website.
Here are a couple of Excel articles I read recently, that you might find useful.
Excel Tips - On the Buffer blog, there's a nice collection of spreadsheet templates and tips. Some of the examples are for Google sheets, but you could send those to Excel (File > Download As > Microsoft Excel). (Level - All)
Cool Excel Charts - Jon Peltier shows how to make a diverging stacked bar chart in Excel, with positive values starting at the centre, and going to the right, and negative at the left. It's interesting to see all the steps Jon went through, to get the chart looking the way he wanted it to. Note: This chart type is available in the Advanced version of Jon's Excel Charting add-in. (Level - Intermediate/Advanced)
Free Microsoft Guides - CORRECTION - Here's a link to the 2016 edition of free eBooks from Microsoft. (Sorry! Last week I gave you the link to the 2015 version.) There are 200+ titles, ranging from Excel keyboard shortcut lists to full books. Search the page for Excel or Power BI titles. (Level - All)
Also, see all my Excel products on my Contextures website.
To get me away from my computer for a while, my husband offered to take me out to lunch. He had heard about a couple of nice spots in the small towns north of us, and we could pick one of those. Well, we never did find those spots, and ended up having lunch at a pizza spot, about 10 minutes from home! It was a lovely drive though, and here's a picture of one of the barns that we passed along the way.
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:48 AM