Excel Number Format Trick
Jan 12, 2016
Pivot Chart fix, number format trick, and more, in this week's Excel news.
Recently, someone asked me why all the columns in their pivot chart were the same colour. The chart showed several months of data, for multiple regions, and every column was blue.
If you want each region or each year to be in a different colour, there is a quick fix. In the pivot table, move the Year or Region field to the column area. That will create a separate series for each item (year or region), and the chart legend will show what the colours represent.
See how it works in a short video, and download my sample file to follow along.
On a worksheet, check boxes can make it easy to enter data -- just click on the box to add or remove the check mark. Each check box can be linked to a cell, so you can use that information in formulas.
However, it can take lots of time to set up those check boxes, if you do it manually. To help you save time, I've added macros to my website, for adding, deleting and linking check boxes, and a few other check box tricks. You can download my sample file, and run the macros when you want to work with check boxes.
Here are a couple of Excel articles I read recently, that you might find useful.
Number Format Trick -- When you format the numbers in a pivot table, they have a nasty habit of reverting to the original format, as soon as you refresh. Bill Jelen shows a simple trick that makes those number formats stick. He also recommends my Pivot Power Add-in, as an easy way to work with pivot tables. (Level - Basic)
Create PDF Reports -- If you need to send reports to multiple
people, Doctor Moxie shared code to do that quickly. The code filters
the list for each person's name, and creates
a PDF file from the filtered data.
NOTE: If you have to send Excel data instead, you can use my sample code to extract each person's data to a separate sheet, using an Advanced Filter. (Level - Intermediate)
More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.
I hope you enjoyed some time to relax over the holidays, and are enjoying a great start to 2016. We had a wonderful Christmas, and the weather was more like September than December. However, a couple of days later, a storm hit, and everything was quickly covered with wet, heavy snow, then freezing rain. We forgot to remove the canopy cover on our back deck, and suffered the consequences! It's down now, and fortunately there was no serious damage.
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser: https://www.contextures.com/newsletter/excelnews2016/20160112ctx.html
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: December 29, 2017 5:12 PM