Video Lesson: Five Powerful Excel Tips

Make sure to download the most recent Master Workbook to follow along!

I’ve noted the time that each section starts in the video, but you may want to let it load completely first as I’ve had errors skipping around early.

1) Dynamic Named Ranges [0:13]

As a follow-up to the Named Ranges video, I go through some examples which have been partially modified from the Dynamic Named Ranges post on OzGrid.  Definitely check out that link for additional information about how to use the functionality!

Keyboard Shortcuts: New Named Range is ALT + MMD, and the Name Manager is ALT + MN.

To check what your named range is currently referencing, hit F5 (for Go To) and type in the dynamic named range into the reference field, and it will highlight that range.  This is useful since dynamic named ranges do not appear in the Name Box.

2) Find and Select -> Go To Special… [5:28]

An excellent feature in Excel is the Find and Select functionality, which includes the ability to select a certain subset of a range.  For example, you can easily select all cells within a range that contain: Blanks / Formulas / Visible Cells / Constants / Conditional Formats and a whole lot more…

Keyboard Shortcuts: For ‘Go To Special’, hit F5 and then ALT + S.

3) 3D Formulas & Editing – Formulas Across Multiple Sheets [7:32]

Do you have information that is spread across multiple sheets in the same format? For example, you may have the same sheet template with different monthly/weekly/daily information.  Using ‘3D formulas’ you could sum a certain cell or range across multiple sheets by indicating the starting and ending sheets (and that formula will grab everything in between as well).

Keyboard Shortcuts: Selecting multiple tabs is CTRL + SHIFT + Pg Up or Pg Dn.

4) Text to Columns [11:24]

Have a lot of data that’s jammed into a single cell?  You can use Text to Columns to separate that cell’s contents into multiple columns based on the location of spaces, commas, periods or other character(s) that you specify.  An example would be splitting a cell with full names into a first name and last name column based on the space.

Keyboard Shortcuts: ALT + DE brings up the Text to Columns option.

5) Customizing Ribbon Menu Tabs & Menu Groups [13:08]

There is a lot of customization that you can build into Excel so that you have custom menu tabs & groupings to help you get to the Excel features and functionality that you use most.

You may have noticed my ‘Ben’ grouping in previous videos on my ‘Home’ tab which has PivotTable, Name Manager, Freeze Panes, Data Validation, Conditional Formatting and other features that I use very frequently.  In this video I will show you how you can do the same!

This entry was posted in Quick Tips, Workbook/Reference Lessons and tagged , , , , , , , . Bookmark the permalink.

10 Responses to Video Lesson: Five Powerful Excel Tips

  1. Anonymous says:


    Love all the explanations! The videos are very well put together and super helpful!!

    I’m not sure how to use the Find and Select -> Go To Special to accomplish the following task: I have a long list of items and I filtered them to only show me some of them. In a currently non-blank column in the list, I’d like to clear out the data for the items that the filter is currently showing me. Easy to do.

    But next, I want to put a 1 in the top row, a 2 in the next row and so on, in that column. Keep in mind that only some of the rows from the list are currently showing.

    Any ideas?

  2. Anonymous says:

    I don’t understand the offset function and you didn’t give an example or go into depth on how how to fill out the function.

  3. Anonymous says:

    I cannot find the customise ribbon with the right click it only give customise quick access toolbar

    • That’s a common mistake but all you have to do is make sure you’re right clicking in / underneath the very top where the Home, Insert, etc tabs are located. If you’re clicking in the ribbon area, the correct option should show up.

      Let me know if you’re still having issues.



  4. Hey,
    First of great work on you tutorials. You’ve helped me a great deal so far.

    I’ve noticied that I cannot find a lot of features like ‘Customizing Ribbon Menu Tabs & Menu Groups’ in my version of excel (I’m using the Excel version of University 365 for mac). Do you have any tips for finding features in newer or older versions?

  5. Joe says:

    Hello Sir,

    Isn’t easier to use the Data Validation (List) function regarding the Dynamic Named Ranges?

  6. Olu says:

    Thank you so very much! May God richly reward and bless you in Jesus name!!

  7. Hilary J says:

    I’m liking your video lessons so far but I do have a suggestion: when you post the Master Workbook, can you please post a version we can download that does not have the demonstrated tasks already done? It would be better to follow along with the videos and do the tasks at the same time; as it is a user has to figure out how to UNdo the tasks in order to DO them for the first time. As with this video: I would like to practice the text to columns task as I watch, but the workbook page has the text already separated. Granted, I could key in some data that I make up and try it that way, but that would undercut the purpose of providing a workbook with the same examples you use. I am already an experienced Excel user, but even for me I would love to learn something new at the same pace as the video lessons. Thanks for reading!

Comments are closed.