Excel Quick Trick - Blank Rows
October 5, 2021
Compare year to year data, quick trick to add blank rows, 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 October 19th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
If you've got 2 years of regional sales data, how would you compare year to year sales in an Excel chart? Usually, I make a pivot table first, to summarize the data. Then, I make a pivot chart based on that.
Compare Quarterly Totals
For example, to compare annual sales per quarter, put Qtr in the pivot table's Rows area, Year in Columns, and Sales in the Values area. Then create a Line chart, and you'll see a separate line for each year.
Compare Years Per Region
Here's another example, that lets you compare year to year sales in each region. In the pivot table, put Region and Year in the Row area, Qtr in Columns, and Sales in Values. Then, make a Stacked Column chart
For details on how to set up this up, go to the Cluster Stack Pivot Chart page on my site.
If you need to insert blank rows between existing data rows, to space things out, here's a quick trick for you.
In this example, there are 4 rows of data, and I want a blank row after each region
The numbers are sorted, and the regions stay in their original order, but now there's a blank row after each region. (After sorting, you can delete those numbers)
You can see this trick in my Cluster Stack Column Chart video, starting at the 0:51 mark.
It's Thanksgiving next Monday, here in Canada, so enjoy the long weekend, if you're celebrating!
The next weekend is special too, because Sunday, October 17th is Spreadsheet Day -- an annual event that I started in 2010. It's growing bigger every year, and you can read more about it on my Spreadsheet Day Blog.
Why October 17th? That was the date, in 1979, when VisiCalc was first released – the first spreadsheet for personal computers.
Here are some Excel-related links that you might find useful or interesting.
Excel Conference: Sign up for the free online conference - Excel Virtually Global 2021. There are 2 full days of sessions, October 12-13, 2021, so I'm sure you'll find something of interest! (Level - All)
Formulas: On the Morning Brew site, Ryan Lasker shows how to analyze spending with Excel formulas. I've never seen number criteria entered like that, have you? (Level - All)
Also see: Previous Newsletter Issues
Were you working in Excel way back then? We've got an old Mac PowerBook that still works, and has Excel 3.0 installed. The earliest workbook I could find was a training file that I created in April 1987. There are screen shots and more details on my Contextures blog.
Anyway, check the dates on your safety equipment, and enjoy Spreadsheet Day!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20211005ctx.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.
Last updated: October 3, 2021 2:02 PM