Excel With The Right Tools - Part 2

Note: The following catalog of content covered in this webinar is time stamped to allow you to follow along or skip to sections of the video that are relevant to your questions. You can also search for content on this page using the FIND command in your browser (CTRL + F in Windows, Command + F in Mac OS.)

• Intro/TOC
• Macro Basics
• Groups
• Named Ranges
• Dynamic Named Ranges
• Data Validation
• Conditional Formatting
• Macros and Workbooks/Worksheets
• Creating Ribbon Buttons
• Creating Templates

0:00 – 4:19: Intro/TOC

4:20 – 15:36: Macro Basics

Where macros live: The Visual Basic Editor (VBE) (4:29)

Accessing and navigating the VBE (6:26)

You can open the VBE by pressing the Alt + F11 keys.

The Properties Manager (7:50)

Overview of tools in the VBE (8:30)

Why use macros? Because they save you tons of time! (9:40)

Macro rules (10:00)

• What am I/are we trying to achieve?
• Have an idea of your end goal, but also an outline of how you will get there.
• Is there a function that already exists?
• Commenting/noting your code is imperative.
• Record actions as much as possible. (Record Macro tool).
• Take recorded macros and tweak from there.
• Use your online resources!

Which file format to use (13:06)

The standard file types include:

• XLSX (.xlsx)
• XLS (.xls)
• CSV (.csv)

Standard template types:

• XLTX (.xltx)
• XLT (.xlt)

Macro-enabled file type:

• XLSM (.xlsm)

Macro-Enabled template type:

• XLTM (.xltm)

15:37 – 20:53: Groups

Expanding a group (17:20)

Hiding a group of columns (18:30)

20:54 – 22:09: Data Validation

Creating drop-downs (20:54)

22:10 – 33:05: Named Ranges

Adding an item to a list (25:00)

Assigning a name to a range of list items (25:45)

The Offset command (28:27)

Dynamic Named Ranges (30:42)

Copying the range’s name from the Name Manager (31:20)

Reformatting the range (31:49)

33:06 – 37:32: Conditional Formatting

Creating “Greater than” and “Less than” formatting for specific cells (34:30)

37:33 – 44:11: Macros and Workbooks/Worksheets

Overview of modules (40:40)

Inserting a blank module (40:40)

Running a macro (42:00)

44:12 – 51:54: Creating Ribbon Buttons

How to create ribbon buttons (44:12)

Applying a macro to a file using its ribbon button (48:30)

Question: What if you need to send a spreadsheet to someone but don’t want to reveal your formulas? (49:40)

Answer: You can use Workbook Protection or Sheet Protection, which prevents users from viewing a hidden sheet. Otherwise, you can just send the spreadsheet as a PDF.

51:55 – end: Creating Templates

Creating a macro that clears all cells in an existing file to use as a template (53:20)