Other Excel Add-In, Templates and Training
CREATING A SIMPLE SPREADSHEET & CHARTING IT/
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. Charts can be created in a number of ways, but if you have never created a chart before, the Chart Wizard within Excel is probably the best way to start. It will walk you through the process of setting up a chart prompting you for the information that you need to complete the process. Once you have created your chart, it can very easily be modified to suit your particular taste or needs.
Creating a Basic Spreadsheet
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
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 on your standard toolbar) , then
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 option on your Worksheet Menu Bar, following this method you can see the options available to you to widen or shorten your columns, 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.
Formatting the Spreadsheet
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!
We will use some of the options on the Formatting toolbar in this case. Note that all of these options are available via the
Format option on the Worksheet Menu Bar.
Highlight the range A2:M2.
Click the on the B on the Formatting Toolbar. 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 on your Formatting toolbar. This will center the contents of your cell. The
Center icon is normally four icons to the right of the Bold icon. 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 by selecting the B on the Formatting Toolbar.
Now highlight the range A3:A6 and select the
I on the Formatting toolbar. This is your ďitalicsĒ key and will italic the Department names for us.
Letís give our numbers a dollar value now.
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 on your
Formatting toolbar. 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 CY2004 INCOME and click
Now click back in cell A1.
Highlight the range A1:M1.
Now select the Merge and Center icon on your
Formatting toolbar. This will merge and center the words CY2004 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. My 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
Bold your heading using the toolbar icon.
Now change the font to a font of your choice by selecting the appropriate font name from the
Font drop down box to the left of the bold icon on your Formatting Toolbar.
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 left of the bold icon on your
Formatting Toolbar, and to the right of the Font Name 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 icon on the Formatting Toolbar. As a default, the borders icon is the third from the left on your
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
Format>Cells/Borders option which has a much wider range for you to choose from.
Charting the Data
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
Highlight the range A2:M7.
Select the Chart Wizard icon (the one with the blue, yellow and red bars on the
The Chart Wizard dialog box should pop up in front of you and you should be on
Step 1 of 4.
Notice here the different categories of charts available to you under the heading
Chart Type: to the left of the dialog box. To the right of the dialog box are the different chart sub-types. If you wanted a preview of how your data would look in different types of charts, click on the particular chart that you like and click the button
Press and Hold to View Sample with your left mouse button. This will give you a basic preview of how your data will look if that particular type of chart were selected.
We are actually going to select Column under
Chart Type: and we want to have the first sub-type selected under
Chart Sub Type:
Select the Next button to move on to
Step 2 of 4 of the Chart Wizard.
You should have the Data Range sheet tab in front of you. We need to make sure that
Rows are selected under Series In:
Select the Next button to take you to
Step 3 of 4 of the Chart Wizard.
Step 3 is where you would put in a title to your Chart, so letís type
CY2004 Income in the Chart Title box.
Under Category (x) Axis type the word
Months. Your X axis is defined as the horizontal axis at the bottom of a chart.
Under Category (y) Axis type the word
Dollars. Your Y axis is defined as the vertical axis to either the left or right (or both) or your chart.
We donít need to make any further changes in our simple chart, so lets select the
Next button to move on to the final step of the Chart Wizard dialog box.
In Step 4 of 4 of the Chart Wizard you have two choices. You can select either
As a New Sheet, which will place your graph on a new Worksheet and call it Chart1 (you may change this name if you wish), or you can select
As an Object In which will place your graph over your data as an object that you can move to its desired location. We are going to select
As a New Sheet and Type CY2004 Income where it currently says Chart1.
Your chart should be produced on a separate Worksheet called
CY2004 Income. They 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, you need to select that particular part, then
double click to see the options available to you in the particular area that you have selected. For example if you
double click on the maroon bars, you will see the option Format Data Series where you can change the colour of your bars,
Chart Type where you can change the type of chart you have selected, ie from a bar chart to a pie chart,
Source Data which is where, if you wanted, you could change your source data.
Add Trendline which obviously will give you a trendline, and Clear which when selected will delete the particular series you have highlighted.
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 the Standard toolbar, then select Enter.
Click back in cell N3 and then using the
Fill handle, fill down to cell
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
Holding down your Ctrl key, select the range
Select the Chart Wizard icon from your
Select Pie under Chart Type and select the
second pie chart under Chart Sub-type.
Click the Next button to take you through the Wizard to step 2.
No changes to make here, so lets select the
Next button to move on to step 3.
Under Chart Title type in the heading
CY2004 Income by Department.
Select the Next button to take you to the 4th and final step of the
Letís select As an Object In, then select
Save your Workbook.
You now have a
pie chart embedded in your data as an object. 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 (squares or circles depending on which version you are using) 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
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
Enabling 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
Highlight the range
M3:M6 with your mouse, then go to
Format>Cells which will display the Format Cells dialog box, and select the
You have two options within this box. The first option
Locked, if selected, will prevent a user from changing, moving, resizing or deleting the selected cells. The second option
Hidden, if selected, will hide the formulas within your Worksheet so that if a cell containing a formula is selected, you will not be able to see what the formula is in the formula bar. Letís select the
Locked option until it appears without a tick., then click on OK to unlock our selected cells (M3:M6).
Now we have unlocked our cells, we can apply
Worksheet Protection to our data.
Select Tools>Protection/Protect Sheet 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.
Disabling Worksheet Protection
You can unprotect a Worksheet in the same way that you protected it in the first place.
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 Tools>Protection/Protect Sheet.
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
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
Letís try and unprotect our sheet now.
Select Tools>Protection/Unprotect Sheet.
You must now type in the correct password for your sheet to be unprotected. Type in the word passwording, then click on
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.
Select Tools>Protection/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.
Goto Next Lesson
Back to Excel Add-in, Templates and Training Main Page
Get the Excel VBA Training for free when you purchase our Technical Indicators now!