1) Dynamic Named Ranges
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 (ozgrid.com/Excel/DynamicRanges.htm). Definitely check out that link for additional information about how to use the functionality!
2) Find and Select – Go To Special…
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…
3) 3D Formulas & Editing – Formula Across Multiple Sheets
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).
4) Text to Columns
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.
5) Customizing Ribbon Menu Tabs & Menu Groups
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!