Excel With The Right Tools - Part 2
August 9, 2019
Presented by: Jake Lott
Length: 1:05:28
Now that we've gone over the basics of what MS Excel can do for you, join us for a more detailed look into the capabilities of this powerful application. We'll show you how to create named ranges, set up templates and reusable macros, develop formulas to augment your plant and irrigation schedules generated with Land F/X, and even assign your newly created macros to your ribbon for easy access.
Webinar Contents:
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)
Contact
- Land F/X
Our software tailors AutoCAD®, Revit®, SketchUp®, and Rhino® to the needs of landscape architects, irrigation designers, and other professionals. We automate your most tedious tasks and ensure accuracy, giving you more time to design.