In this blog I aim to show you how to easily create a chart in a Word document or in PowerPoint, say for reporting or a presentation.. We will use the scenario of counting types of vehicles passing a given location over the period of a month. We will group the vehicle type by 4x4, Lorry, Bus, Car & Motorbike. Again, you can apply this to anything you see fit.. Most reports are in reality generated from data, the system is automated, but for a small company or individuals that have to send out a daily report and do not have the benefit of an automated system, this will work fine.. I will show how to apply charts in MS Excel soon, where is reality most data will be stored.
Section 1: Creating the Chart
Section 1: Creating the Chart
- Open a new Word document (or the current daily report)
- Now go to the Insert Tab & Select Chart, there are many different types of charts to choose from. As always, I will show the basics here and let you play about to discover more. So for this blog, I will select the Default Column Chart..
- Once you select your chart, it will displayed on the Word document and an instance of MS Excel will open with a small table which you can adjust, as seen below:
- The basic table has 3 columns & 4 rows of data. We require 31 days and 5 vehicle types. I am going to create the dates (1 - 31) in cells A2 - A32 and vehicle types in cells B1 - F1, so dates down the side and vehicles alone the top..
- Below the table it tells you how to resize (To resize chart data range, drag lower right corner of range.) So resize the table now by dragging the lower right corner to cell F32 & delete the resize text... You will notice the chart has changed on the Word document.
- Ok, now we want to name out vehicle types and show 31 days. So along the top in B1 type 4x4, C1 - Lorry, etc to F1 - Motorbike...
- In cell A2 type 1 & use Fill Series to auto-populate the cells to A32 (as shown in a previous blog - Filling cells quickly - Fill Series)
- You will now see the days of the month along the bottom and the vehicle types colour coded on the right. We have not deleted the data yet, so will show some bars in the bar chart. Delete the data now(cells B2 - D5), and the bar chart will be clear..
- Now add your own data for the first of the month. Not very impressive, but don't worry as the days pass the chart will build up and be more impressive.
- Now, just close the Excel sheet as it is saved through Word when you save the document.
- To edit the data, just Right Click on the Chart and select Edit Data from the popup menu. Note that the chart type, colours and legends can all be changed anytime, so play about with it to you find something you like..
Added extra - I want to see how the chart looks fully populated, but rather than type the data for 5 vehicle types over 31 days (155 cells), I have used the RANDBETWEEN function to give me random number between 2 given numbers and pasted it into each vehicle type column.. Here are the functions:
=RANDBETWEEN(10,30) For 4x4, you can just copy and paste =RANDBETWEEN(10,30) into the formula bar and set your own numbers. Remember, this is just to populate the chart (to see how it looks) before you use real data and submit the report...
=RANDBETWEEN(10,40) Lorry
=RANDBETWEEN(20,60) Bus
=RANDBETWEEN(55,350) Car
=RANDBETWEEN(5,15) Motorbike
Below is the "test" chart:
No comments:
Post a Comment