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!

About these ads

6 thoughts on “Video Lesson: Five Powerful Excel Tips

  1. 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?

  2. 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!

Post any comments, feedback or questions below!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s