Excel Pivot Table Shortcuts
May 5, 2020
Group colour banding, pivot table shortcut, 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.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
When you create a named table in Excel, you can colour the alternating rows with one of the built-in Table Styles. But how could you colour alternating groups of information, such as dates? Here's a colour banding technique that I adapted from Chip Pearson's site.
In this example, column D, has the heading TRUE, and this formula, which returns TRUE or FALSE.
=IF(A1=A2,D1,NOT(D1))
Then, conditional formatting was applied to the table:
Another way to separate the groups is with a top border, like I did with this list of dates.
There are more Conditional Formatting examples on my Contextures website.
There are little plus and minus signs in a pivot table, that let you show or hide the details for a specific item, such as the Bars category (shown below), or an entire field, like Category.
Did you know that you can use the mouse scroll wheel to expand and collapse too?
In a pivot table, point to a cell in the Row or Column area.
You'll get different results, based on the cell you're pointing at, so experiment to see how it works.
There are more Pivot Table Collapse/Expand tips on my Contextures website.
Formula Challenge: See my blog post about last month's formula challenge on odd/even week totals. I explained how some of the solutions work, and how we can avoid hard-coded values in the formulas.
Here are a few Excel-related articles that you might find useful or interesting.
Excel Tips: Work faster in Excel, using the tips, shortcuts and new features that Wyn Hopkins will share next Wednesday, May 13th (Level-All)
Spreadsheets: On Reddit, someone asked, "What's the most impressive/most useful spreadsheet you've created for work?", and there are some interesting replies. What's yours? As always, read Reddit at your own discretion! (Level - All)
Excel Skills: Take a look at the free 4-week online Excel course, from the University of Colorado - Everyday Excel Part 2. It covers advanced data management, Excel for financial applications, and more. (Level - Int/Adv)
Excel Humour: See what people said about Excel recently. Are your Excel files like escape rooms?
Also see: My Excel Products || Previous Issues
We had beautiful weather on Sunday, so I spent time weeding the garden, and cleaning it up. Now the weather forecast says we'll have below average temperatures for the next couple of weeks, and probably a snowfall or two. Sigh. That's why do don't do any planting until the Victoria Day weekend.
This plant, a hyacinth I think, is a bit straggly, but the bright pink flowers look nice in a close-up shot. I hope it survives the cold days ahead!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200505ctx.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
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.
Last updated: June 17, 2021 9:23 AM