Description
Databases, Filter/Sort, Named Ranges
- Looking at Excel program options, automatic calculation,
- Error checking rules, customising the ribbon and quick access toolbar.
- The format of databases: delimiters, headers, records, fields, flat file vs relational data,
- Filtering and sorting data and different data formats like Comma Separated Values (CSV), TXT, Tab separated values
- How to open data in different programs.
- Naming Ranges and using Named Ranges in formulas.
- Data validation and drop down lists and mastering find and replace.
- Use the Power Query Editor
- Group and Filter data to make it easier to understand
- Summarise data by fields
- Append one Excel data file onto another that has more columns
- Combine information from two columns for a bakery sales report
- Produce a sales report by order date and remove duplicates
- Merge data from related sources to provide meaning home insurance reports
Pivot tables, 3D Formulas, Advanced Formulas and Protection
- Understand 3D formulas between worksheets,
- Clean up data using advanced filter,
- Create a pivot table and change the value field settings,
- Create a pivot chart to visually show the stats,
- Using advanced functions like CHOOSE, VLOOKUP, HLOOKUP, MATCH and INDEX,
- Look at advanced functions within functions (Nested Functions) and
- How to protect the worksheet and workbooks.
Goal Seek, Data Consolidation and Solver
- Find out how much you can afford to pay for a house using goal seek and financial functions,
- Consolidate the information from several sheets,
- Understanding the grouping of data,
- Use solver to explore possible outcomes depending on input variables.
- Understand how to forecast future income
- Internal rate of return
- Net present value and other financial functions
UPGRADE Option: ULTIMATE Excel Course Package
If you want to learn as much as possible about how to use Excel and spreadsheets explore the ULTIMATE Microsoft Excel Course Package.
Ultimate Excel includes beginners to advanced topics and the entire range of training topics we have available for Microsoft Excel.
See the ULTIMATE Excel Course Package