Module 1: Data Analysis in Excel
“Data Analysis in Excel … Explore and Extend a Classic Excel Dashboard”
Summaries
- Module 1 > Data Analysis in Excel > Lecture
- Module 1 > Data Analysis in Excel > (Optional) Create an Excel Table
- Module 1 > Data Analysis in Excel > (Optional) Using VLOOKUP
Module 1 > Data Analysis in Excel > Lecture
- Hello everyone my name is Dany Hoter and I’m a Senior Program Manager in the Excel team in Microsoft.
- This course is going to be about analyzing and visualizing data in Excel.
- Originally it was supposed to be with Excel 2016, but we decided to just call it with Excel.
- Because you’ll be able to follow this not only with Excel 2016 but also with Excel 2013 and with a little bit more difficulty also with Excel 2010.
- Now, I’m gonna be covering areas of importing data into Excel, creating models of data in Excel and using new tools that were added to Excel in the last versions.
- I’m gonna show you data analysis using data in Excel, in the grid of Excel.
- So let me open my first file here in Excel and let’s understand how people are doing data Analysis and Excel for years.
- All right so what do you see here is at least my take on a classic dashboard in Excel, it had three pivot charts.
- The data for this, what you see here is actually in the, I can hide, I can unhide some of the data here.
- You’ll see that actually all the data is right here in Excel.
- The data came into Excel, let me unhide also the others because there are multiple tables here that contain the data.
- This is kind of typical that the data is contained in a few ranges in Excel.
- The author who created this support needed to combine all the data into one consecutive range of data.
- For that, the Excel user uses one of the oldest tools in Excel, the Vlookup function.
- Now it may seem a little different from Vlookup you saw before if you haven’t been using tables in Excel.
- In this example I’m using tables this is why it’s formatted this way and also this is why the expressions use column names and table names instead of a regular reference to Excel.
- They’ve been around for quite some time in Excel, since 2007, and they are a great tool.
- So what’s wrong in this picture and why did we need to introduce new tools into Excel in the latest versions, to go beyond what I was able to do here? So the challenges here are the following.
- In order to do that the Excel user had some tools, you could use manual copy and paste operations, you can create some macros if you knew how to.
- None of these solutions was really easy and accessible for Excel users.
- The Excel grid is a maximum of a million rows and for many cases this is not enough.
- So being able to manipulate millions of rows is not something that a regular Excel user would be able to do with regular Excel like this.
- So in order to solve these issues and another one that I want to mention is that some data sources are not really accessible to native Excel.
- So some modern Data sources let’s say if data is coming from Facebook, from Salesforce from other OData, some of them are really not accessible to Excel and people want to use a large variety of data sources.
- To solve all these problems we actually since 2010 have been providing new tools to Excel to be able to first of all create pivot tables and pivot charts without needing to combine the data physically into one large range.
- Being able to get into Excel millions and millions of rows, even tens of millions if you need to, is another thing, and with very good performance.
- I won’t even go and show to you exactly how to do it, it’s been there in Excel for many, many years.
- The new tools that I’m going to show you will make it possible for you to create really, really sophisticated business logic and do the calculations in a language that is similar to Excel, but extends the capabilities of Excel.
- I will show you the new tools, the new data modeling Excel, the new language for calculations, the new tools for querying and shaping the data.
- So this is the end of this module and you should expect in the next modules to learn about this new tools that are available now for doing data analysis and visualization in Excel.
Module 1 > Data Analysis in Excel > (Optional) Create an Excel Table
- On the Home tab I click “Format as Table” and then select the table style that I want to apply.
- Since I already have headers included in my data I want to make sure that “My table has headers” is selected Well look, by default filtering has been added to my new table.
- I think I’ll stick with my original table style so I’ll just click on the worksheet Oops! Now the Design tab is gone! But don’t worry if I want to make design changes to my table all I have to do is click anywhere on my table and the Design tab will appear.
Module 1 > Data Analysis in Excel > (Optional) Using VLOOKUP
- VLOOKUP works a lot like a phone book, where you start with a piece of data you know, someone’s name, in order to find out what you don’t know, their phone number, so as an example: I’ll enter part numbers, the thing I know, and find out prices, the thing I don’t know.
- I’ll enter H2 as the first argument because that’s where I’ll type the part numbers, followed that with a comma and then I’ll enter the range of cells that contains the data I want to search, that’s this block of data here.
- So I’ll enter B3, a colon, and E52 then I’ll type another coma.
- When I press Enter to tell Excel I’m done, you can see I get an error message because I haven’t entered the value in Cell H2. But when I entered a part number I get a price! So what just happened? I told Excel here’s a value in the left-hand column of my data, now look through this range of cells and in the third column to the right find the value on the same row.
