Microsoft Excel Advanced

Course Requirements

To ensure success, learners should have practical experience creating and analyzing datasets using Excel. Specific tasks learners should be able to perform include: creating formulas and using Excel functions; creating, sorting, and filtering datasets and tables; presenting data by using basic charts; creating and working with PivotTables, slicers, and PivotCharts; and customizing the Excel environment.

What You Will Learn

In particular you will be able to:

Work with multiple worksheets and workbooks simultaneously

Share and protect workbooks

Automate workbook functionality

Apply conditional logic

Audit worksheets

Use automated analysis tools

Present your data visually

About This Course: This course builds on the foundational and intermediate knowledge presented in the earlier course and your previous Excel experience to help you get the most from the software. The ability to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions will give you the full power of Excel at your fingertips. The more you learn about how to get Excel to do the hard work for you, the more you’ll be able to concentrate on getting the answers you need from the vast amount of data produced by organizations.

Course Outline

Automating Worksheet Functionality

Update Workbook Properties

Create and Edit a Macro

Apply Conditional Formatting

Add Data Validation Criteria

Auditing Worksheets

Trace Cells

Troubleshoot Invalid Data and Formula Errors

Watch and Evaluate Formulas

Create a Data List Outline

Analyzing and Presenting Data

Create Sparklines

Create Scenarios

Perform a What-If Analysis

Perform a Statistical Analysis with the Analysis ToolPak

Create Interactive Data with Power View

Working with Multiple Workbooks

Consolidate Data

Link Cells in Different Workbooks

Merge Workbooks

Exporting Excel Data

Export Excel Data

Import a Delimited Text File

Integrate Excel Data with the Web

Create a Web Query

Appendix

Importing and Exporting XML Data

Long Course Description

Automating Worksheet Functionality (2007, 2010, 2013 2016)

As an experienced Excel user, there may be times when you need to automate frequently performed tasks, restrict the type of data entered in cells, or format data. Streamlining your workflow by tailoring the Excel environment to your job needs can improve your efficiency. In this lesson, you will learn to automate worksheet functionality. You will create and edit macros to complete commonly occurring tasks. You will leran to apply conditional formatting to give yourself additional flexibility over the data in your worksheet. Finally, you will set data validation criteria that restricts the type of data that could be entered in a cell or range of cells.

Update Workbook Properties

You need to find and identify files.You want to locate your workbook by using specific properties or information, such as its title or summary. Sometimes it can be extremely frustrating to search for something you need, especially when working under a deadline. In this topic, you will learn how to update workbook properties and to attach specific information so that locating the workbook or identifying its contents becomes a lot easier.

Create and Edit a Macro

You know very well that certain tasks in Excel become repetitive. Doing the same thing over and over can get tedious and contribute to data errors. Automating such tasks helps you to perform them with minimal interaction and lets you complete your work more accurately and efficiently. This is why you need to know all about macros. A macro is an automated tool that repeats a set of commands to complete a series of frequently needed tasks. In this topic you will learn how to create and edit macro.

Apply Conditional Formatting

Sometimes you will be asked to locate some specific data or make some parts of your worksheet stand out. In order to do that you will need to apply conditional formatting. Conditional formatting sets a specific format for cells or a range of cells based on criteria that you set.Cells can contain numerical or textual data. There are default formats already available for you, but you can also set your own formatting style. By learning how to use Conditional formatting you will be able to display the required information in a different format so that it is more distinctive.

Add Data Validation Criteria

You worked with worksheets that store various types of data. Restricting the type of data that can be input into your workbook gives you greater control over the information you need to work with.Some cells need a numeral, others should have times,dates, or whole numbers. In this topic, you will discover how to add data validation criteria so that all input values fall within a specified range. The validation criteria will help improve the integrity of your data by forcing specified cells to accept only a specific type of data. Setting data validation rules before you enter data guarantees that all input values will fall within a range that you set.

Auditing Worksheets

You should already be familiar with how Excel works with data and formulas and you know about the types of problems that errors in formulas or data can create. When working with complex worksheets having complex formulas, the risk of error is quite high. It is important to know that your data is being handled in exactly the way that you expect. Auditing worksheets will help you to validate data and ensure that formulas,functions, and data work as planned. Your ability to audit your worksheets will save time and make them more accurate.

