Your Top Excel Tips For Beginners

Top Excel Tips for Beginners

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.

My Top Excel Tips

In my reply to David, I listed 2 tips:

  1. Format lists as named Excel Tables
  2. 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.

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!

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?

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.

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.

___________________________

David and Ken thought that Power Query would be better in a later class.

___________________

Structured Data

But Jorge Camoes disagreed, and said that Power Query should be taught from Day 1 in Excel.

___________________________

And Ken seems to agree with Jorge now!

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

Top Excel Tips For Beginners

__________________________

3 thoughts on “Your Top Excel Tips For Beginners”

  1. My top three would be…
    1. Keyboard shortcuts (learn some)
    2. Using INDEX/MATCH instead of VLOOKUP, AGGREGATE instead of SUBTOTAL, and IFS instead of IF (as in SUMIFS/COUNTIFS instead of SUMIF/COUNTIF)
    3. Using Tables

    My rationale in #2 is that they’re all better or more robust alternatives, so they might as well be a user’s “default” preferences. And I would at least cover something about Power Query (Get and Transform). It’s a bit new/different to the Excel family, and people are often hesitant to even experiment with such things until someone has given them the first nudge in a positive direction.

    Reply

  2. All of the above, especially tables.
    1) Learn how to use and understand Dynamic ranges (ctrl-F3)
    2) Get to know the OFFSET function – single cell and range form. A lot of people shun it but it can be your best friend for working with blocks of data
    3) Add common complex formulae to the Custom Dictionary replacements, it saves time and avoids misplaced brackets
    e.g. I added OMRC (capitals) to my custom dictionary and when i type that and a space it pops up
    =OFFSET( DblClk_Anchor, Match(DblClk_Value, DblClk_Range,0), Match(DblClk_Value, DblClk_Range,0))
    then I can double click the names and put the ranges in
    Two other useful ones
    First Non-Zero – FNZ – IFERROR(INDEX(MATCH(1,–( MyRange 0),),),0)
    Last non zero – LNZ – LOOKUP(2,1/( MyRange 0),COLUMN( MyRange ))-COLUMN(OFFSET( MyRange ,0,-1,1,1))
    4) Shortcuts. Ctrl-z is a favourite.
    5) Enable the ‘Developer’ tab, if only for the controls such as combo box

    Reply

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.