Speed Challenge Day 5

Connecting the Dots


This is where it all comes together! In this video, we'll use Power Query to retrieve and prepare data, Tables to store the data, and a PivotTable to summarize the data. It has all been leading up to this 🙂

Moss Creates One-Click Report Refresh

When we combine the tools we learned during the Challenge, including Tables, Power Query, and PivotTables, we are able to create workbooks that can be easily refreshed. My student Moss combined these tools and others he learned into his operational reports. He said "Now, each month when we have new data available, I simply hit refresh and it performs all those steps for me." See what he did here.


If you'd like to give it a try after watching the video, download the Excel file ChallengeConclusion.xlsx and the related data file ChallengeData1.csv.

Try It

Exercise 1

  • Data > Get Data > From File > From Text/CSV.
    • Browse to ChallengeData1.csv
    • Click Transform Data
  • We have several options for getting the department name from the Dept column. One option is:
    • Select the Dept column.
    • Transform > Extract > Text After Delimiter.
    • In the Delimiter field, enter a colon (:).
    • Click OK.
  • One option for getting the account name from the Acct column:
    • Select the Acct column.
    • Transform > Extract > Text After Delimiter.
    • Enter a colon and click OK.
  • Send the data back to Excel.
    • Home > Close & Load > Close & Load To ... Table an an Existing worksheet.
    • Select the Data worksheet.

Exercise 2

  • Create the PivotTable.
    • Select any cell in the data table (on the Data worksheet).
    • Insert > PivotTable.
    • In the resulting dialog, select Existing worksheet and browse to the Report worksheet. Select a cell on the worksheet that represents the upper-left corner of the report.
  • Define the structure of the PivotTable report.
    • Drag the Account field into the Rows layout area.
    • Drag the Amount field into the Values layout area.
    • Drag the Period field into the Columns layout area.
  • Format the values by right-clicking any numeric value in the PivotTable and selecting Number format.
    • Select any desired format.

Exercise 3

  • Note the total of the PivotTable
  • Navigate to the Data Table and add a Total Row.
    • Select any cell in the Table.
    • Table > Total Row.
  • The Table total should agree to the PivotTable, confirming all data made it into the report.
  • Yay ... you did it 🙂

Continue Learning!

Thanks for joining the Excel Speed Challenge!! I hope the features we covered will help save you time!

In addition to these features, Excel is full of many more that will help you save a ton of time. If you'd like to explore them, I'd love to be your guide.

In my training programs, we not only uncover other time-saving features, we also dig deeper into Power Query and PivotTables.

If you are interested in learning more about our formal training programs:

We also have several free resources you can use to continue learning Excel:

  • Weekly Excel newsletter (we'll automatically email you our Excel tips and new YouTube videos)
  • Excel university blog
  • Published articles
  • YouTube channel

Spread the Word

If you think one of your friends or colleagues would enjoy taking the Excel Speed Challenge, please forward the link below so they can register. Thank you!


Challenge Survey

Strongly disagreeDisagreeNeutralAgreeStrongly agree
If you enjoyed the Challenge, PLEASE let us know why!