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!