On Twitter this week, David Napoli asked us – What top Excel tip would you teach in a class? David is planning an Excel class, and has most of the content ready, but wanted ideas from other people who have taught similar courses. What are your top Excel tips for beginners?
David’s Tweet
If you’re on Twitter, you can find David’s account here. He’s a Data Viz consultant and instructor, recovering rocket scientist, and loves cycling near his home in Colorado.
Here is the tweet that David posted, asking for suggestions on Excel tips and topics.
I will be teaching an Excel workshop in a few days. Class content is essentially set, but in pursuit of continuous improvement…
What would be the top #Excel tip / trick I should cover?@ddalgleish @jschwabish @Jon_Peltier @wisevis @AnnKEmery @kpuls
— David Napoli (@Biff_Bruise) June 26, 2019
My Top Excel Tips
In my reply to David, I listed 2 tips:
- Format lists as named Excel Tables
- Save early and save often
See the end of this blog post for the macro that I use to make a quick backup copy, while working on an Excel file.
1. Format lists as named Excel tables
2. Save early and save often— Debra Dalgleish (@ddalgleish) June 26, 2019
Sensible Formats and Names
John MacKintosh had a few great tips in his tweet, including sensible date formats and column names. Those can save you lots of pain and trouble.
Keeping data on a single sheet is important too – don’t create a different sheet for each month or department or whatever!
After named tables, consistent / sensible date formats and column names.
Not splitting data over worksheets when it should be in a single table (e.g. a sheet for each month)
Then, and only then, vlookup()— John MacKintosh (@_johnmackintosh) June 26, 2019
Personalize the Topics
Ann K. Emery had a great tip – personalize your topics, such as “Here’s a thing I wish I knew sooner…” How many times have you finally learned a quicker way to do something that you’ve been doing for years?
Whatever topics you cover, just personalize them — “Here’s the thing I wish I knew sooner…” or “This is the technique that would’ve saved me so much time…” or “I see a lot of people do this the long way, and here’s a great shortcut…”
— Ann K. Emery (@AnnKEmery) June 26, 2019
Formats and Layout
Jovan Lekovic suggested that David show Themes, from the Page Layout tab, and Copy/Paste Special…Formats with charts.
Cell Styles are an overlooked feature too, that can make it quicker and easier to format a workbook.
Oooh! Fun one! I always get great reactions for “Copy/Paste Special… -> Formats” used on graphs. Also “Page Layout -> Themes/Colours/Fonts” is a good quick hack to get away from Excel defaults…
— Jovan Lekovic (@lekovicj) June 26, 2019
Power Query or Not?
The big debate though, was whether or not David should show Power Query (Get and Transform) to Excel beginners. Which side are you on?
Ken Puls and Greg Frazier both suggested Power Query, before David said it was a beginner class.
Very much depends on the course outline. For em I try to get some Power Query in everywhere as that is by far the best way to save time.
— Ken Puls, FCPA, FCMA (@kpuls) June 26, 2019
___________________________
If it is a mixed class touch on power query for the advanced folks. Lots of people stick to a workflow they developed years ago and don’t know how much pq can speed up processing.
— Greg Frazier (@gnfrazier) June 26, 2019
David and Ken thought that Power Query would be better in a later class.
So if it’s truly beginner I’d steer away from Power Query only because they won’t understand the impact. I would definitely introduce them to Tables and pivot tables.
— Ken Puls, FCPA, FCMA (@kpuls) June 26, 2019
___________________
Agreed … as this group will be quite beginner level, I may broach that feature in — hopefully — a follow-up session.
— David Napoli (@Biff_Bruise) June 26, 2019
Structured Data
But Jorge Camoes disagreed, and said that Power Query should be taught from Day 1 in Excel.
I think you should hit them with PowerQuery from day one. PQ is so disruptive of the traditional workflow that you need to tear apart their current Excel skills and rebuild them from the ashes. pic.twitter.com/hy8xhUF2LT
— Jorge Camoes (@wisevis) June 26, 2019
___________________________
Power Query, tables, pivot tables are just instances of/tools for structured data, and that’s what matters, from day one, because that’s the hardest part for Excel users, along with error handling.
— Jorge Camoes (@wisevis) June 26, 2019
And Ken seems to agree with Jorge now!
And now I feel dirty for saying don’t show people Power Query. I think I need to take and internet timeout and think about my life direction.
— Ken Puls, FCPA, FCMA (@kpuls) June 26, 2019
Your Excel Tips for Beginners
So, what are your top tips for Excel beginners? And would you include Power Query basics in your beginner class?
Excel Workbook Backup Macro
Here’s the Excel workbook backup macro that I use almost every day. Copy this macro code, and paste it into a regular code module in a workbook that’s always open, when you use Excel.
NOTE: If you’ve bought a copy of my Excel Tools add-in, you already have this macro. Just click the Make Backup command in the Workbook section of the toolbar
Quick Backup Macro Code
The code below will save a copy of the active workbook, in the same folder. The copy has the same file name and extension, with the date and time added to the end of the name. For example:
MyFileName_20190627_0905.xlsx
Sub QuickBU_SameFolder() Dim wb As Workbook Dim strFile As String Dim strName As String Dim lExt As Long Dim strDir As String Dim strExt As String Dim lPer As Long Dim strStamp As String Set wb = ActiveWorkbook strName = wb.Name strDir = wb.Path & "" strStamp = Format(Now, "_yyyymmdd_HhMm") lPer = InStr(1, UCase(Right(strName, 5)), ".") Select Case lPer Case 1: lExt = 5 Case 2: lExt = 4 Case Else: lExt = 0 End Select If lExt = 0 Then MsgBox "Please save the file and then try again." GoTo exitHandler End If strExt = Right(strName, lExt) strFile = Left(strName, Len(strName) - lExt) ActiveWorkbook.SaveCopyAs _ strDir & strFile & strStamp & strExt MsgBox "The file was saved as " _ & vbCrLf _ & strFile & strStamp & strExt _ & vbCrLf _ & vbCrLf _ & "in the current folder:" _ & vbCrLf _ & strDir exitHandler: Set wb = Nothing Exit Sub End Sub
____________________
Top Excel Tips For Beginners
__________________________
Original source: https://contexturesblog.com/archives/2019/06/27/your-top-excel-tips-for-beginners/