Contextures News 2021-01-12

Excel Data Bars in Pivot Table

January 12, 2021

Quick division, pivot table data bars, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Thank you for reading the news, and you'll get the next email on January 26th.

• Quick Division
• Data Bars
• Excel Articles
• Weekly Photo

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Quick Division

My daughter, Sarah, just started making videos for me, so I gave her an easy one to start with -- show how to do quick division with Excel's Paste Special command.

To see Sarah's video, and to get the sample file, go to the Change Numbers With Paste Special page on my Contextures site.

Here are the steps for dividing a list of numbers by 1000:

• First, in a blank cell, enter the amount by which you want to divide.
• Next, copy the cell where you entered the division amount.
• Then, select the cells with the numbers you want to divide.
• Right-click one of the selected cells, and click Paste Special
• In the Paste Special window, for Operation, click Divide
• Click OK to apply the change

Data Bars

For pivot tables with a one or two number columns, conditional formatting data bars can be a great way to show the results. I made a video to show how to set up the data bars, and then adjust the settings, so new data shows the data bars too.

To see my new video, and to get the sample file, go to the Pivot Table Conditional Formatting page on my Contextures site.

To add data bars quickly, use the Ribbon command:

1. In the pivot table, select the monthly sales amounts, in cells B3:B8 (don't select the Total)
2. On the Ribbon's Home tab, click Conditional Formatting
3. Click Data Bars, and click one of the Data Bar styles

Then, to make sure new data get the conditional formatting, go into Conditional Formatting Rules, and make this change for the Data Bar rule:

• In the Apply Rule To section, the first option is selected (Selected Cells)
• Select the 3rd option, All Cells Showing "Sum of Sales" Values for "YrMth"
• Click OK, twice, to apply the revised rule

Excel Articles

Here are a couple of Excel-related articles that you might find useful or interesting.

Charts: On the Junk Charts blog, Kaiser Fung looks back at his top posts from 2020, and a few others, like this pie chart makeover. (Level - All)

Tables: Jon Acampora shows 10 shortcuts for working with Excel Tables. Do you use all of them? My favourite it dragging the heading cell to move a table column. (Level - All)

Also see: My Excel Products || Previous Issues

City Streets

Here's one of the things I did for fun over the holidays. The streets from my home town are at the left. It's quite different from the city where we live now, shown at the right!

To try this for yourself, use the city roads page on GitHub. The page is the plainest thing I've ever seen on the internet -- just 2 lines of text and a box to type in.

Type a city name and province/state, then press Enter. For some cities, you might have to choose from a list of suggestons, after you enter the name.

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210112ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd @contextures.com
ctxdebra @gmail.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.