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.