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.
Case Studies Included in this Excel Course
You’ll get a client database list which contains errors and omissions. In that format you would waste lots of paper, have missing fields, send sales letters to people with no addresses and more.
You’ll learn how to open and work with data in a structured environment and know when to use the right software program for the right purpose of sending a newsletter or sales letter to your client database.
Learn more about Microsoft Excel Advanced Course 307
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.
Case Studies included in this workbook:
Microsoft Excel Online Training Course Case Study 308-1: As part of our Advanced Microsoft Excel Online Training Course, we feature an additional micro course which comes to you by way of an Ad Hoc Payroll case study. This case study is part of our advanced Excel course and comes at no extra course.
Microsoft Excel Online Training Course Case Study 308-2: When we operated our physical training centres in Sydney we taught a lot of seniors, mums returning to work and employees who needed to improve their Microsoft Office Skills AND MYOB Bookkeeping Software skills. We used PivotTables and PivotCharts to design quarterly course schedules to allocate rooms, trainers, training course resources AND refreshments like Coffee, TimTams and cool drinks. Read more..
Learn more about Microsoft Excel Advanced Course 308
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
Case Studies included in this workbook:
The Granny Flat investment spreadsheet will take you through the various costs involved in developing a granny flat in your backyard. It will clearly demonstrate the costs and enable you to forecast your future earnings and return on investment. Read more about Investing in a granny flat
Based on purchasing an investment property in Newcastle NSW we’ve created a number of spreadsheets to help students discover how much they can borrow, how to compare property investment options and make decisions to help them prepare financially for their life.
Learn more about Microsoft Excel Advanced Training Course 309
