Excel - How to Become a Power User
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: Mike Thomas
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.0, ATAOP 1.0, ATATX 1.0, CPE 2.0
-
Few people learn or use Excel for fun. In fact, many people see it as a necessary evil. So if you have to use it, why not learn to use it faster and more effectively? How often have you found yourself in Excel thinking, “there must be a better and faster way to do this?”
In this session you’ll learn a handful of powerful productivity-boosting tips and secrets that will speed up your work in Excel, save you hours of time and remove some of those hair-tearing-out moments! The training will be delivered using Excel 2016 for Windows although much of the functionality is available in earlier versions of Excel on both Windows and Mac.
Among the topics covered are:
- Data entry shortcuts including Transpose, AutoFill and Flash Fill
- How to copy and paste with a single mouse-click
- Automating cell formatting with Conditional Formatting
- Taking Find and Replace to the next level
- Using data across worksheets and workbooks
- Creating and managing dynamic ranges
- Using protection to "bullet-proof" your spreadsheets
- Using the power of The Cloud to collaborate and increase efficiency
- Working in a mobile world: Excel on an iPad
- Pivot Table power tips
- Managing external data
- Introduction
- Data Entry Shortcuts 00:02:25
- Conditional Formatting 00:02:39
- Dynamic Ranges 00:02:50
- Pivot Table 00:02:57
- Protection 00:03:04
- Linking Worksheets 00:03:12
- Functions 00:03:16
- Data Entry Shortcuts 00:04:49
- Flash Fill 00:07:15
- Transpose 00:15:40
- Conditional Formatting 00:21:22
- Conditional Formatting (cont’d) 00:29:37
- Named Ranges 00:36:50
- Absolute Cell Reference Alternative 00:41:34
- Part Absolute - Dollar Signs 00:46:31
- Tables 00:52:14
- Pivot Table 01:05:33
- Lookup - VLOOKUP - False 01:22:40
- Lookup - VLOOKUP - True 01:34:43
- Presentation Closing 01:39:32
- Absolute Reference 00:41:37
- Cell Reference 00:41:37
- Conditional Formatting 00:02:39, 00:21:22
- Dynamic Range 00:02:52
- Filter 00:58:27
- Flash Fill 00:07:15, 00:12:26
- Formula 0:49:29
- Name Box 00:39:15
- Named Ranges 00:36:50
- Pivot Table 00:02:58, 01:05:39
- Range Names 00:02:53
- SUBTOTAL 01:02:35
- SUM 01:02:26
- Table Array 01:36:03
- Transpose 00:15:40
- VLOOKUP 001:22:46
Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.
Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
Dynamic Range: Dynamic ranges are also known as expanding ranges - they automatically expand and contract to accommodate new or deleted data.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Flash Fill: Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.
Formula: A formula is an expression which calculates the value of a cell.
Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.
Named Range : A descriptive name — such as Jan_Sales or June_Precip — that is attached to a specific cell or range of cells in a worksheet or workbook. Named ranges make it easier to use and identify data when creating charts, and in formulas.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Range Names: A user-defined identifier for a cell or block of cells. Range names simplify formula writing and auditing, as the words SalesTax are readily identifiable within a formula as opposed to $B$1. Range names cannot contain spaces; must begin with a letter; be no longer than 255 characters in length; and can be comprised of only letters, numbers, underscores, periods, and slashes.
SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet
TRANSPOSE: The Microsoft Excel TRANSPOSE function returns a transposed range of cells. For example, a horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a horizontal range of cells is entered as a parameter.
Table Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.
VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.
