Microsoft Excel - Pivot Tables and What if Analysis | Nejla Z. | Skillshare

Microsoft Excel - Pivot Tables and What if Analysis

Nejla Z., Manager of Information Technologies

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (17m)
    • 1. Introduction to pivot tables

      5:23
    • 2. Pivot - Filters

      1:51
    • 3. Pivot - slicers

      2:02
    • 4. Pivot - Charts

      1:57
    • 5. What if Anlaysis - goal seek 1

      3:40
    • 6. What if Analysis - goal seek 2

      2:14
18 students are watching this class

About This Class

Microsoft Excel can be tricky. 
Tons of people can format cells and and apply formulas. 
But few can do it in a way that helps their boss see
what point they’re trying to prove. 
Plenty of people can present data visually. 
But few can present data in an engaging way that increases, 
their chance of securing a sale.

Excel is used to:
- store database records
- conduct planning
- manage budgets
- create charts and graphs 
- analyze business intelligence, sales and marketing data
- keep customer and staff records 

As per the following infographic, 
professions in which Excel power-users can stand out include
marketing analyst (with a starting salary of $50,232), 
business intelligence analyst ($55,386), and management consultant ($70,676).

If your current or future job requires you to compile, analyze, 
and present data, would you feel confident in your ability to do it? 
Simply put, can you maneuver expertly around a spreadsheet?

If you are not you are at the right place! Here in this course you 
will start to make yourself familiar with the spreadsheet, than you will come
at the level where you will start to use it as a real pro! 

If you want to enhance your Excel skills, you’ve got your work cut out for you. 
Don’t be fooled into thinking you can master Excel in one sitting. 
Nor is there one be-all and end-all course that will ensure your success
with every spreadsheet in your future. 
It’s hard work understanding pivot tables, using filters to get just
the right result, tweaking data series, and resizing charts.

If you are ready to become real excel specialist click enroll and enjoy us! 

Good luck! :) 

Transcripts

