See solution in other versions of Excel:. Question: How do I create a pivot table in Microsoft Excel 2011 for Mac? Answer: In this example, the data for the pivot table resides on Sheet1. Highlight the cell where you'd like to see the pivot table.
In this example, we've selected cell A1 on Sheet2. Next, select the Data tab from the toolbar at the top of the screen.
Click on the PivotTable button and select Create Manual PivotTable from the popup menu. A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we've chosen cells A1 to D13 in Sheet1. Next, select where you wish to place the PivotTable. In this example, we clicked on the 'Existing worksheet' option and set the location to Sheet2!$A$1.
Click on the OK button. Your pivot table should now appear as follows: In the PivotTable Builder window, choose the fields to add to the report. In this example, we've selected the checkboxes next to the Order ID and Quantity fields. Next under the Values box, click on the 'Sum of Order ID' and drag it to the Row Labels box.
Your pivot table should now display the total quantity for each Order ID as follows: Finally, we want the title in cell A2 to show as 'Order ID' instead of 'Row Labels'. To do this, select cell A2 and type Order ID.
ExcelFrog Clear Excel Tutorials Excel: The What If Analysis With Data Table There are some Excel features that are awesome but somewhat hidden. And the 'data table analysis' is one of them. Sometimes a formula depends on multiple inputs, and you'd like to see how different inputs values would impact the result.
The data table is perfect for that situation. This is extremely useful to analyze a problem in Excel and figure out the best solution. The Dataset To use the data table feature we will need some data. Here's a table with 2 inputs (book price and quantity sold), and a formula (revenue = price. quantity).
It's easy to insert a new table on your PowerPoint slide and then enter content within this table as required. However, as it happens in most offices or other work places, the table content may already be populated in a bunch of contiguous Excel cells. What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. Excel will change the value of cell C4 accordingly for you to see the corresponding result on the sheet. Scenario Summary. To easily compare the results of these scenarios, execute the following steps.
If we sell 100 books at $29 each, we will make $2,900. The data above assume we know the book price and how many copies we will sell. But we're actually not sure, so we'd like to know the revenue for different combination of price and quantity.
For example: how much profit will we make if we sell 150 copies instead of 100, and what if the price is $19 or $49? There are 2 main ways to do that:. Manually change the 2 inputs, and see how the result change. Do that automatically with the data table feature.
Nobody likes doing repetitive tasks manually, so let's try the second option;-) The Set Up Before we start using the data table, we need to do some changes to our data. First we need to copy the formula to a separate place.
You can do this by simply typing =C4 in a blank cell. Then we need to tell Excel what values we would like to test. To the right of the formula we list all the prices ($19, $29, $49), and below the formula we list all the quantities (25, 50, 100). And we can add some basic formatting (borders and currency) to make things look slightly better.
Our goal is to fill this table with the profit for each combination of price and quantity. Data Table Example Now we can start the interesting part! Select the new table we just created, and in the 'data' tab, click on the 'what if analysis' button. There select 'data table'.
A popup will appear that you need to fill like this:. Row input cell: we listed the price at the top, so the 'row input' is the price, which is in C2. Column input cell': we listed the quantity in the left, so the 'column input' is the quantity, which is in C3. Press OK, and we are done. Excel did the math for us, and automatically updated the spreadsheet with all the numbers we wanted.
Now we can see exactly how much revenue we will make based on the different combination of inputs. For example it shows that selling 50 copies at $49 will make us $2,450. That's super powerful. Conclusion With just a few clicks, we can get Excel to do some magic computation and give us interesting information. For this example we had a simple formula. But you could do the same on something much more complex, and Excel will give you the perfect answer in no time.