Excel With The Right Tools

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
• Intro to Excel
• Terminology
• What Does It Have to Offer? (Capabilities)
• Examples
• Best Practices
• Hands On
• Tools to Speed Up Everyday Use
• Stepping Into Macros

0:00 – 4:05: Intro/TOC

4:06 – 20:49: Intro to Excel

Terminology (4:06)

• Workbook: The entire Excel file
• Worksheet: Component of a workbook
• Cells: Component of a worksheet
• Columns/rows: Vertical and horizontal banks of cells
• Ribbon: Tools at the top
• Cell reference: Particular cell – combination of column and row
• Cell range: Larger set of cells
• Formula: Equation applied to cells (starts with an equals sign)
• Formula bar: Location where you can see the formula
• Function: Button in formula bar that allows you to see a number of pre-existing formulas and other features
• Macro: A recorded combination of functions and processes that you can save and repeat to perform specific tasks

What Does It Have to Offer? (Capabilities) (9:18)

• Spreadsheet layout
• Filter lists
• Conditional format
• Create data validations
• Perform simple to complex calculations
• Produce charts and graphs
• Record and code functions (macros) to perform tasks
• Create dialogs/ forms to run recorded macros
• Newer data entry options – insert data from pictures

Examples (11:54)

Natural water management budget tracker (12:08)

Water use reduction table (12:38)

Shade calculation (13:05)

Summaries and reports of zones, etc. (13:30)

Mobile features (13:55)

Best Practices (15:35)

Formulas:

• Break them down
• Choose clarity over looks
• Avoid using repetitive formulas
• Avoid fixed numbers
• Use caution with macros – look for solutions offered within existing tool sets first

Formatting/layout:

• Keep it simple
• Preparation of a good spreadsheet
• Think about the order
• Don’t merge cells
• Save styling until the end
• Keep styling consistent
• Don’t be afraid to note your cells

iOS and Android versions (19:50)

20:50 – end: Hands On

Sending a Watering Schedule and Plant Schedule to Excel from CAD/Land F/X (21:00)

Tools to Speed Up Everyday Use (22:50)

Creating several worksheets starting with the same content (23:40)

Editing content manually(25:15)

Setting column widths automatically by double-clicking (27:00)

Creating matching column widths (27:25)

Setting a column width manually (27:40)

Selecting all and setting all column widths automatically (28:20)

Manipulating data in the same cell across multiple worksheets (30:35)

Creating a new formula and using a lookup value (32:35)

Using the F4 key or dollar signs ($) to lock columns and rows (36:30)

Commenting on specific items or making a key (41:00)

Making mass changes across different selections (43:00)

Copying a spreadsheet into an existing workbook without having to change the existing equations (i.e., “data dump”) (43:20)

Stepping Into Macros (45:15)

Customizing the ribbon (50:50)

Creating a macro for formatting cells (51:30)

Setting the tasks for a macro to perform (53:00)

Viewing and editing the tasks set for a macro (54:00)

Running the macro (56:10)

Creating a macro to clear formatting (57:00)

Assigning a macro to an object to create a button (58:30)

Copying and pasting a spreadsheet into CAD using the Paste Special command (1:00:20)

Question: Is it possible to drag and drop an entire Excel file into CAD? (1:02:08)

Answer: It’s possible but not recommended. Because of all the different worksheets and other components, it’s better to just copy and paste.