A frequently asked question by many of our readers, so we thought we’d tackle this little task first, and show you how to make lovely graphs and charts to brighten up even the dullest of spreadsheets 🙂
Graphs and Charts are useful tools and visual aids for analysing data and if you use Excel for storing data of any kind, at some point or another you are likely to want to create a graph or chart. The functions of Microsoft Excel, and the chart wizard make it quick and easy to create a graph or chart from a table of data – providing of course that your data is in an appropriate format to begin with! The main thing that you want from your data is to have a table with headings, and also row labels if applicable. Secondly, you need to make sure that your cells are formatted correctly, so for example, cells containing numbers should be formatted as such, and monetary values should be formatted as ‘currency’.
Lets start by setting up a simple table to track the income from jobs that we invoice for each month:
Type ‘Month‘ in cell A1, and type the months January through December in cell A2 through A13. Alternatively, type ‘January’ in cell A2 and autofill the cells down to A13, you will see that the months are automatically completed. In cell B1, type ‘Income’. For each month, increase the income by £100. In cell B2, type “400,” in cell B3 type “500” and so on. The last month in the ‘Month’ column should be be in cell A13 and the last figure in the ‘Income’ column should be in cell B13. Make sure that column B is formatted to accept currency by highlighting the column, right click and select ‘Format cells…’ from the menu.
Once this is done, you will have a table that looks like this:
Next, highlight the data that you want to display in your graph. In this case, cells A1:B13
 Activate the chart wizard toolbar by selecting the ‘insert’ tab, then Select the chart or graph you want to use from the ‘charts’ group. In this case we will use a line graph.
 
Your graph will automatically be populated and you will see that a ‘chart tools’ group become available to you in the toolbar. From here you can modify the data source, layout and format of the graph.
If you need to modify the data source, do this by selecting the ‘design’ tab and then ‘select data‘. You will be presented with the dialogue box below where you can change the axes of the graph and change the data range that is included in the graph.
To add labels and headings or edit the existing ones, use the options under the ‘layout‘ tab in the ‘labels’ group. To edit the title of the graph, you can also click on the text and simply change it heading where it is on the graph.
Under the ‘format’ tab, you can change the colour, shape and alignment of the graph area and text, add pictures, backgrounds and much more! The possibilities here are endless! Once you are happy with your finished graph, you can change the size of the chart by grabbing a border of the chart and dragging it or you can cut and paste it into a new location.
To move the graph to a new sheet on the same workbook select the ‘design’ tab and then the ‘move chart’ button. You can rename the chart here and select a sheet to move it to or choose to insert the chart as a sheet on it’s own.
That’s all there is to it!
Handy Hint: If you modify the data that is included in the data table, the graph will usually be refreshed on it’s own. It will also refresh each time you open the workbook. If it appears that your graph isn’t updating for any reason, make sure that you check the data source to ensure that the graph is referring to the correct information.
Steph Middleton is an experienced Virtual Assistant and self-confessed computer geek! She is owner and manager of Outhouse and provides virtual assistant services to her clients, including spreadsheet and database design and support.
