Learners should have used Excel before and be familiar with creating workbooks with formulas and functions, 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
Automate workbook functionality
Apply conditional logic
Visualize data by using basic charts
Implement advanced charting techniques
Analyze data by using PivotTables, slicers, and PivotCharts
About This Course: This course builds upon the foundational knowledge presented in the Microsoft Excel Introduction course and will help start you down the road to creating advanced workbooks and worksheets that you can use to create dashboards. The ability to analyze huge amounts of data, extract actionable intelligence from it, and present that information to decision makers is critical succcess factor in driving a successful organization that is able to compete at the highest level.
Excel has become a powerful tool which you use every day and it makes your life easier. You rely on its functions to calculate and store data.The commonly used functions in Excel may not be enough any more to handle more complex data analysis needs. In this lesson,you will begin to unlock Excel’s potential as a data analysis tool, which will take you far beyond using Excel as a mere calculator and data storage. By building this foundational knowledge of Excel formula syntax,you are taking the first steps to true Excel mastery.
Use Range Names in Formulas
This is said to be one of the most powerful features of Excel. It enables you to name ranges for use in functions and formulas so that everyone working in the same workbook understands precisely at first glance what the formulas and functions are calculating. Mastering this to create formulas and functions is an easy way to use the same ranges over and over in your worksheets and to make those formulas and functions clear to others who view and work in your workbook files without manually typing or selecting cell and range references in large workbooks with many formulas. Once you understand how the meaningful names you assign to a given cell or range, and what calculations are being performed in a formula, how to reuse the references for a number of purposes, you’ll be developing complex, interconnected worksheets,and highly informative dashboards.
Use Specialized Functions
As you progress in your knowledge of Excel, you are likely to be called to perform more complex number-crunching and data-analysis tasks. For this task, you’ll need to have a much better understanding of Excel’s functions than you did before. Excel has a vast array of built-in functions that you can use to perform a staggering number of calculations.There are 12 standard categories of included functions which can be expanded by installing certain Excel add-ins. If you expect Excel to provide answers to your questions you’ll need to know how to locate specific functions when performing specific tasks and also a great deal about the syntax of these functions.Why don’t you take the time now to build an understanding of how some of these specialized and complex functions work.
Automating Workbook Functionality
As you develop larger and more complex workbooks, you may find yourself spending a lot of time and effort performing the same few tasks over and over again. What you want is to find ways to automate a number of tasks to save time and reduce errors. This lesson will show you a variety of Excel’s features that allow you to save valuable time, keep your data intact, and make using your workbooks easier.By learning how to take advantage of Excel’s automation functionality, you will ensure that only the correct data is entered into your worksheets and it will also perform repetitive task for you. The analysis you generate from your workbooks will provide sound organizational intelligence.
Apply Data Validation
Given the large number of people who may use your workbooks, it’s essential that you will be able to trust the data they enter. How to ensure that the data you have to analyse is accurate? Excel is an answer, of course. Data validation helps ensure that only valid data is entered into your worksheets. Understanding precisely how to configure your worksheets to accept only correct entries, will let you take control of your worksheets and be assured that you’re avoiding major data errors that could take hours to resolve.
Work with Forms and Controls
As you know there are different workbooks used by different people for different purposes. But they all have one thing in common: They all contain data. This means that someone has to enter that data. Although data entry in Excel worksheets is typically fairly straightforward, some worksheets require excessive scrolling for users to be able to access all cells. Other worksheets may require users to enter data that is difficult to type, such as technical jargon or complex numeric figures.If you find yourself in charge of a workbook that some users find difficult to work with, it would be helpful if you could take advantage of functionality to help those users complete the task accurately and quickly. Forms and controls in Excel provide you with a number of options for helping users enter data.In this topic you will learn how to use them.The end result will make you smile: You’ll enjoy the benefits of managing workbooks that aren’t full of errors and getting the data you need from your colleagues quickly.
Applying Conditional Logic
In this lesson, you will use conditional logic and functions to expand Excel’s capabilities beyond the basic functionality of built-in functions.Here you’ll begin to understand how to program Excel to perform tasks that you can’t use the built-in functions to perform. It requires an understanding of a new set of Excel functions, Lookup functions, and a deeper understanding of function syntax. By investing the time to gain an understanding of how these functions together, you’ll begin to develop the ability to create very complex functions and formulas that can perform any number of calculations. That will give you a deep understanding of the details stored in your data.
Use Lookup Functions
Imagine that you’ve collected a massive amount of data about your operations and you need to find out who employee 1289’s manager is or in which region he or she works? If you don’t know the answer you’re looking for, you don’t know what search criteria to enter. Fortunately, Excel enables you to to find out the information you need easily – even in massive datasets. By understanding how to use a set of functions known as Lookup functions, you’ll be able to look up in a formula or function any particular entry in any dataset.This can help you make clear, informed decisions that affect large-scale operations based on just a single chunk of data.
Sometimes Excel functions and formulas that you have used so far aren’t always exactly what you need or they aren’t capable of performing the task you want them to. Suppose you need to use the values returned by several different functions as arguments in another function. This may seem like difficult challenge. You may think you need to use a variety of functions in a variety of cells, and then include references to the results in other formulas to pull this off. But that’s not really the case. Excel has the capability to consider multiple functions or calculations simultaneously, and it can do so within just a single cell. Once you begin to understand one simple concept, you’ll be able to put together highly complex functions and formulas that can perform a sophisticated set of calculations based on a wide variety of criteria.
Visualizing Data with Basic Charts
You may find yourself presenting information to large audiences on a regular basis. In these cases, you don’t want people scanning lines of data on a worksheet when you’re trying to present. You want to give them a simple view of important data so they can quickly understand what’s really important. In short, you need a way to generate visual representations of your data. Excel includes some handy functionality that can convert your raw or analyzed data into visually clear, easy to interpret diagrams with just a few steps. In this lesson you will learn how this functionality works. This understanding will give you the ability to generate high-impact visuals to quickly present to nearly any audience. Providing your audience members with a visual snapshot of your data will enable them to quickly see the overall impact of raw data, recognize trends, make easy comparisons, and focus on your message instead of your worksheets.
As an Excel user, you need to convert all of the figures and labels from your massive data into a picture that makes a clear, simple statement. You are aware that decision makers are looking for only a snapshot of the most relevant information. And, it’s likely your job to provide that snapshot for them. The good news is that this this is quite simple to do. This topic will provide information how to make use of charts – graphical representations of the numeric values and relationships in a dataset in to easy-to-view visuals that will help worksheet viewers to quickly and easily interpret the data. Now you need to learn how Excel pulls data from your worksheets to create charts and how Excel reads your data to create them.
Modify and Format Charts
Now, you learned how Excel pulls and reads data from your worksheets to create charts and how this can be done with just a few mouse clicks, which is good. But you might not want to use the default chart configurations always. That will depend on your audience and the message you want to convey. You may decide to include more or less information than the default configurations include, present your data with organizational branding, or simply make your charts larger or easier to read. This topic will show you how by formatting and modifying your charts, you take full control over the message your charts present and their overall visual impact. You will learn to modify charts by making changes such as moving chart elements, adding or removing chart elements, turning the display of particular data on or off, and changing the chart type. You will understand how a well-formatted chart can mean the difference between simply delivering information and making an impact on your audience.
Advanced Charting Techniques
In this lesson, you will explore Excel’s capabilities for graphically presenting data that go well beyond the use of simple pie or column charts types. Data and relationships can be complex, and the basic charts aren’t always sufficient for displaying such complexity. You will use advanced charting features and sparklines to convey complex or large datasets graphically. Taking advantage of Excel’s data visualization functionality will enable you to instantly make a connection with your audience and give them instant insight into even the largest datasets. Investing a bit of time now to fully understand what these capabilities are and what they can do will give you the ability to make visual sense of your complex data for nearly any type of presentation.
Use Advanced Chart Features
Excel includes a wide range of advanced charting features that enable you to display varying sets of data together, include forecasting trends on your charts, and also reuse highly stylized or formatted charts again and again. This level of functionality means you can quickly make an impact on nearly any presentation without having to put a lot of time and effort into doing so. Suppose you want to display future projections for your datasets on the same chart you use to display the data itself? You definitely want to learn how use of advanced chart features can help you to do so effortlessly.
For some datasets charts and trendlines aren’t always practical ways to graphically display data, trends, and relationships of the information you’re presenting. To give your managers an easy way to visually identify trends with just a glance, but without cluttered and dense charts, you will need to take advantage of Excel’s data visualization functionality in sparklines. They allow you to store massive amounts of data on very large worksheets with the ability to spot important information quickly. Sparklines could be explained as type of miniature chart that you can actually insert within worksheet cells.
Analyzing Data with PivotTables, Slicers, and PivotCharts
You’ve become familiar with sorting and filtering functions to drill down into your data to get answers to very specific questions. Although they are good option for getting specific answers, these aren’t necessarily the best options if you need to frequently change the questions you’re asking of your data. You might find that by using these functions you often need to rewrite functions, adjust criteria, or re-filter your data; or sometimes, you need to use several of these methods at the same time. If you work in a high-paced, data-intensive environment, you simply need something a little more dynamic. Excel offers a powerful feature that enables you to ask any number of questions of your data and get detailed, specific answers. And you can do it all over again in just a matter of moments by using PivotTables to analyze your data. As the volume of data, and the speed at which organizations generate it, continues to grow, your ability to extract actionable intelligence from it becomes increasingly critical and can give you the competitive edge to succeed.
Create a PivotTable
To take advantage of PivotTables, you must first understand how to create them. Although this is a relatively simple process, you must also know a bit about the type of data that works best for PivotTables. PivotTables are effective for summarizing large volumes of data according to two or more criteria to return specific answers to your questions. PivotTables combine some of the most powerful and useful types of Excel functionality, such as sorting, filtering, summary functions, and subtotals, to give you an incredible level of control over how you view your data. By getting this foundational level of understanding, you’ll be preparing yourself to create useful, effective PivotTables that you can use to analyze your raw data in fine details.
Analyze PivotTable Data
Creating and configuring effective PivotTables is a matter of asking the right questions to attain the necessary information. PivotTables allow you to reorganize and re-analyze your data as many times you want to get all of the answers you’re looking for.Once you’ve determined what question you want Excel to answer, you can begin to design your PivotTable’s structure. In this topic you will find out a simple way of translating your questions into a PivotTable structure. You will learn of many different tools and commands which enables you to organize the structure of your PivotTables. Knowing how these tools work and understanding how PivotTable structure translates into actionable intelligence are the keys to getting the answers you seek.
Present Data with PivotCharts
Although PivotTables provide you with an amazing array of options for analyzing your data, they have one downfall: they can be difficult to read. Wouldn’t it be nice to present PivotTable data into visually appealing, easy-tointerpret charts for your audience to gather meaning from all of that data with just a glance? Fortunately,there is a quick and easy way to translate your PivotTable data into charts just as with your other data. For this to happen you need to learn how to present your data with pivotCharts – graphical representations of numeric values and relationships among those values.Taking the time to familiarize yourself with this functionality will provide you with all of the benefits of Excel charts when it comes to presenting the data.
Filter Data by Using Slicers
If data analysis is your key function, you’ll appreciate the ability to quickly and easily re-filter your PivotTables on any number of fields. In addition, the ability to quickly view the filters applied to the current dataset could come in handy if you’re returning to a worksheet after having not worked in it for a while. In short, you need some type of tool that gives you a high level of control over PivotTable filtering; one that is easy to work with and easy to interpret regardless of how many filters you apply to your PivotTables. Excel includes such a tool. Gaining an understanding of how Filter Data by Using Slicers works will give you a greater level of control over your PivotTable filtering and the peace of mind knowing that you have filtered your data in precisely the manner you meant. Slicers are PivotTable filtering tools that you can link to various PivotTables in your worksheets. You can create a slicer out of any of the fields associated with a PivotTable, and then use those slicers to filter each field by any of its unique entries.
Microsoft Excel Dashboards was last modified: February 2nd, 2017 by darrell