Trace Cells

There are times when you are aware that your data is incorrect but you cannot solve the problem. You need to troubleshoot such problems. Excel enables you to correct the problem by graphically display where the data is coming from and where it is going. In this topic, you will learn how the trace cells commands will graphically display the relationship between a formula and the data that the formula uses. Tracing cells will help you to validate a given formula and makes locating formulas and their dependent cells a lot easier.

Troubleshoot Invalid Data and Formula Errors

Any data that does not fit a cell’s data validation scheme is considered invalid data. It creates problems by stopping necessary calculations or providing incorrect answers. Invalid data can destroy the accuracy and functionality of your spreadsheets. In this topic, you will learn to find and troubleshoot invalid data and formulas.

Watch and Evaluate Formulas

Some of your worksheets contain complex formulas, many of which are not visible on your screen.In order to ensure that the formulas are operating properly and returning the right answers,you may want to keep an eye on them while they run. Evaluating formulas while you are developing them allows you to make sure that they are functioning as intended. You want to know how certain formulas react to changes in data, and make sure that they are returning the expected values. Evaluating formulas during the development process helps you to ensure that they are functioning the way you want them to. In this topic, you will watch and evaluate formulas.

Create a Data List Outline

You watched and evaluated formulas in order to closely monitor the data that was not within the view area. You have both individual data and data lists in your worksheet, and now you want to view only the data lists by themselves.Viewing the data lists in a clear format can be a very effective means of analyzing and comparing a subset of data to the entire worksheet or workbook. But what if you want to view several data lists side by side and compare them? Data list outlines allow you to view multiple data lists at the same time.

Auditing Worksheets

Analyzing and Presenting Data

By now you are familiar with formulas, functions, and making everything work together as you intended. In today’s business world, analyzing data is as important as any business plan.Excel provides the tools and a variety of methods to perform complex statistical analyses for you.In this lesson, you will learn how to analyze data by creating sparklines,scenarios and other tools to perform sophisticated statistical analyses. Which of these data analysis tools will you use most often? Have a look and see which one could work best for your.

Create Sparklines

Typical charts often make it difficult to see the worksheet and the chart on the same sheet. Imagine that you need to show trends by presenting graphs and charts and numbers at the same time – Sparklines might be the answer. Sparklines show a quick view of data trends. By learning how to create them, you will be able to present the numerical data and a graphic representation to run side-by-side.

Create Scenarios

In life as well as in Excel different choices have different outcomes.In order to look at potential alternatives you will need to become familiar with scenarios. By creating different scenarios and analyzing their outcomes you will understand how changing conditions in created scenarios affect formula results. This Excel function will enable you to help your company make the most informed choices possible.

Perform a What-If Analysis

What if you wanted to borrow money to expand your business or if you want to know how much mortgage you can afford? You want to be able to look at the options of term, rate, and monthly payment. To be able to do that you need to master how to perform a What-If Analysis. It will allow you to project different possible outcomes by changing conditions like the length of the loan, interest rate, and closing costs. Goal Seek and The Solver are great ways to do this.

Perform a Statistical Analysis with the Analysis ToolPak

At some point you might be asked to analyze complex data to provide new insights into data that could not have been derived from Excel’s standard functions.And you know that analyzing complex data may involve a lot of steps and is prone to errors. What you need is a tool that obtains the desired results from complex data sets.It is called the Analysis ToolPak – it is an add-in that contains a variety of tools that help you perform sophisticated statistical analyses. It includes tools to create histograms, derive random samples,and perform regression analyses.

Create Interactive Data with Power View

To have an understanding of how to use a new add in called Power View will enable you to create interactive data presentations and to define visually attractive summaries of your worksheet with emphasis on the variables that are useful to you. In addition to that, you will be able to choose a new theme, background, transparency,pictures, and other content with the ability to insert, modify, arrange, and analyze. With Power View you can connect to different data models in one workbook.

Working with Multiple Workbooks

As you are becoming a proficient in Excel and using its many functions, you realize that your workbook is not an island. Excel gives you an option to connect individual workbooks to other workbooks. It makes it easier to use the information that you already have and extends Excel’s functionality. In this lesson you will focus on working with multiple workbooks. Your ability to link workbooks will make them more accurate and up-to-date.

