Lesson 1. Custom Views, Outlines, and Templates in Excel
To streamline spreadsheet management, Excel offers templates and outlines to simplify data presentation, making it easier to share and emphasize important sections. Custom views can be created to easily hide unnecessary data ranges, improving focus while working.
Lesson 2. Math Functions: Simplifying Excel Calculations
Excel's functions such as ROUND and SUMIF elevate data processing by providing specialized calculations that cater to specific scenarios like rounding to desired precision and conditional summing. Mastering these functions transforms you into a proficient data analyst, making Excel an invaluable tool.
Lesson 3. Error Types Unveiled: Excel's Toolset for Troubleshooting
Use Excel's ISNUMBER to differentiate numbers from text, allowing for dynamic calculations based on cell content. For distinguishing text, ISTEXT and ISNONTEXT offer simple solutions for checking if cells contain text or non-textual data.
Lesson 4. Excel Data Lookups Made Simple
VLOOKUP is ideal when you need to find specific data in a vertical column by referencing adjacent cells. It's particularly useful for dynamic results that auto-update when datasets change.
Lesson 5. IF Function Unpacked: Building Powerful Excel Formulas with Conditions
Lesson Summary 1: The IF function in Excel evaluates conditions and displays results based on whether they are true or false, allowing for complex calculations with nested statements and logical operators. Common issues include logical errors and adjusting conditions for greater accuracy, such as using 'greater than or equal to' rather than 'greater than' alone to prevent bugs.
Lesson 6. Exploring Excel's Text Functions: A Comprehensive Guide
Excel text functions are integral for efficient data formatting, including FIND and SUBSTITUTE for text searches and replacements and LEFT, RIGHT, and MID for substring extractions. UPPER, LOWER, and PROPER functions standardize text case, enhancing readability and consistency across spreadsheets.
Lesson 7. Color-Coding with Logic in Excel
By leveraging conditional formatting, Excel users can personalize cell styling based on predefined data conditions, such as numeric comparisons or text occurrences. This enables efficient data analysis and visually appealing spreadsheets without manual formatting.
Lesson 8. From Rows to Visuals: A Journey with Sparklines
Sparklines in Excel enhance data visualization by embedding small, dynamic graphs within individual cells to show trends quickly. Whether using line, column, or win-loss types, they adapt effortlessly to data updates, offering an efficient means of presentation.
Lesson 9. Linking to External Excel Workbooks
Excel's 'Get and Transform' feature supports retrieving data from various sources, enabling users to easily integrate and analyze data across distinct worksheets and workbooks.
Lesson 10. Beyond the Workbook: Exploring Importing Techniques for Various Data Sources
Importing data into Excel from various sources, including SQL databases and web pages, is streamlined through the 'Get & Transform' feature, which supports diverse data formats. While SQL databases offer easily integrated structured data, web page data likely requires meticulous cleaning and formatting to fit analytical needs.
Lesson 11. Exploring the Dynamic World of Pivot Tables in Excel
Excel's pivot tables represent a shift in data analysis, providing a user-friendly method for restructuring and summarizing data from multiple sources. By accommodating various aggregation and grouping settings, pivot tables empower users to gain insights efficiently without manual data manipulation.
Lesson 12. Innovative Uses of Pivot Tables for Data Analysis
Transform large data sets into comprehensive reports by leveraging pivot table capabilities, including advanced calculated fields and Excel functions. Simplify your filtering tasks with slicers and timelines, while keeping your data up-to-date through easy refresh options.
Lesson 13. Creating a Pivot Chart
Excel's pivot charts transform data into a visually engaging format, making complex datasets more accessible to third-party viewers. Through customizable elements and filters, these charts enhance data clarity and decision-making processes.
Lesson 14. Goal Seek and Scenarios: Unveiling Future Revenue Insights
By leveraging Excel's What-If analysis tools, businesses can foresee the effects of proposed variable adjustments, allowing them to predict outcomes and refine strategies proactively. Tools like Goal Seek offer targeted analyses, while Scenario Manager provides comprehensive exploration of multiple potential adjustments, aiding effective decision-making.
Lesson 15. Advanced Problem Solving: From Goal Seek to Solver
Excel's Goal Seek enables basic What-If analysis by adjusting one variable at a time, but complex scenarios benefit from Solver for managing multiple unknowns and constraints. Available via add-in, Solver offers advanced algorithms for optimization, equipped to handle large data sets and multiple decision parameters.
Lesson 16. Excel Analysis Toolpak: Intro
The Analysis ToolPak enhances Excel's capabilities by allowing complex statistical evaluations, offering tools for users to perform tests like t-tests and ANOVA without needing to write intricate formulas. It serves non-statisticians and statisticians alike, simplifying data processing and making advanced statistical features accessible within the Excel interface.
Lesson 17. Data Validation 101
Data validation in Excel helps manage the type of data that third parties enter into spreadsheets, avoiding potential formatting issues. This ensures data integrity by restricting entries to specific types such as numbers or dates and providing feedback or warnings as needed.
Lesson 18. Unleashing Data Potential: Mastering Get & Transform Queries
By utilizing 'Get & Transform,' users can import, query, and segment large datasets from databases with Excel, offering a robust framework to perform advanced data manipulation. Power Query streamlines data editing, allowing for the creation of targeted reports and insights from meticulous data extraction.
Lesson 19. Exploring Excel's Formula Tools for Error-Free Sheets
Excel 2019 offers comprehensive tools for formula auditing, essential for ensuring data integrity and avoiding unintended changes. Key features like the 'Show Formulas' function allow users to identify and address formula-related issues efficiently.
Lesson 20. Automating Excel with Macros and VBA: A Comprehensive Guide
Excel 2019 offers the ability to record and run macros for automating repetitive tasks, saving time during spreadsheet formatting or data management. More advanced users can delve into Microsoft's Visual Basic for Applications to create complex macros, enabling enhanced automation through custom programming.
16 Hours average completion time
1.6 CEUs
20 Lessons
28 Exams & Assignments
208 Discussions
20 Videos
21 Reference Files
Mobile Friendly
Last Updated July 2023
Psychology 
Science 
Health & Medicine 
Special Education 
Learning Disabilities 
Social Science 
