In this day and age charts are widely used in business to represent data in another way apart from just digits in a Worksheet. Charts can add colour and style to a presentation and allow the reader to easily identify the trends and patterns that you may wish to relay. Once you have created your chart, it can very easily be modified to suit your particular taste or needs.
Let’s look at creating and formatting a basic spreadsheet to chart the income of the various Departments within a small organization.
Create a new blank Workbook.
Click on cell A2.
Type in the word Department.
Click in cell A3 and type in Administration.
Click in cell A4 and type in Marketing.
Click in cell A5 and type in Finance.
Click in cell A6 and type in Stores.
Click in cell A7 and type in Total. Notice here that the contents of the cells A2 and A3 spill over into cells B2 and B3. This is because cells B2 and B3 are empty. Once these adjacent cells have data in them, the data in cells A2 and A3 will appear to be cut off, although this is not really so. If you were to have a formula result that is too wide for a column, the cell would display ####. This is telling you not that your formula is incorrect, just that your column is too narrow for your result to be seen.
Click in cell B2 and type in the month January.
Click back in cell B2 and using your Fill handle, fill to the right until you reach December (cell M2).
Click in cell B3 and type in 1000.
Click in cell B4 and type 1500.
Click in cell B5 and type 1750.
Click in cell B6 and type in 2000.
Click in cell C3 and type in 1750.
Click in cell C4 and type 1600.
Click in cell C5 and type 2300.
Click in cell C6 and type in 1900.
Now highlight the range B3:C6 and using your Fill handle, fill right until you reach December (column M).
Let’s put in the totals in for the months now, by clicking in cell B7, selecting the AutoSum icon (the backward Z under Editing options on the Home tab) , then Enter.
Click back on cell B7 and using your Fill handle, fill the AutoSum formula to your right until you reach column M (December).
You will notice now that the Department names in cells A2 and A3 appear to be cut off, and the month September in J2 also seems to be cut off. If you click in either of these cells, you will note that the whole cell entry is still there as you can see it in your formula bar. Let’s have a look at quickly best-fitting the width of these columns, so we can see their whole contents.
There are a few different ways to best fit a column. You can go via the Format>Column Width option under Cells styles on the Home tab. Following this method you can type in the exact column width you desire, or you can double click between the column references on your Worksheet. Let’s try this as this is by far the easiest and quickest way of best-fitting a column.
Wave your mouse on the cell border between the A and B column heading (black writing, grey background) until your mouse changes to a black cross with a left and right pointing arrow.
Double click with your left mouse button.
Now wave your mouse pointer on the cell border between J and K until your mouse changes to a black cross with a left and right pointing arrow.
Double click with your left mouse button.
Pretty simple hey!!! If you wanted to stretch or shorten your columns, you could wave your mouse over the desired the column borders until it changes to a black cross, then holding your left mouse button down, drag either left or right to widen or shorten your column.
The same technique will work for widening rows, except that when you wave your mouse over the row border, the black arrow with the left and right pointing arrows will change to a black arrow with an up and down pointing arrow.
Let’s pretty up our Worksheet just a little now by using some basic formatting techniques. We will not go into this too deeply, as we stated earlier in the course, we believe it is far more important to get the “guts” of the spreadsheet correct, rather than having a pretty Worksheet that does not come up with the goods, so to speak!
Highlight the range A2:M2.
Click the on the B under Font options on the Home tab. This is your bold key and will bold our highlighted text.
Although we have stated before that it is easier to work with text and numbers that are aligned correctly (that is numbers to the right and text to the left of a cell), if you were required to centre them for “looks” sake, you can do so easily.
Keep the range A2:M2 selected and select the Center icon under Alignment options on the Home tab. This will center the contents of your cell. T
Note - Remember to wave your mouse over an icon to see what action it will perform if selected.
Let us now select the range A7:M7 and bold the totals of the months as we have done previously
Now highlight the range A3:A6 and select the I under Font options on the Home tab. This is your “italics” key and will italic the Department names for us.
Let’s give our numbers a dollar value now.
Highlight the range B3:M7.
Select the $ icon under Number options on the Home tab.
You will notice that column L is full up with #####. Best fit this column using the technique described above.
When you give a range a dollar value within Excel, it, as a default, will place two decimal places within your cell. To remove them do the following:
Select the range B3:M7.
Click on the Decrease Decimal Spaces icon under Number options on the Home tab. This is the icon with the .00 and .0 and a blue right pointing arrow on it. If you click it once, you will remove one decimal place, and if you click it again, you will remove the next decimal place.
Let us now put a heading on our table. Click in cell A1.
Type in CY2008 INCOME and click Enter.
Now click back in cell A1.
Highlight the range A1:M1.
Now select the Merge and Center icon under Alignment options on the Home tab. This will merge and center the words CY2008 INCOME across the cells that you highlighted.
Note here that it is NOT a good idea to use Merge and Center on anything that you may wish to use in a calculation. Our advice is to use it to format a heading only. You will run into all sorts of problems if you try to do calculations with a merged cell(s).
Let us now bold our heading and change the font size and cell alignment.
Click in cell A1 (note here that your range A1:M1 has now all become cell A1 as indicated by the Name Box (the white box above column A)
Bold your heading using the B icon under Font options on the Home tab.
Now change the font to a font of your choice by selecting the appropriate font name from the Font drop down box above the bold icon
Once you have selected a font, change it’s size to 26, by selecting this number from the Font size box (located immediately to the right of the Font box.
Now let’s put some borders on our data.
Highlight your whole table.
Select the drop down arrow to the right of the Borders tool under Font options on the Home tab.
Select the icon named All Borders with your left mouse button.
If you wished to use different types and widths of lines for your borders, or change the colours of them, you will need to do this via the More Borders option at the bottom of the Borders list which has a much wider range for you to choose from.
OK, now we are ready to create our chart. We will only create a basic chart in this lesson, as we go into much more detail during the Excel – Level 2 training course.
Creating a simple chart is easily done by following these steps.
We should firstly save our file, so let’s do that now and save it as Charting.xls.
Highlight the range A3:M7.
Click the Insert tab and under Chart options select Column to see a list of Column charts that you can select from. If you want to view all chart types, select Charts dialog launcher and the various chart types will be displayed.
Select the first option under 2-D Columns the clustered column chart.
The chart will show itself in the middle of your data, and you will notice a new tab Design appear at the top of your screen.
Firstly, let's move our chart to it's own worksheet. Select the Move Chart button on the far right hand side of the toolbar under Location options. The Move Chart dialog will pop up in front of you. Select the New Sheet option and click OK.
Select the first Layout 9 under Chart Layout options. To see all the chart layouts, click the scroll bar.
Click where it says Chart Title and give your chart the title CY2008 INCOME.
Click the Axis Title on the left and enter in Dollars
Click the Axis Title at the bottom of the chart and enter in Months
Click the scroll bar under Chart Styles and select Style 30.
As you can see, charts really are very easy to create and just as easily they can be modified. One thing of great importance to note with charts is that if you change your source data, your chart will update to reflect these changes.
If you wish to modify any part of a chart, select it and make changes from the Design tab. If, for instance you do not like any of the Chart Layouts, you can develop your own style by selecting the series you wish to colour, then going to the Home tab and selecting Fill color under Font options.
Let’s have a look at creating a simple pie chart from non-contiguous data (data not joined together) now. This is done in much the same way as charting from contiguous data.
Let’s chart the Total Income for the year by Department
Click in cell N2.
Type the word Total.
Click in cell N3 and select the AutoSum icon from Editing options on the Home tab, then select Enter.
Click back in cell N3 and then using the Fill handle, fill down to cell N7.
Format the Total column so that its formatting is the same as the rest of the table.
Click in cell A3 and select the range A3:A6.
Holding down your Ctrl key, select the range N3:N6.
Click the Insert tab and under Chart options select Pie to see a list of pie charts that you can select from.
Select the first option under 2-D Pie.
The chart will show itself in the middle of your data, and you will notice a new tab Design appear.
Select the first Layout 6 under Chart Layout options. To see all the chart layouts, click the scroll bar.
Click where it says Chart Title and give your chart the title CY2008 INCOME BY DEPARTMENT.
You now have a pie chart embedded in your data as an object (which is the default). This chart can be moved or sized easily. If you wish to move an object, click inside the object (in this case a pie chart) so that it becomes active. You will know it is active when you see 8 handles (depicted by three dots) around the edge of the pie chart. If you then hold your left mouse button down until your mouse pointer changes to a cross with four arrow heads, you can move your pie chart to it’s desired location.
You can also change the size of it easily, by selecting any of the 8 handles around the outside of your object until your mouse changes to a double headed arrow, then drag in the desired direction. Note here that if you select a corner handle, your object will be sized relatively, in other words the width will change relative to the height and vice versa as you drag.
Pie charts can be modified in exactly the same way as all other charts, by double clicking to select the part of the chart you wish to modify. Note as with all other charts, if you make a change to your source data, your pie chart will update to reflect the changes.
In this day and age of computers where we now have many files that have multiple users, you can protect all or part of a Workbook easily. Protecting Workbook data makes it very difficult for specific cell values to be changed, either accidentally or deliberately. Some reasons for protecting your data could be:
To direct others to specific cells that they can input into by making it impossible for them to enter data anywhere else on a Worksheet.
To prevent accidental deletion, or modification of essential values within a Worksheet that may be needed to perform specific calculations.
To prevent accidental deletion, or modification to essential and sometimes very complicated formulas within a Worksheet.
Worksheet protection is a very valuable and useful tool, but using it can sometimes seem a little confusing, as there are actually two separate processes that must be followed to protect data.
The first step is to unlock any cells that will require editing. The second step is to apply Worksheet Protection.
Let’s have a look at how we would apply Worksheet protection to the file Charting.xls that we used previously, and how we would unlock the December figures for each Department, so that they can type their own figures in there.
As a default, all cells within Excel are locked by default. However, you can still change or edit these cells because the Worksheet or Workbook is unprotected. The first step to using data protection is to unlock the cells that need to be changed when we apply Worksheet Protection.
Open the file Charting.xls if it is not already open.
If you need to, move your pie chart out of the way using the technique described above so that you can see the range M3:M6.
Highlight the range A3:A6 with your mouse, then go to the Home tab Cells options select Format>Lock Cell (Note that the button to the left is highlighted as all cells are by default Locked) or right click and select Format Cells select the Protection tab and then deselect Locked (it by default has a tick in it) and click OK. This will unlock the cells you want to be able to type in.
Now we have unlocked our cells, we can apply Worksheet Protection to our data.
Select the Review tab, then Protect sheet under Changes options and the Protect Sheet dialog box will appear.
Depending on which version of Excel you are using, the options may be slightly different in the Protect Sheet dialog. We do not wish to use a password at this stage, and we will just accept the defaults as they are in this case, so just click OK.
OK, let’s have a go at using our protected Worksheet.
Click in cell L3 and type the number 1234. You will notice that as soon as you start to type, Excel will display a message that tells you the cell or chart you are trying to change is protected and therefore read-only.
Now click in cell M3 and type 1234, then Enter. This time your entry will be allowed, as would any entry that you typed in M4, M5, or M6.
You will note that when you make a change to either M3, M4, M5, or M6, that the formula in M7 will update. This is because locked cells that contain formulas will still change in accordance with the data that is used to calculate them.
You can unprotect a Worksheet in the same way that you protected it in the first place.
Go to the Review tab, then Unprotect sheet under Changes options to unprotect your worksheet so you can use it as before.
OK, so we have looked now at Worksheet Protection. However anyone with a working knowledge of Excel, could figure out how to unprotect a sheet if they wanted to, so Excel offers you the ability to use a password to protect your Sheet. Passwords are case-sensitive. One thing you MUST be aware of when using a password is that if you lose or forget your password, it cannot be recovered, so it might be a good idea to write down your passwords somewhere and the Worksheets that they relate to and store them in a safe place. It is also a good idea when using a password to use a combination of letters, numbers and symbols.
Select the Review tab, then Protect sheet under Changes options and the Protect Sheet dialog box will appear.
Click in the Password box and type in the word password then click OK.
Excel will ask you to confirm your password by retyping it, so lets retype in the word password then select OK.
Now click in N3 and type in 1234 as before you cannot make a change here, and a message box will pop up telling you so.
Now click in M3 and type in 5678 and click Enter. As before, no warning will appear and you are able to change cell M3.
Let’s try and unprotect our sheet now.
Go to the Review tab, then Unprotect sheet under Changes options
You must now type in the correct password for your sheet to be unprotected. Type in the word passwording, then click on OK.
You will get an error message from Excel advising you that the password that you typed is incorrect. You will also get a hint from Excel, that maybe the caps lock key could be on, just in case you have typed the right password in the wrong case.
Click on OK and we will try again.
Unprotect Sheet and type in the correct password (password).
Select OK and your Worksheet will now be unprotected again.
So remember, if you want to use the Protect facility, you must remember to unlock the cells that you wish to be changed before your apply sheet protection. You can protect a Worksheet with or without a password, but remember that the password does not prevent access to the data, but instead prevents the worksheet being unprotected. Once the protection system is in place, it is impossible for a user to edit, change or delete the contents of a locked cell.
If you try, Excel will display a message advising you of this.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.