Contextures

Contextures News 2021-10-05

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.

Compare Years

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.

There are detailed steps and a video on my Contextures site.

compare quarter sales year over 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.

compare region sales year over year

Add Blank Rows

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

  • In A3:A6, type the numbers 1-4
  • Copy those numbers, and paste them below, in A7:A10
  • Next, select the numbers, data, and blank cells (A3:F10)
  • Sort in A-Z order

add blank rows step 1

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.

blank rows inserted quickly

Spreadsheet Day

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.

Excel Articles

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

Safety First

It's Fire Prevention Week here in Canada, so we bought a new fire extinguisher for our kitchen. I was shocked to see that the old one is dated 1986 - it's only a year younger than Excel!

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!

weekly photo

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.

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

Debra Dalgleish

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.

contextures newsletter info

 

Last updated: October 3, 2021 2:02 PM