Learners should have used Excel before and be familiar with creating basic formulas, using Autofill along rows or columns and working with Absolute Cell References (e.g. $C$5) to refer to fixed figures – such as VAT rates or performance targets.
What You Will Learn
In particular you will be able to:
Create advanced formulas
Analyze data by using functions and conditional formatting
Organize and analyze datasets and tables
Visualize data by using basic charts
Analyze data by using PivotTables, slicers, and PivotCharts
About This Course: This course is designed for learners who already have foundational knowledge and skills in Microsoft Excel and who wish to take advantage of some of the higher-level functionality in Excel to analyze and present data. Upon completion of this course, you will be able to leverage the power of data analysis and presentation to make better informed, intelligent organizational decisions.
Creating Advanced Formulas
The commonly used functions in Excel may not be enough to handle more complex data analysis needs. As you progress with Excel, and as you are called upon to provide a deeper understanding of your organizational data you’ll need to know how to talk to Excel at a higher level to get the most out of your data. Understanding the language Excel speaks is the key to having conversations with it and getting the answers you need. Creating advanced formulas is the gateway to some of the most sophisticated functionality Excel has to offer. By embedding multiple functions within a single, complex formula, you can transform raw business data into useful strategic information.
Apply Range Names
Range names will make your functions and formulas easy to understand and maintain.They are meaningful names you assign to a given cell or range to make it easier to both understand what calculations are being performed in a formula and reuse the references for a number of purposes. It is highly likely that you will not be the only person working with and analyzing data with your workbooks. This means it’s essential that everyone working in the same workbook understands precisely what the formulas and functions are calculating and this is what you will cover in this topic.
Use Specialized Functions
Excel has 13 categories of functions. Each category has a number of functions designed for very specific types of calculations. You will need to become familiar with specialized functions to run calculations on your data. You’ll need to know how to locate specific functions when performing specific tasks.Taking the time now to build an understanding of how some of the more specialized functions work, will give you the tools you need.
Analyzing Data with Logical and Lookup Functions
In this lesson, you will analyze data with logical and lookup functions. You will work with logical functions such as AND and OR and lookup functions such as VLOOKUP and HLOOKUP to find the answers to specific questions you had — answers that could be found only within your dataset.
Leverage Questions and Testing to Write Formulas
This topic will help you understand how by asking the right questions prior to writing out your formulas, that use logical and lookup functions, you can get an initial idea of which functions you’ll need to use. After you’ve written a formula, it’s always good practice to thoroughly test it before you deploy it for use by others. You want to ensure that the formula performs exactly as expected.
Use Logical and Lookup Functions to Find Answers to Questions
Logical and Lookup Functions are very powerful tools which you want to master.It is very important to learn how to run logical tests on your data and how to use VLOOKUP and HLOOKUP functions which will enable you to find data based on specific criteria.
Organizing Worksheet Data with Tables
As your worksheets and workbooks grow in size and complexity, you might find it more challenging to locate the data you need. You want to organize the data in your Microsoft Office Excel files so that you can find what you need as efficiently as possible. It’s much easier to sort and filter the data to locate the information you need if data is structured in tables. In this lesson, you will find out how to get the output that you require by organizing data and performing meaningful functions.
Create and Modify Tables
Creating a table is one of the best ways of organizing data. In this topic, you will create tables for a set of data. While Excel data is organized into rows and columns, the data specific to your needs will not be treated as a unique data set on the worksheet unless you represent it using tables. You will learn how by applying unique formatting options you can make the data distinct from the rest of the worksheet and improve its clarity.
Sort and Filter Data
After you enter data in your worksheet, you may want to view the it in a different way.By understanding how to sort your data, you can create different views of the same data without altering its original format. Filtering the data will help you to eliminate unnecessary data from appearing on your worksheet.
Use Summary and Database Functions to Calculate Data
In this topic, you will perform calculations on tables and worksheet data ranges. When you use a formula to perform a calculation in a worksheet, you have to choose the data to be included in the calculation. This can take some time if the volume of data is large. Database functions and other types of summary functions, can find the data you are looking for and perform the calculation, all in one step.
Visualizing Data with Charts
In this lesson, you will create and modify charts to graphically display data. You will also learn which chart type will work best to transfer knowledge as quickly and efficiently as possible. Excel provides an enormous array of charts, from bar and column charts to pie and doughnut charts to line, area, and scatter charts. They will enable you to view a large amount of data at a glance and help you to draw relevant conclusions quickly.
Sometimes when you look at a large amount of data, the rows of data may seem endless. This complicated display of data makes it difficult to draw any meaningful conclusions. When you use a chart, you can consolidate data into a visual format that is easily understandable. By looking at the information graphically, you can quickly compare data and assimilate information you would not have noticed otherwise.
Modify and Format Charts
Once you have created a basic chart, you can modify its structure in various ways to suit your needs.The default format of a chart item may or may not convey the correct meaning of the data in your chart.Instead, you can format each chart item to appear exactly as required. If you decide that you would like to change the style and the layout of the chart to represent your data in a better way, there are options to change the appearance of individual chart elements or the appearance of the entire chart. This topic will help you understand how to modify and format charts so that your data is not misleading or difficult to understand.
Create a Trendline
A common business task you might be asked to do is to project what the business will be doing in the near-, mid-, and longterm on the basis of the data you have. In order to do so you would need to know about trendlines – the Excel tool that will help you visualize those projections about the future state of the data based on current trends in your data. You can use trendlines to make predictions about virtually any type of data. There are six types of trendlines available in Excel which you will learn about in this topic.
Create Advanced Charts
This topic will show you how the advanced charts can work in your advantage to consolidate the data into a single view—instead of using more than one chart.Sometimes you will need to visualize data based on two X and/or two Y axes. The additional axes help you better grasp what the data is trying to tell you.You will learn to use the advanced chart that uses primary and secondary X and/or Y axes to convey meaning.
Analyzing Data with PivotTables, Slicers, and PivotCharts
PivotTables and PivotCharts take chart and table functionality one step further and enable you to selectively analyze the data in your worksheet in sophisticated and interactive ways. In this lesson, you will learn to create PivotTables, PivotCharts and slicers to make data visualization interactive. When you present data in that way, data users would be able to engage more deeply, uncovering new questions and answers that might not be readily apparent if the data were presented in a static format.
Create a PivotTable
A PivotTable is a dynamic table that enables you to reposition the columns and rows of a raw dataset without altering the data. The repositioned columns and rows provide new perspectives on the data. This interactive worksheet table can be used then to summarize and analyze large amounts of worksheet data quickly. It displays the selected worksheet data in a matrix format with specialized functionality that enables you to see new data relationships and create concise customized output.
Filter Data by Using Slicers
In this topic, you will use Slicers to filter PivotTable data. A Slicer is a filtering tool that allows you to include only the required elements in the PivotTables and PivotCharts. You can add and remove elements from the table display so that the data can be compared and evaluated from different perspective. You could choose more than one Slicer for a PivotTable, if required, and Slicers can be also used with multiple PivotTables to showcase your data consistently in a variety of scenarios.
Analyze Data by Using PivotCharts
Though PivotTables can generate reports that are easy to understand, you may also want to show a visual representation of data, such as growth or decline on a monthly or yearly basis. You may want to focus on a particular quarter to highlight revenue growth.To do that you need to learn how to apply a PivotChart. This is an interactive chart that graphically represents the data in a PivotTable report and is also tied to its PivotTable report’s contents and functionality. The PivotChart represents the current state of the PivotTable report.By using this function,you will not need to edit or revise the source data.
In this lesson, you will learn to insert graphic objects into your workbooks. You will explore a variety of the graphic options available in Excel, including pictures, screenshots, shapes, clip art, and SmartArt. You will learn how to group and layer the various types of graphics and connect shapes to worksheet data. Properly utilized, graphic objects can help improve the overall impact of your workbooks.A spreadsheet that contains only tables and numeric data looks monotonous and uninteresting.When you use graphic elements in a spreadsheet, you will be able to draw users’ attention to specific areas of the spreadsheet. You can also enhance the look and visual appeal of your data with corporate logos, diagrams, and other graphic elements to add some visual appeal to an otherwise bland workbook.
Insert and Modify Graphic Objects
To make your worksheets attractive, you may want to add graphic objects to them.The most common types of graphical objects are screenshots, pictures, and ClipArt graphics. In this topic, you will find out how to insert graphics into your worksheets and modify them. Some graphics, including pictures and clip art, are premade and inserted from existing files. Other graphics, such as SmartArt and shapes are created from scratch.However, if you have a selection of graphics that already exists, such as ClipArt, you can quickly choose and insert them when required.
Layer and Group Graphic Objects
Your worksheet may require you to create more complex designs, integrating separate graphics within a narrow space. To control the position of those graphics, you will need to use layers,as well as form groups of graphics when you want to move multiple layered images simultaneously on the page. For example, some graphics should appear above or below others to create a specific effect or sometimes you might want to treat a number of graphics you’ve inserted as a single graphic. In this topic, you will layer and group graphic objects. This will enable you to manage the graphical relationship between graphic objects.
A SmartArt graphic is a custom-made visual representation of the relationships between data, events, or ideas which combines graphic objects with text and symbols to create complex custom illustrations. Once you insert a SmartArt graphic, you can change its structure and layout, add and customize text, and modify the format and appearance of each element and of the SmartArt object as a whole. Even if you do not have much time or particular graphic design skills, you can use SmartArt graphics to quickly create a comprehensive workflow diagram or any other graphical representation that involves relationships, sequence, or hierarchy.
So far you have inserted a number of components in a workbook to increase the complexity and sophistication of content within it. You can extend the level of customization to apply to the Excel environment, so that all your workbooks have rich features and sophistication. In this lesson, you will learn how to enhance the appearance and functionality of the Excel environment by using the customization options.This will enable you to organize the work environment to suit specific needs and preferences. You will find out how to make it more functional by including comments, hyperlinks, watermarks, or background pictures to your worksheet.
Your workbook is a unique object and will have its own unique requirements for both the data layer and the presentation layer.There are several things you can do to customize your workbooks, including adding comments,hyperlinks, watermarks, and background pictures. You may want to provide more information about the contents of a cell without cluttering up the page, to navigate quickly to other locations through hyperlinks, or to create watermarks. Customizing your workbook will allow you to provide all the necessary information in a stylish and efficient manner.
A theme is a collection of formatting options that can be applied as a whole to an entire workbook.Workbook attributes that can be formatted via a theme include fonts, colors, effects, and styles.Sometimes it’s more efficient to change the look of an entire workbook all at once rather than one element at a time. This is especially true when you know you want to apply a consistent look and feel for fonts and colors to all components of a workbook.In this topic you will apply themes to get consistent formatting across a particular workbook.
Create and Use Templates
Creating templates are time-saving techniques that help you modify existing or create new workbooks with just a few mouse clicks. Templates are useful when you know you’ll be reusing a particular style of worksheet over and over again. Rather than re-create the worksheet each time you need it, you could create a template that has most of the work done for you already.Excel has a number of built-in templates that you can use. You can also create your own custom templates or save your functional worksheet as a template for all other worksheets.
Knowing how to protect workbooks and worksheets is a vital skill—especially if you will be sharing your files with other people either in or outside of your company. The last thing you want to do is send a file to someone and have them return it with new data or a new structure you were not expecting. After mastering this topic you will know how to prevent unintended changes from occurring,and you will be able to apply various types of workbook or worksheet protection.
Microsoft Excel Intermediate was last modified: February 2nd, 2017 by darrell