• List of Excel Add-In
• List of Excel Templates
• List of Excel Training
• Back to Free Excel Training Index

CREATING A SIMPLE SPREADSHEET & CHARTING IT/
PASSWORD PROTECTION

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 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 on your standard toolbar) , 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 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.

• Highlight the range B3:M7.

• Select the $ icon from your Formatting Toolbar.

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 Enter.

• 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 Name Box.

• 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 Formatting Toolbar.

• 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 Charting.xls.

• Highlight the range A2:M7.

• Select the Chart Wizard icon (the one with the blue, yellow and red bars on the Standard Toolbar.

• 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.

• Click Finish.

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 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.

• Select the Chart Wizard icon from your Standard Toolbar.

• 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 Chart Wizard.

• Let’s select As an Object In, then select Finish.

• 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 changes.

Worksheet Protection

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.

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 M3:M6.

• Highlight the range M3:M6 with your mouse, then go to Format>Cells which will display the Format Cells dialog box, and select the Protection tab.

• 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.

• Go to Tools>Protection.

• As the Worksheet is already protected, your sub-menu will contain a command to unprotect your Worksheet, so let’s select Unprotect Sheet.

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 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.

• 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 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.

• 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.