Microsoft Excel, an innovative way to prepare year end charts
As we approach the end of the year 2006 many of us face a challenge of presenting a summary for the performance during the entire year. One of the creative way to summarize the performance is to prepare a chart showing
the details for every month or every day, as detailed as you want to go. Doing this work by hand would take long and if there is some change in data whole work has to be done from the beginning. Computer can be used to complete this work quickly and efficiently.Microsoft Excel, part of
microsoft professional package, can be used to prepare such charts. To show you the steps of preparing chart I will follow a example given below.Example: Following is the table showing profits of ABC company for every month during the year 2006. Prepare a summary for the performance of the company.January: $20000 May: $2500 September: $6000February: $15000 June: $2000 October: $12000March: $18000 July: $1000 November: $12000April: $10000 August: $8000 December: $16000Follow the below given steps:1. Open
Microsoft Excel by going toStart - All programs -
Microsoft office -
Microsoft office excel2. In the first cell (Cell: A-1) of the new file type the following “Company Name: ABC”3. Below that line (Cell: A-2) type the following, “Performance summary for the year 2006”.4. In the cell A-4 type the following, “Month” In the cell B-4 type the following, “Profits”5. Fill out the values given in the problem. (To get the Dollar sign after typing the number select the cells B-5 to B-16 by pressing Ctrl key and clicking left button of mouse. After selecting do the following: Format - Cells - Number - change the category to “Currency”.)and your excel sheet should look like following,A B 1 Company Name: ABC2 Performance summary for year 200634 Month Profits5 January $20000.006 February $15000.007 March $18000.008 April $10000.009 May $2500.0010 June $2000.0011 July $1000.00 12 August $8000.00 13 September $6000.0014 October $12000.0015 November $12000.0016 December $16000.006. Now click the Insert menu and then click on “chart”. A dialog box for charts will open.7. It will ask for chart type and chart subtype. For the example given we will use Chart type as “ Column” and chart subtype as “clustered column”. (Description of Chart subtype is provided in the lower part on right side.)8. Click Next and a new dialog box opens.9. Here you have to provide the data range. Data range is the data for which we are preparing chart. Sometimes the software itself picks up the data range. The data range will be shown by blinking box surrounding the table we just prepared. Check your data range if it is automatically selected by software. If it is wrong or software does not select on its own follow the next step to input the data range.10. In the line provided after the word Data range type the following: =Sheet1!$A$4:$B$16 “Sheet1” is the name of the sheet on which we have table.11. For the “Series in” select “Columns” and press Next.12. The next dialog box will ask you for chart title. In the box for chart title type, “2006 Performance Summary”. For “Category (X)axis” type “Months” For “Value (Y) axis” type “Profits”13. Go to Legend tab given on the top of the dialog box. Uncheck the box Show legend if it is checked. After completing this press Next.14. The new dialog box will give user the option of saving the chart as a separate sheet of to save chart as a object in any of the sheet he is using. For our example we will save the graph as a object in sheet1. So select the second option “As object in” and select sheet1.15. Click Finish and the graph will be displayed right below our work.The background, font color as well as the color of the bars in the graph can be changed to the colors you like. The formatting is confusing and will be discussed in coming articles. Just so that you do not get confused prepare couple of self made examples and try to go through the steps given. Practice the steps very well and then you can go into the steps needed for formatting.The basic chart is prepared following the steps given and it is very useful in day to day life. I hope you are clear on how to prepare the bar chart with
Microsoft Excel. I know it is complex but if you practice couple of times you will be able to grasp everything better.
