Excel Chapter 3 Video 3

In this video, I will show you how to make a 3D pie chart, change a chart layout, place a chart on its own sheet, move and delete tabs and use goal seek. Let’s start by making a pie chart. I’m using the same spreadsheet as the previous video. The only change I have made is I have unmerged the month cells in row 4. This will make it easier to create our Pie chart. Creating a pie chart is a s simple as selecting the data and labels and then clicking the pie chart button on the insert tab. However, sometimes your data isn’t in adjacent cells. By clicking and holding the Ctrl button, you can select your data in non-adjacent cells. I am going to make a pie chart that reflects the total number sold for each month for this first item, item number 696-33. I will start by clicking on cell B6, pressing the Ctrl button then selecting the rest of the total # sold cells for each month in row 6. Notice how each cell I select turns blue. Now I will click the months individually, continuing to hold the Ctrl button. Now I will click on the insert tab and select pie chart and 3D pie chart. A 3D pie chart is created with a legend. Notice the three new ribbons at the top of the page. I want to change my chart layout so that the month names are around the pie. Simply click the first chart layout under the chart layouts grouping on this new Chart Tools Design ribbon. Now I want to move my chart to its own sheet. I will click the movie chart location button, select “New Sheet” and I will name it “Item # 696-33”. Finally, I will give the chart a title by clicking on the Chart Tools Layout tab and selecting Chart Title and center overlay and typing “1st and 2nd Quarter sales”. Notice my pie chart is on its own tab. I will rename sheet 1 by right-clicking on the tab and typing “Sales Figures”. Tabs can be reordered by simply clicking and dragging. Now I will delete sheet 2 and sheet 3 which are empty by right-clicking and choosing delete. The last thing I will show you in Chapter 3 is how to use Goal-Seek to solve simple mathematical equations. I have created a total sales row and placed the sum function in it to calculate the total number of items sold for that month. I have also frozen the panes for row 5 and above and column A. Let’s say for example, you want to know how many more of item # 459-15 need to be sold in order for total sales in the month of February to reach 87,000 units. Goal-seek in Excel can solve any mathematical equation with one variable. Open the Goal Seek dialogue box by clicking on the data tab and selecting What-if Analysis and then Goal Seek. This dialogue box has three fields. We will set cell D58 to the value 87,000 by changing D54. In order for goal seek to work the “Set Cell” field needs to be a formula or function that is dependent on the cell that you are changing. Click ok. This dialogue box tells us that a solution was found. Goal seek works using numerical methods. In other words, Excel will change the value of D54, check to see if it got closer or further away from the target value of 87,000, then based on that it will continue to adjust D54 until it zeros in on that target value. This all happens in a fraction of a second. So goal seek told us that if we want to reach a total number sold of 87,000 units by only selling more of item number 459-15, we would have to sell 1,689 of that item.