1. Introduction to pivot tables : Hello and welcome to Marks of Pixel 2016 tutorial. Today we will introduce pivot tables when you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. Pivots. Tables can help make your worksheets more manageable by summarizing your later and allowing you to money. People eat it in different ways. We use pivot tables to answer. Question. Consider my data here and let's say we want you to answer the question. What is the amount sold by each sales person? Answering It could be time consuming and difficult. Each sales person appears on multiple rows, and we would need to total all off their different orders individually. We could use the sub total commend to help find the total for each sales person. But we would still have a lot of data to work with. But people table well, save our time. So let's see now how to create a pivot table, First selected table or cells, including column hatters. You want to include in your pivot table, so I will select all my data from the insert tab. Click in the people Stable command as you can see create pivot table dialog box appeared does your settings here I will to stable one and that my pivot table want to be report in new work Shit. And when you're done with your settings will just click. OK, as you can see a blank pivot table and feel the least appeared on a new work Shit. So, as you can see, once you create a pivot table, you will not need to decide which fields to add. Each field is simply ah, column Hatter from the source data in the pivot table field least here, check the box for each field you want to add. In our example, we want to know the total amount sold by each sales person, so we will tax, steals, person box and order amount fields on other way east to drag and drop you're Hatter. The selected fields will be added toe one offer the four areas below. In our example, the sales person field has been added to the rose area, while order amount is added to the or values. Alternatively, you can drag and drop fields directly into the desired area, as you can see, or you can just check the boxes here in the People Table Joe's Fields, the people stable will calculate and summarize the selected fields. As you can see now, example, the food people stable shows the amount sold by each sales person. Just like with normal spreadsheets. You can soar to the data in a pivot table using the sword and filter commend on the home tab. Or you can also apply any type of number formatting you want. For example, you may want to change the number four match to currency. However, be aware that some types of formatting may disappear when you modify the pivot table. Note here that if you change any of the data in your source worksheet, the people table will not update automatically to manually update it. Select the people table and then goto analyzed and refresh. And last thing is pivoting data. One of the best things about people tables is that they can quickly people toe or reorganize your data, allowing you to examine your Roche it in several ways. Pivoting data can help us for different questions and even experiment with your data to discover new trends and patterns. So in that way, you can add columns. So far, our people table has only shown one column at a time. In order to show multiple Collinsville, you'll need to add a field to the columns area. So drag and field month in column. And as you can see, we have now multiple columns to change. AURORE COLUMN You can give a completely different perspective on your later. All you have to do is remove the field in question and replace it with another. So you I can just drag and drop here and I cancel my sales person in rows, and I will just enter, for example, region in that area. So, as you can see now, my data is different, the people table adjusted and showing a new data in our in our example. It now shows the amount sold by each a region. This was all for these introduction off people's tables. I hope you like it and learn something end. See you later in the next lesson. 2. Pivot - Filters: Hello and welcome to Microsoft Excel 2016 tutorial. As you learned in our previous lesson introduction to be with tables people. Tables can be used to summarise and analyze almost any type of data to help human people eat your pivots table and gain even more insight into your data. Exel offers three additional tools. Filters, slicers and be with Char's, So let's start with the filters. Sometimes you may want focus on a certain section of your data. Filters can be used to narrow down the data in your people stable so you can view on Lee the information you need. Let's see now how toe add a filter in the example here will filter our certain sales people to the Thurman how their individual sales are impacting each region. So to do that, drag seals person field into to feel thirst. As you can see, the filter appeared above the fevered table Dan. Click drop down arrow, then check the box next to select multiple items, select multiple items. Now you can uncheck the box that next to any item you don't want to include in the pivot table. In my example here, I will contact the boxes to for a few sales people. I will choose this. This and this author, you're ready. Click OK. As you can see, the beauty table adjusted to reflect the changes. In the next lesson, we'll see something more about slicers. 3. Pivot - slicers : Hello and welcome to Marcus of Excel 2016 tutorial. In the second part, off pivot tables will see more about slicers. Slicers make filtering data in pivot tables even easier. Slicers are basically just feel thirst. Both are easier and faster to use, allowing you to instantly paver to your data. If you're frequently filter your pivot tables, you may want to consider using slicers instead of filth wrists, so we will now see how toe add a slicer. First, select any cell in the pivot table and from the analyze tab you will click. Insert slicer command. As you can see dialog box appeared Jack the box next to the desired field. In my example here, I will choose seals person and then click OK. As you can see, this slicer appeared next to the pivots table. Each selected item will be highlighted in bull as it is here. In the example below, the slicers contains oh, eight sales people, but on Li fi off them are currently selected just like filters on Lee. Selected items are used in the pivot table. When you select or de select them, the pivot table will instantly reflect the change So if I, for example, add Brennan Michael to my least as you can see, you can check more than one by taking control on your keyboard. So, as you can see, my pivot table now is a little bit different. What you can do also is that you can click filter Aiken on the top, right quarter off the slicer to select all items it once. 4. Pivot - Charts : Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover be with charts. People trust our like regular charts except the display data from a pivot table. Just like a regular cause charts, you will be able to select a chart type layout and style that will best to represent to the data. Let's see now how to create a pivot chart. In example, here, our pivot table is showing a portion off each region sales figure. So will use the people charged so we can see the information more clearly. Select any sow in your pivot table, then from the insert tab here, click the pivot chart. Commend as you can see favor Chart box Insert charged The dialog box appeared. You will now select the desire tar type in layout and click OK, so I will choose this one. And as you see the people charred appeared off course. You can use filters or slicers to narrow down the data in your people charge or to view different subsets of information to change columns or rows in your pivots table. For example, I can who'd sales be people out or I can add month in my access as you can see different options which will change your data in your pivot table as well as in your pure chart. This was all for the people to tables. Lesson an additional people table features. I hope you will like it and that you'll learn something and see you later in the next list . 5. What if Anlaysis - goal seek 1: Hello and welcome to Microsoft Excel 2016. Tutorial Today will cover What if analysis Excel includes many powerful tools to perform complex mathematical calculations, including What if analysis This feature can help you experiment and answer questions with your data, even won. The data is incomplete in this lesson. You will learn how to use a what if analysis tool cold gold seek. So let's see what he's gold seek. Whatever you create a formula or function in Excel, you put various parts together to calculate a result. Go Sig works in the opposite way. It lets you start with a desired result and it calculates the input value that will give you the net result. We will use a few examples to show how to use gold seek. So, first example as you can see here, we'll show you that less. Hey, you are enrolled in a class and your currently have you currently have a great off 65 you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average so you can use gold . Seek to find out what a great you need on the final assignment. Pass the class. So as you can see here, in my example, the grates on the 1st 4 assignments are 58 70 72 16. Even though we don't know what if if grade will be weaken right, Ah, formula or function that Khalkhali the final great In this case, each assignment is weighted equally. So all we have to do is average all five grades by typing formula here, equal signed and average, including cells from B two to B six. I'm just click OK. Once we use school sake, Selby six will show us, said the minimum grade will need to make on that assignment to make gold seek what will select the cell with the value we want to change whatever we use school seek. You will need to select the cell that already contains a formula or function. In our example it is cell B seven. So will quick B seven And then from the data tap, we will quick what if analysis and from the drop down my new shoes Gold seek. As you can see, a dialog box appeared with the three fields The first field or SAT cell will contain the desired result. In our example it is B seven. The second field were to value is the desired result. In our example we will enter 70 because we need 72 past exam and the third field by changing south is the cell where goal see quote please. It ends Worm So woke Woolls cell activity sticks after we have finished wall quick. Okay. As you can see, excel need a little bit time to calculate result and if it find result in the dialogue box will tell you that it found a solution. The result will appear in this pacify cell in our example Goal seek calculated that we will need to score a police a nightie on the final assignment toe enter a passing Great. So just click ok and this is my result 6. What if Analysis - goal seek 2 : Hello and welcome to Microsoft Excel 2016 tutorial. This is second part of what if analysis and we will see one more example to use gold seek. Let's they were planning and want and want to and white as many people as you can without exceeding a budget off 500 so we can use gold. Seek to figure out how many people to invite in our example. Here, Selby five contained the formula, which is I will show your now here in my what is be to plaza be three times before to calculate the total cost off a room reservation. Blast the cost per person. So what will select to the cell with the value we want to change? In our example, we will select to be five. And from the data tab, click the what if analysis and select a goal seek from the drop down menu again, we have three feels the first feel the desired result. What is be five to value global and 3500 because we can spend 500 in the turned field by changing cell is the cell where gold sequel place it's answer in our example, will select cell before because we want to know how many guests we can in wide without spending more than 500 after we are done, Double click. OK, as you can see, Gold seeking with Salbi five found a solution. So the result appeared in the specified cell. In our example Gold seek calculated The answer, Toby Approximately 18.62. In this case, our final answer needs to be a whole number. So we'll need to round. The ants were up or down because a rounding up would cause us to exceed our budget. We're round down to 18 guests. As you can see in our example here, some situation will require the answer to be a whole number. If gold sig gives you an decimal, you will meet around, up or down, depending on a distant creation.