Summary
Highlights
Enhance data filtering in pivot tables with slicers. Learn to add a slicer to filter pivot table data by 'City,' making data exploration more visual and interactive.
Use Excel's error checking feature to identify and correct formula errors, such as typos. The demonstration shows how to quickly fix a common formula error.
Go beyond basic subtotals by adding two subtotals to the same worksheet without overwriting previous calculations. This requires sorting the data and unchecking 'Replace current subtotals'.
Master the AVERAGEIFS function to calculate averages based on multiple criteria. The example finds the average sales revenue for 'full restaurants' located in 'Montreal'.
Learn how to use the Fill Series feature to automatically fill a range with a pattern, such as increasing values by 100. This section also covers how to use 'Fill Without Formatting' to maintain existing cell formatting after using the fill handle.
Discover how to use the HLOOKUP function to retrieve values from a horizontal lookup table. The example demonstrates looking up specific telephone numbers based on exam types, emphasizing the use of 'FALSE' for an exact match and absolute references for the table array.
Automate repetitive tasks by creating a macro. This part guides you through recording a macro to insert a header, assigning a shortcut key (Ctrl+H), and storing it within the current workbook.
Learn to group numerical data in a pivot table. The example shows how to group 'Price' values into ranges (e.g., 1-100 and 101-200) for better data analysis.
Optimize Excel's performance by changing the formula calculation option from automatic to manual. This prevents the workbook from recalculating every formula with each change, which is useful for large datasets.
Understand how to enable or disable macro settings for security or performance. This section demonstrates how to disable all macros without notification via the Trust Center settings.
Protect your workbook structure from unauthorized changes by setting a password. The tutorial shows how to configure the workbook so users need a password ('Wendys') to make structural modifications.
Apply advanced conditional formatting rules. This section instructs on clearing existing rules, then creating a custom rule to format text ('Every day') as 'Bold Italic' with no fill color.
Utilize the WEEKDAY function to determine the day of the week for a given date. The tutorial also shows how to format cells to display the actual day name instead of a numerical value.
Craft a combo chart that combines different chart types (e.g., area and line charts) on the same axis. The example illustrates creating a chart for 'salary' and 'revenue generated'.
Efficiently modify multiple chart elements to meet specific formatting requirements. This part demonstrates changing chart layout, style, colors, and title rapidly, a crucial skill for expert users.