This was an exceptional session on how to effectively use Excel. David Brown, dbrownconsulting, explained that he learned Excel by teaching others how to solve their problems. Through his experience, he became a master at Excel—a wizard in my mind.
Throughout his presentation, he effectively used Poll Everywhere. During his presentation, he shared a number of key observations that will help you get the most out of Excel.
Two Sentences to Unlock Excel
1. If it is not in a cell in Excel, it is not Excel.
2. A format can never change what is in a cell, it changes what you see.
An important part about Excel is that it gives you the back end or the programming side of the application. It’s your responsibility to create the front end or what you see.
7 Golden Rules for Data
This and the next section are perhaps the essences of his whole presentation. It is absolutely essential to get this right.
- Only one row of headings
- No empty rows
- No empty columns
- No totals or subtotals
- Dates in a single column
- Every unique data has its own column
- No random text around the data and no merge cells
Framework for Automating Training Needs Analysis
When working with an Excel workbook, you need only three sheets:
- Datasheet
- Control sheet
- Report sheet
Build Amazing Visuals With Zero Hassle
He emphasized that when you create a visual, create one that uses the least space but says the most.
Pivot Tables for Quick ROI Analysis
Get to know how to use pivot tables. Set up your data in a datasheet and build your pivot tables on the control sheet.
Banished Copy and Paste
Learn to collect your formatted data to data sheets. Don’t copy and paste from bad reports, try to get the raw data. You can pull your information together using Power Query, which allows you to consolidate all your data sources into one datasheet. The added bonus is that it can refresh on a schedule. You can have your data workbooks collecting in a folder and with Power Query, you can pull all those data workbooks as well as any new data workbooks into your spreadsheet for processing.
Unfortunately, we ran out of time. But based on what I learned, I can definitely make an improvement to the Excel sheets that I create. I will go out far and wide to help colleagues fix their Excel spreadsheets.