Consolidate Data

In this topic you will learn to consolidate data from various workbooks, so that the data can be shown even if it comes from a different workbook.Imagine a lot of complicated spreadsheets that keep track of sales all over the world. Your manager wants to see an overview of sales without all the boring details. You do not want to be inefficient and to open up many different files just to look at their summaries.Excel comes to your help you by consolidating your data. This procces of summarizing data from multiple worksheets into one concise summary might become very important, time – saving tool for your needs.

Link Cells in Different Workbooks

Linking cells between workbooks makes work more efficient because you do not always need to keep files open and is also useful when you have to link the data in one workbook with the data in other workbooks. When dealing with different but related workbooks, sometimes it is better to directly link from a cell in one workbook to related cells in another. In this topic, you will link cells in different workbooks.

Merge Workbooks

There may be instances where you need to combine the contents of a set of similar workbooks into a single workbook.Trying to merge workbooks together manually is not an option. Excel allows you to merge multiple copies of a workbook with different names into a single copy, lets you automate the process so that related data is organized. Upon completion of this topic you will know how to merge workbooks data from many sources that can be used in a single file.

Exporting Excel Data

By now you know how to share data between different Excel workbooks. You saw in previous lesson how linking, consolidating, and merging saved time and made results more accurate. However, not all data you need to access can be found in Excel. There will be times when you need to import to or export from Excel to different applications. Your knowledge and ability to import or export needed data will help you complete projects more efficiently.

Export Excel Data

You do not know if everyone on your list who needs to see and use the data has access to Excel, but you know that you need to get this information available to them. What could you do? Retyping all the data into another file format can be very time consuming. By learning how to use Excel’s exporting ability to send data from one application to another is the answer. Excel lets you export a range, a worksheet, or even an entire workbook while keeping your Excel file unchanged but at same time allowing you make changes in the new application.

Import a Delimited Text File

Not only can you export your Excel data for use in Word and other programs shown to you in the previous topic, but you can also move in the opposite direction by importing Word data into Excel. It is possible to import all or only a selected part of the data from a text file into Excel and from a wide range of application types, including spreadsheet, databases, word processing, or the Internet. Once the data is imported, it will look and function just like data generated in Excel. This is called importing – formatting and saving data in one application for use in another application.

Integrate Excel Data with the Web

Imagine that you have some important sales information that needs to be sent to the whole company. A good solution for this would be to publish this information on a website. If the data is in Excel, you will need to put it into a format that can be placed on the web. Instead of retyping and reformatting it, Excel will convert it for you. Putting your Excel data up on the web will let you access and edit your data from anywhere. How to publish Excel files to a web page to make valuable data more accessible is your next challenge. You will learn to publish an entire workbook, a worksheet, or a selected range.

Create a Web Query

If you need to keep the important data on the web updated frequently, you want to learn how creating a web query lets you automatically import data that changes. Web queries are updated every time the information on the website is changed.

Appendix

Exporting Excel Data

Another type of data that you can manipulate in Excel is XML – Xtensible Markup Language content. The good thing is that you don’t need to be proficient with XML itself because importing and exporting XML means you can take data stored in XML, work with it in the familiar Excel user interface, and then make it available as XML once again all by using your Excel skills. It is useful knowledege as XML has become a very common, and universal way to store data.

Importing and Exporting XML Data

As you are almost at the end of your Excel Level 3 training there is something more to take in to take your Excel skills to a new level. You already know that XML is a programming language that describes data using structured text files and that you do not need to be proficient in XML because you can use the data through Excel. You have XML data that you want to work with in Excel and you must import that data in an easily readable format. You do not want to retype it all and make many mistakes. You want to learn how importing and exporting XML Data makes your life easier.

Microsoft Excel Advanced was last modified: January 12th, 2018 by darrell

Find out about our training

Excellent trainer who judged his delegates well and, for me, worked through the training at a perfect speed, giving a few extra tasks to those working quicker to give them something to do while the slower delegates had more 1 to 1 mentoring to help them catch up rather than make everyone work quicker and leaving some of the more 'confused' behind. Paul O'Donnell

The course has given me a lot more confidence in trying new things in Excel, I found the sections on conditional formatting, charts and pivot tables particularly useful. Helen Hodgkinson