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
