Charts the Hard Way

To follow the steps in this you will need to download this file Basic Concepts it has been designed for Excel 2007

The Hard way to Create a Chart

                With the Charts Spreadsheet being the active sheet go to the Insert Tab and there is a group called Charts.  This Group has several kinds of Charts that you can choose from.  There are Column Charts, Line Graphs, Pie Charts, Bar Charts, Area Charts, Scatter Charts, and Other Charts.  The Most common and universal Chart is the Column Chart.  This is the one that you will be spending most of your time working with.

                When you click the Column Button a Dialog Box will open and it has several different styles of Column Chart that you can use.  The most common Chart in this Dialog is the 2-D Clustered Column Chart, or the first one in line.

                Click on the 2-D Left most Button and two things will happen.  The First thing that most people notice is that there is now a blank box covering part of your data.  The second thing, sometimes this needs pointed out before people notice, is that there are now three more Tabs on the Ribbon.  They are Design, Layout, and Format.

                You can move the Empty Box around on the Spreadsheet so that you can see all of you data.  As you hover over the Blue Surround of the Box you will see the Crossed Arrows, when you get this symbol hold your Left Mouse Button down and move the Box to where you want it.  There are also Eight sets of three dots, one in each corner, and one in the middle off each of the runs, these can be used to make the Chart larger or smaller.  As you hover over the three dots your cursor will change from the crossed arrows to either a two headed arrow pointing up and down, a two headed arrow pointing right and left, or a two headed arrow pointing at a diagonal.  Then you can hold your Left Mouse Button and drag the direction of the arrows to resize your Chart.

                With that said, how do you get your chart to show information?

                On the Design Tab there is the Data Group and in the Data Group there is a button called Select Data.  Click this Button and a Dialog Box will appear, it has the title Select Data Source.

  In the Chart Data Range it will automatically choose the Cell that your Cursor is in at the moment.  As long as you can see the black around white text all you have to do is select where you want the information to be pulled from.  Select Cells A5 to G16, you will see the Dancing Ants around the selected area.  You will also see =Charts!$A$5:$G$16 in the Chart data range text box.  Also there will also be information in the Legend Entries (series) section and the Horizontal (Category) Axis Label section.  Now Click OK.

                Is this more like what you expected?

                I tend to think that there is too much information in the Chart now.  Do you?

                That is okay because you can deal with that, as soon as I show you how that is.

                The first thing that you need to do is figure out what information it is that you want to see.  Do you want to see just the Totals or Just the Item information?

There are two ways that you can accomplish this task, I will show you how to do both.

             For now I will show you how to just show the Totals for the months.  The first thing that you need to do is go back to the Select Data Button, which is on the Design Tab in the Data group.

             There are a couple of ways that you can get the exact information that you want.  One way is slightly easier than the other.

                The first way that I will show you is working inside of the Select Data Source Dialog Box.

Months in the Legend

Product s in the Legend

                There is a button in the middle of the Dialog Box, it says Switch Row / Column, press this button and your information will change sides as seen in the sample above.  Once this is done, you can now remove the information that you no longer want or need.

                Click onto the White Bread so it has the Blue around it and press the Remove Button, continue to hit the Remove Button until the only items left are Total and Average, then remove Average.

                Now click the Switch Row / Column Button again and then click OK.  Now you should have a Column Chart that has the Totals in the main area and the Months in the Legend.  Notice that the Colors are corresponding, January is Purple, February is Dark Blue, etc, etc…

                Have you noticed that the data used to create the Chart has a Green Box around the Legend Information, a Purple Box around the Label “Total”, and a Blue Box Around the Data for the Totals.

                Now that you know how to get the information that you want the hard way I’ll walk you through getting the information an easier way, use the Undo Button, it is on the Quick Access Toolbar it looks like an Arrow that is Hooked to the Left, or you can use the CTRL + Z Buttons on the Keyboard.

                Now that you have the blank Chart once again, make sure that the Chart is still active; you will know that is active if you still have the Three Additional Tabs on the Ribbon.

                On the Design Tab click the Select Data Button and then use the Backspace Key, this should clear the Chart Data Range portion of the dialog box.  Hover over Cell A5 and hold your Left Mouse Button down and pull to Cell G5 then let go.  The Chart Data Range should now read =Charts!$A$5:$G$5.  Now hold your CTRL Key down and hover over Cell A15.  Once you are hovering over Cell A15 continue to hold the CTRL Key down and hold Your Left Mouse Button down and move to Cell G15.  The Cart Data Range should now read =Charts!$A$5:$G$5,Charts!$A$15:$G$15, click the OK button and you will see the Legend now says Total and you have the months Jan to June as the Labels for the Columns.

                Congratulations, you now know the Hard Way to create a Chart.

                There are some people who prefer to create the Charts this way.

                I am going to have you Delete the Chart now, click onto the Blue Border or the Chart and use the Delete Key.  By clicking the Blue Border you will ensure that the Chart has been selected instead of one of the objects inside of the Chart.