What do you want to learn in Excel? Complete Topic Guide for our Excel for Business Courses
Microsoft Excel is perhaps the most powerful application used by business professionals today. With so many functions and features, it might be daunting to get started. This guide will give you a brief overview of what Excel can do and which course would be the right fit for you.
Beginner Excel Classes
- Data entry & Calculations: Excel is an extremely powerful tool for calculations. Get started using spreadsheets and performing calculations. Create budgets, track expenses and projects, and more.
- Autofill: Save time by allowing Excel to complete patterns for you.
- Quick Access Toolbar: This allows users to save time by bookmarking commonly-used commands and "pinning" them to the top bar.
- AutoSUM Functions: Learn Excel's key functions, including sum, average, max, min, and count.
- Text Functions: Use a variety of functions to format and correct text. Change the case of text, trim unnecessary spaces, and pull the first or last characters from a cell.
- Formatting: Create polished and presentable work by changing fonts, colors, alignment, and more. Learn shortcuts to format quickly.
- Format painter: Use this tool to copy the format from one cell and quickly apply to other cells.
- Absolute cell references: Make your Excel formulas more dynamic by "locking" cells that remain constant across calculations. For example, when calculating the sales tax for multiple products, "lock" the sales tax rate to avoid retyping the formula.
- Conditional formatting: Set predefined formats based on values. For example, format any cells greater than 100 with a light red fill. Add icons and bars to visually represent the contents of a cell.
- Charts: Visualize and summarize data with dynamic charts. Learn the essentials of creating and formatting charts, including line charts, bar charts, and pie charts. Format the axes, add data labels, change the chart theme, and more.
Intermediate Excel Courses
- Navigation shortcuts: Navigate around the worksheet fluently without using the mouse using CTRL, SHIFT, HOME, END and the arrow keys. Move quickly between tabs.
- Navigation within cells: Navigate, highlight, and edit data with cells. These shortcuts will help you efficiently work with text across all the major Microsoft applications.
- Split and Join text: Split text to multiple columns with Text to Columns, and join text with the CONCAT function (or by using "&").
- Dropdown menus: Create drop-down menus and restrict unwanted data entry with Data Validation. Create custom error messages and alerts.
- Sort & Filter: Within Excel databases or tables, sort alphabetically, largest to smallest, or with a custom sort. Filter the data to show records that meet specific criteria. For example, show employees whose salaries are greater than $50,000 and who work in the New Jersey office.
- VLOOKUP: Retrieve a value from a table based on the value in the first column. For example, retrieve the name associated with customer ID 74 (where the customer ID is in the first column.
- IF Statements: Write formulas that show different outputs based on a condition. For example, if the employee worked less than 40 hours, show "No overtime", and if the employee worked more than 40 hours, show the number of hours overtime worked (hours worked minus 40).
- SUMIFS: Sum values that meet one or more conditions. For example, the total employee salaries who work in accounting and in the Northeast region.
- COUNTIFS: Similar to SUMIFS, COUNTIFS will count the number of items that meet one or more criteria. For example, the number of employees who work in the finance department.
- Pivot Tables: Summarize and visualize data with Pivot Tables. For example, consider an Excel table that shows a log of t-shirt sales, with each record containing a price, size, sales rep, sale date, and customer. With Pivot Tables, you can quickly show the total amount of sales by size, the number of sales made by a specific sales rep, the sales by month or quarter, and so much more.
Advanced Excel Training
- Advanced Cell Locking (or Anchoring): In beginner Excel, we reviewed how to perform absolute cell references where the row and column are locked. In advanced Excel, we learn mixed cell references, which locks the row or column.
- Hot Keys: Work in Excel without using the mouse by activating Hot Keys. Essentially, any command in Excel can be reached by a series of keyboard strokes. Learning these shortcuts will substantially expedite your workflow. For example, center align text with ALT + H + AC.
- Cell Auditing: Review your formulas for errors, and trace dependents and precedents. These tools allow users to track their formulas and spot errors.
- Conditional formatting with formulas: In the introductory Excel course, we reviewed basic conditional formatting. In advanced Excel, we make our conditional formatting more powerful by formatting cells based on a formula. For example, format cells that are greater than the value in cell C35 with a light red fill.
- Nested IF Statements: In intermediate Excel, we cover IF statements, which allow for two options (value if True or value if False). By using Nested IFs, or an IF statement within another IF statement, we can create formulas that allow for multiple options. For example, if A1=1, then show "Base", if A1=2, show "High", if A1=3 show "Low."
- IF Statements with AND/OR: This allows us to expand the capabilities of the logical test, the first input in the IF function. For example, for an employee to get a raise, they have to have a 4.5-star review AND have taught more than 200 students. If they are entitled to a raise, the raise should be 5% of their salary, and if not, the raise is equal to 0. The AND function allows us to specify that two or more conditions need to be met, while OR tell Excel that only one of two or more need to be satisfied.
- Goal Seek: This is a powerful calculation tool that allows the user to change an input to achieve the desired output. For example, change the score on the final exam so that the student's cumulative average is equal to 90 (in Goal Seek, it would be: set the Average to 90 by changing the final exam score.)
- Data Tables: With Data Tables, you can sensitize, or change, one or two inputs and show how that impacts a specified output.
- Pivot Charts: Once the data is summarized with Pivot Tables, we can use that summary to create charts.
- VLOOKUP-MATCH: Make the VLOOKUP function more flexible and dynamic by adding the MATCH function for the Column input.
- INDEX-MATCH: Create a more flexible lookup function by using INDEX. This allows users to "lookup backward" which the VLOOKUP cannot do.
- INDEX- Double MATCH: Use two MATCH's in the INDEX function to create the most powerful and dynamic formula to extract data from tables.
- Recording Macros: Record macros to automate a series of actions that are frequently repeated.
