Course presentation notes

·        Check all computers are working, on, have the application and launch okay.

·        Check all toolbars are the same.

·        Check all exercises (if required) are loaded on the machines.

·        Check you have all (if any) photocopied handouts ready.

·        Write one's name, date and course title on the board (If supplied).

·        CHANGE THE NUMBER OF SHEETS TO 20.

·        Introduce yourself, the course and explain the fire exits, toilets, coffee and ask them to sign in (if relevant).

·        Tell them a bit about yourself, your background and knowledge.

·        Explain the timing of breaks, (1100am ish / 1500pm ish) lunch (1300pm) and estimated time of course completion.  (1600-30pm)

·        Ask the candidates to introduce themselves, assess their level of knowledge and ask them what they hope to achieve from the day.

·        Explain that what you will be showing them is ‘My preferred' way and therefore not definitive; there are usually other ways of achieving similar if not the same results.  If they know of a different way of doing something, great.

·        Hand out Course Evaluation Forms and get candidates to fill them in.

·        Clear the relevant/amended files you have used from each PC.

·        Check all PC's are shut down properly and switched off.

·        Make sure the room is left clean and tidy - as you would like to find it next morning.

·        Enter Course Evals onto the website.

To be written onto the board

1.                   Introduction / Overview

2.                   Layout / Navigation

3.                   Copying

4.                   Data, Sums, Formulae and Functions

5.                   Cell Referencing

6.                   3D Spreadsheets & File Linking

7.                   Formatting & Protecting

8.                   Printing

9.                   Charts and Graphs

Excel is the Microsoft Spreadsheet program.  It organises numbers and numerical data.  It allows us to analyse figure create reports, charts and graphs.  Similar to a filing cabinet, but takes up less space and very much more flexible and powerful.

We will:

·         Enter data; create functions and formulae to analyse the data.

·         Copy data, duplicate it and move it around.

·         Refer to data in particular cells and use this to cross reference with other information.

·         Work across multiple spreadsheets AT THE SAME TIME.

·         Make our spreadsheets look fab for reports

·         Lock formulas and cells so they can't be altered.

·         Set up and print our spreadsheets.

·         Use the data to create charts and graphs.

2:         Layout and Navigation

a) - Layout

Go to Start, Programs, [Microsoft OFFICE] and ‘EXCEL'.

OR double click the EXCEL icon on your desktop.

The application launches into typical screen:

Blue Application bar – Application / Document name and MINIMISE, MAXIMISE and EXIT

Drop Down Menus – Anything you want to do is here.  Click once and menu will ‘drop down' These also have several ICONS that are on the standard toolbar.

XP:      To the right of the Standard toolbar, there is a question box/dropdown.  This is a quicker way of accessing HELP.

Standard Toolbar – The toolbar that contains the most commonly used controls in Excel.  Hover the mouse over, its function will appear.  Toolbars can be added or removed from here – in the view menu.  NOTE: Excel will remember the last changes you made when you exit and will revert to them when you come back to it.

Cell Name Window – (White rectangle, left, with grey arrow dropdown).  As you click on different cells in Excel, its name or location will appear here.  You can always tell where you will be inserting data.  Particularly useful if heavy formatting has been applied and you can't see which cell is selected.

Formula Bar – To right.  Whenever you enter data into a cell, it will also appear here.  Formulas and functions can be amended here, without having to double click the cell you want to amend.  As you enter data into the cell, a red cross and green tick appear.  These are ‘cancel' or ‘enter'.  You can use the mouse on these, or just press ‘Enter'.

Spreadsheet Window – Similar layout to your school maths books - Comprising of CELLS – little white boxes with grey borders where you will enter your data.  Across the top, in grey are COLUMN LETTERS – you can't type there. -  Down the left, ROW NUMBERS.  Every cell has a name.

Scroll Bars – Down the right and across the bottom of the spreadsheet.  Grab the darker 3d bit with the mouse and use to navigate around the view of a sheet.  When you see where you want to type, click in the relevant cell.

Tabs – At the bottom left of sheets.  These represent the pages in your workbook.  Clicking on these turns them white.  The white tabbed one is the one we work on.  We can add more pages or take them away as necessary.  We can also set Excel to remember how many pages to open up when we launch it - (Coming later).  We can also re-name these sheets – Later…

Double arrows – (Bottom Left).  These help you move through the pages of the workbook – but only if there are too many pages to see all in one go.  If some are out of view (e.g. behind the scrollbars) you can use the inner 2 arrows to move one sheet at a time, or the outer 2 to move to the first or last sheet.

Open ‘ALBUM SALES' (File Menu, Folder icon or CTRL+O)

To move around the spreadsheet:

The Mouse – Allows you to choose a cell and click in it.

Arrow Keys - These move you a cell at a time in the desired direction.

HOME Key – Will take you to the beginning of that row.

CTRL+HOME – Will take you straight to cell A1.

Press (and release) END – Then any arrow, this will take you to the furthest extent of that block of data in the direction selected.  Once there, press/release END and [LEFT ARROW] again, this will take us to far right column (IV)(256 columns).  Go to END/DOWN ARROW (65,536) this is how large each sheet is, exactly 16,777,216 cells.

PAGE UP/PAGE DOWN – Moves the view up or down 1 screens worth of cells at a time.

CTRL+Page Up/Page Down – This moves us a ‘page' (Sheet) at a time through the workbook.

On the ‘EDIT' menu, choose ‘GO TO' (Or CTRL+G) Next to ‘Reference' type a cell name.  (Don't worry about the ‘Special' button that pops up.)  The highlight will jump straight to that cell.

ADV: Takes you directly to a specific cell.

DISADV: You need to know that specific cells address.

Mouse:

To activate changes in cells, particularly formatting, you must select them.

1.       Click on the first cell you need to select.  You can click & hold the mouse button and drag across to the end of the area you want to select.

2.       Click on a column or row number or letter to select that column or row.  Or click and drag across the letters or numbers to select multiple rows and columns Or click on 1 then shift and click on the last one you need.

3.       For rows and columns not next to each other, CTRL+ clicking will select them.

4.       To select the entire sheet: Grey box at top left (left of A, above 1).

Keyboard: Click with the mouse in the first cell of your selection, then you can use:

1.       SHIFT and the arrow keys to select cells in the corresponding arrow direction.

2.       CTRL & Shift/arrow will select all the data in a table in that direction.  Once selected, do it again and it will select the entire row or column.  To deselect the selection, just press one of the arrow keys.  This will place you in the next cell to your last selection.

3.       If you are in a block of data, CTRL* to select all the data.

4.       The whole sheet: CTRL+A.

Once the your data has been selected, you can move it around with the mouse by grabbing one of the thick black borders, clicking and holding and moving it to where you want.  Then let go of the button.

Click in a cell with plain text.

COPY it (‘EDIT' menu, ‘COPY' / COPY ICON / CTRL+C)  (Strobing border appears round the cell.)

Click in a blank cell.

PASTE it (‘EDIT' menu, ‘PASTE' / PASTE (CLIPBOARD) ICON / CTRL+V).

Copied cell should appear. – Strobing border still around original cell.

Click in another blank cell and PASTE – You can COPY again & again while that border is there.

To DESELECT the original cell, press ‘ESC'. – Strobing stops.

Highlight 2 cells with just plain text.  (Click & Drag or Shift and ARROW).

CUT it (‘EDIT', ‘CUT' / CUT (scissors) ICON / CTRL+X)  (Strobing border appears round the cell.)

Click in a blank cell.

PASTE it (‘EDIT', ‘PASTE' / PASTE (CLIPBOARD) ICON / CTRL+V).

The cut cells should jump to the new location.  Similar to actually moving it.

Repeat the above (COPYING) with a formatted cell.  Excel can copy formats across too.

XP Note:          When you select a second cell to be copied, the task pane to the right will appear.  This then enables you to see everything you have cut/copied (up to 24 items).  You are able to select the item you wish to paste from the clipboard task pane and it will then be pasted.

When you paste an item, a small button with the clipboard icon will appear.  By clicking on it, you will see a list of options you can choose from.

To delete and remove items from the clipboard, hover the mouse over the item, a drop down option appears on the right of the item, from which you can choose DELETE.

To copy just the format of a cell to another with plain text –

Highlight cell to be copied.  (BROS for example)

Click on the ‘FORMAT PAINTER' Icon, next to PASTE (it looks like a blue handled paintbrush with a yellow stripe.  The strobing border appears.

Highlight a cell with plain text.

The formatting is carried over.  (One cell at a time.)

To copy the formatting over indefinitely -

Highlight the cell to be copied.

DOUBLE click the ‘FORMAT PAINTER'.

Click on as many unformatted plain cells as you want.

To switch it off, click the button again or hit ‘ESC'

Useful if you spent a long time setting up a format and had to add some extra data that needs formatting up too.

Highlight B1 to E1.

Copy it.  (Strobing border appears).

Click in a new cell to the right of the table.

Go to ‘EDIT', ‘PASTE SPECIAL'.

With this box, we can specify what Excel

copies over.

Don't worry about top section or operation,

For now, just tick ‘TRANSPOSE' and press

‘OK'.

The text going across is now going down.

UNDO

Go to ‘EDIT'.

Top option – ‘UNDO…' (Or UNDO (Blue Left Curving Arrow with little black drop down) ICON) / CTRL+Z.

Using this undoes your last modification(s).  It can only remember UNDO's up to your last save.

REDO:              This option is only available when UNDO has been used.

Go to ‘EDIT'.

2^nd option down – ‘REDO…' (Or REDO (Blue Arrow Curving Right – with drop down) ICON) / CTRL+Y.

This will REDO your UNDO's, but will also only remember however many UNDO's you've done.

All UNDO's / REDO's are lost when you save.

Click in the cell you want to type in.  Enter data you want and when finished in that cell, press ENTER.

To Amend Data In A Cell.

1.       Highlight the cell and simply overtype new data.

2.       Single click a cell then click in the formula bar at the top of the page.  Type amendments and use [red cross] ‘cancel' or [green tick] ‘Enter' or just press Enter

3.       Double click in the cell. The flashing cursor appears.  Navigate with the arrow keys and amend data, using ENTER when satisfied.

4.       Highlight a cell & Press F2.  This will add the insertion point at the end of the current data.

Note:

TEXT ALIGNS LEFT

NUMBERS ALIGN RIGHT – If numbers align left, there may be problems with formulas.

Click on the column letter in the grey area at the top.  This selects the column.  Press CTRL and + (on the number pad) and this will insert a new column.  To get rid of it, or any other columns, press CTRL and – (on the number pad).  The same works for rows and even cells (extra box pops up when you need to insert cells.)

Column width adjustment.  This can be done to 1 column (or row) or multiple selected columns (or rows).

Hover between any of the column header letters until a direction bar with 2 arrows appears.  Manually move this to re-size your column.  You can select multiple rows by clicking & dragging across to highlight them – if more than one is selected, it will apply the same changes to all columns.

The quicker way is to DBLE click with the direction bar and the cells will resize automatically.  As you type in data, sometimes ‘####' can appear.  If it does, you can DBLE click to the right of the column header, on the dividing line when the direction bar appears and the column will resize.  Same applies if your text is hidden behind other cells.

Open a new workbook.  (‘FILE','NEW' (‘New' window – ‘OK') / NEW (blank sheet) ICON / CTRL+N)

(CAR SALES)

How can we find the totals?

Go to B1.  Start with ‘=' (tells Excel that we want it to do some work).

Type in the numbers, (i.e. – ‘=24+36+12+4').  Press ‘ENTER'. – Total appears.

Amend B5 (‘4') to ‘44'. – The total isn't correct now.  We could amend it on the formula bar, but we can set Excel up to do it automatically for us.

Substitute the numbers in the formula for cell references. (=B2+B3+B4+B5). This updates the total for us, whenever we amend the table.  It's a bit quicker.

Amend B5 (‘44') back to ‘4'.  The total updates automatically for us.  We can set up Excel so it is more efficient for us.

In A7, type ‘Average'.  (Total divided by number of criteria).

In B7, type ‘=B6/4'.  (Either typing in or clicking on the cell you want to do stuff to).

Amend B5 (‘4') back to ‘44' again.  This updates the Total and Average automatically.

This may take longer to set up, but once in place, we can manipulate the data very quickly.

We can ask Excel to choose the sums we want it to do for us.  These are ‘Functions'. (1 word commands to tell Excel what to do).

Delete the formula in B6.  Type in there ‘=SUM('  (We are telling Excel we want it a) To do some work, b) the work we want it to do and c) The area we want it to work from.

Once ‘=SUM(‘ is entered, drag over the cells we need.  =SUM(B2:B5) should appear.  Press ENTER.

To find the average, we can do the same, enter ‘=AVERAGE(B2:B5)' + ENTER.

Amend B5 (‘44') to ‘4'.  The functions will update as before.

Click in Cell C2 under ‘PROJECTED'.  (We can expect to sell 12 times each monthly amount).

Put ‘=B2*12'.

To copy this formula down, we can use copy and paste (as before) and click through all the cells, adding the formula.  This is not the quickest route.

Check the formula worked (i.e. copied successfully through the cells – check this in the formula bar.)

Delete the last 3 formulas.

Copy the original formula again.

Highlight the original formula and hover your cursor over the little black box in the bottom right of the cell. It changes to a black cross (FILL HANDLE).  Holding onto this, drag down over the 3 cells beneath.  This copies the formula down.

Delete the cells again.  Go back to the FILL HANDLE and DOUBLE CLICK it.  Excel then copies the formula down the table it sees and stops when there is a gap.

In A8, type ‘Maximum'.  In A9, type ‘Minimum'.  In A10, type ‘Count'.

To find the maximum, we can't just type ‘MAXIMUM'.  Click in B8 and press the little 'Fx' button.  We need to find a list of abbreviations for certain functions.

This window appears: It gives us a list of all Excel's functions and their abbreviations.

Choose ‘All' then ‘M' and find ‘MAX'.  A brief description appears in the grey area at the bottom.

Cancel this, go back to our cell and type '=MAX' (then select the cells).

We can guess that Minimum is similar, so B9 should have ‘=MIN(‘ and the cells.

Count ( ‘=COUNT(…‘ )

Click on a new sheet.  In A1, type 2, below it 3 and below that, 5.  In A4, type

‘=A1+A2*A3'.  We expect it to come up with 25.  Instead, it comes up with 17.  Why?

An old mathematical ruling called BoDMAS:

B = Brackets

o = nothing

D = Division

M = Multiplication

A = Addition

S = Subtraction

Excel uses this and works its way through your formula applying each criteria to it.

So, at the moment, it's looking for Brackets, then Division, Multiplication, Addition and Subtraction.  It does the 3x5 bit, then adds 2 making 17.  We need to add some brackets into this formula.

Modify the formula. ‘=(A1+A2)*A3'.  This should create the expected result.

This is all worth noting if your formulas aren't working out after you've set up your sheets.

1^st time we save, we must give it a Name & tell it where to save.

You will see different locations on your PC or network areas to save, so choose where you want, change the name of your file and choose SAVE.

Call this file [Your Name 01]

We can tell if it saved, as it should come up with the name of your file in the blue application bar at the top of the screen.

Right click on a tab at the bottom of the sheets.  The window (right) appears:

Insert – Puts in new sheets, to the left of where you clicked.  Sub-window appears – just need to OK it.

Delete – Deletes the selected sheet.

Rename – Highlights the sheet name – you can overtype a new name.  Double clicking on a

Sheet's tab will highlight the name as well.

Move or Copy – (MOVE – sub-window appears showing sheet order and the moved sheet

is put before the selected one.)

(COPY – open the sub-window.  Tick the ‘Create a Copy' box.  The chosen sheet (and all its contents) will be copied.  The copy has (2) next to it.

Select All Sheets – Allows you to choose all the sheets at once.  This is useful if entering data across more than one sheet. (COMING LATER…)

View Code – Don't worry about this it's not relevant.

Go back and in this current workbook, call Sheet 1 "Working Out Sheet" call Sheet 2 "BoDMAS".

Create this sheet.

In C2, change the percentage to 10%.  We could amend all formulas in this list by copying & pasting, but what if we have a huge spreadsheet?

Click in A14 & Type ‘Discount'.  In B14 type ‘10%'

In A15, type ‘VAT'.  In B15, put '17.5%'

Back in C2, delete this column of formulae and put ‘=B2*B14'.  Everything in the table works, BUT… copy the formula down and it all goes Pete Tong.  Why?

Change view (CTRL+nameless Key under ESC) – This view shows all the formulas in all the cells.  We can see that as we have copied down the formula, it is referencing the cells UNDER B14.  (B15, B16 etc…)  We need to Freeze the reference in the formula to just the cell B14.

Click in C2.  Go to the formula bar and click the insertion point into the 'B14' reference.  Press F4.  This is the freeze key.  Note that $ appears around the B.  ($B$14).  This tells us the referencing to that cell is locked.  Copy this formula down, then click in the cell B14 and change the number.

To get rid of the absolute reference, just delete the $ signs (Or tap F4 twice) and re-copy the formula down.

Open a new workbook.

Advantages:  Saves time if there are a lot of similar sheets to be created.

Disadvantages:  Can overtype multiple sheets if you are not careful.

To select multiple sheets:

Click on the sheet 1 tab.  Press and hold SHIFT and click on the tab of sheet 4.  All the tabs will turn white (become selected / Enter group mode (Excel will say (group) in the application bar.

To exit group mode, click on an unhighlighted sheet, or, if they are all selected, right click on any tab and choose ‘Ungroup Sheets'.  If you want to select/deselect a particular sheet at a time, use CTRL and click on the tabs you do or don't need.

Group the first 3 sheets in your new workbook.

In A1, put ‘Sales Team'

A2, type ‘[Your Name] 01'.  Copy this down till there are 10 of ‘you'.

In B1, type ‘Jan' and copy this across to make a year.

We now need some random figures just to fill our spreadsheets.

In B2, type ‘=INT(RAND()*50)' (INT=Integer = Whole number – This equation creates random numbers that will completely change every time you do something in the sheet, or come back to the sheet.)

Copy this down to fill all the names.  Then copy this across to fill the whole year.  We now need to get rid of the formulae, so we have just whole numbers left.  Select all, COPY it, go to ‘EDIT', then ‘PASTE SPECIAL' fill the ‘Value' Radio button (This copies the value only – not the formula too.)

Under the 10^th person, put ‘Total'.  In B12, press ‘ALT='.  This guesses a sum.  It's correct for us, so copy it across the 12 months.

In N1, put ‘TOTAL' and N2 do ‘ALT=' again & copy down.

Exit group mode (click a grey sheet or right click a tab and select ‘UNGROUP SHEETS').

Check the 3 sheets, should be the same, except the names and numbers should all be random between 1-50.

Call the sheets ‘Dealer 01/02/03'.  Rename sheet 4 as ‘Area Summary'.

In A1, put ‘Area', B2, ‘Total', A3, Dealer 01 (copy down the 3 dealers), A7, ‘Area Total'.

Click in B3 and put '=' then go to ‘Area 01' sheet and click the Total number (N12).  The strobing border appears, then press ‘ENTER'  The total appears, and the formula bar tells you where the figure came from.  Do the same for the other 2 ‘Dealers'.

In B7, type in ‘ALT=' and the autosum will assume the sum we need.  ENTER.

On sheet 01, change M11 to 1,000 and check all the totals have updated.

This is called a 3D spreadsheet.

This is the toolbar, select different bits of text to apply formatting to.

Then go to the ‘FORMAT' menu and work through the tabs that are there.  (Right).

Go to Area 01.  CTRL* it to select all.  Go to ‘FORMAT' and ‘AUTOFORMAT'.

Go through the list of options and see what is available.  They aren't great, bur they're okay just to give a plain spreadsheet a little bit of life.

Select the ‘OPTIONS' button.  The sub-drop menu drops down with tick boxes that allow you to customise your sheet to your heart's content.  Again, not dazzling options, but suffice.

Choose OK to action your choices.

Select the data in the sheet and clear all formatting (CTRL* then EDIT, Clear, Formats)

To lock the sheet.

Go to ‘TOOLS', ‘Protection' and ‘Protect Sheet'.  A box appears asking you for a password (don't worry about the other tick boxes).  Use your 1^st name and Excel asks you to confirm it.  Type it again and OK.  Then try and edit any data in the sheet.  The file is called ‘Read-Only' and you can only amend it by unlocking it, which (if you have the password) you can do.  This is okay to lock up formulas you've set up, but no-one can edit the data.  Not very useful(?)

Unlock the sheet, (‘TOOLS', ‘Protection', ‘Unprotect Sheet').  It asks you for a password, enter this and OK it.

To only protect certain parts of the sheet.

Highlight the cells you don't want locking.  Go to ‘FORMAT', ‘CELLS' and choose the end tab – PROTECTION.

Take the tick out of the Locked box and OK that.  This now means that the selected cells will not be locked when the rest of the sheet is.

Go back through the Toolsà Protection Menu again and lock up the sheet.  Then when you click back in it, the important bits (i.e. formulas) are locked and the data cells can be amended as necessary.

Go to Print Preview (Through the ‘FILE' menu or the blank sheet with a magnifying glass icon.)  We can see 4 potential problems.

1.       Not all the sheet is on the page.

2.       There are no gridlines.

3.       The sheet is not central on the page.

4.       The row headers don't follow through to page 2.

In the bottom left, there should be ‘Page 1 of 2'.  Close the preview (button) and you will see some dotted lines have appeared.  These are the boundaries of the print area we have just seen.

Select (CTRL*) the block with the data in it we want to print.  Go to ‘FILE' and ‘Page Setup'.

1^st, click on the ‘Sheet' tab and put a tick in the ‘Gridlines' box.  Ok this.

Then, select (highlight) the area of the sheet that you want to print, go to ‘File', ‘Print Area' and ‘Set Print Area'.

If we Print Preview now, it will say only ‘Page 1 of 1'.

Make the list of items stretch down to about 25.  Print Preview this – we can't see the extra stuff.  Back to the sheet, reset the Print Area and re-Preview.

Back in the sheet, copy down so there are about 60 items in the list.  Reset the print area and preview this.  If we look at the second page, there are no column headers like there are on page 1.

Go back into Setup, and in the Sheet Tab, Click in the little griddy bit of ‘Rows to repeat at top:' Then click in the top row of your sheet.  The strobing borders should appear across the whole row & $1:$1 will appear.  OK this, then Print Preview once again.  Check that what we hoped would happen actually has.

FILE: ‘Page Setup' and ‘Page' tab - ‘Fit to' radio button (1 page wide by 1 page tall).

To resize the sheet, put a dot in ‘adjust to' and rescale,  whichever way you need.

To centralise the sheet on the page:

FILE: ‘Page Setup' and ‘Margins' tab at the top.  Do NOT meddle with the margin settings – if you set them up wrong, some parts of your chart will not print.

Just tick the ‘Centre on page' boxes.  The preview will put the chart in the middle of the page.  Then OK.

To set a header or footer.

FILE: ‘Page Setup' and ‘Header/Footer' tab.

Click the Custom Header Button and the box (right) appears.

Type a name for your file in the centre section.  Format it using the ‘A' box and click OK when you are truly happy.

(The boxes in order are)

1.       Format text

2.       Current page (system generated)

3.       No. of pages in document(system generated)

4.       System Date

5.       System Time

6.       Workbook Name

7.       Sheet Name

Hit the Custom Footer button at the bottom, and the same window appears.  In the left section, Type in ‘Page_#_of_++' not forgetting the spaces.

Put the Date & Time in the centre section (with spaces) and the Filename and sheet tab name in the right hand space.  Print preview this little lot and this is how it should appear.

Open "Things For Sale".  Choose the data by highlighting it (including headings.)  (CTRL*)  Click on Chart Wizard and follow through the instructions in each stage.

Step 1 of 4 – Chart Type

You can choose the type of chart you want to create.  The list on the right gives types of chart available & on the left, you can choose a more tailored version.  Choose one of the chart types, then press and hold the said button to preview it.

Have a fiddle, but arrive at the top left one. (Clustered Column)

The custom types tab contains some pre-set basic charts that you can add to – we'll do that later.  To use the machine defaults, choose Built in.

When you are happy, choose next.  As with all wizards, we can choose to go back at any point and change our chart.

NEXT

Step 2 of 4:

The next step is Chart Source Data.  As we have already selected some data, excel checks this is correct by putting a strobing border around it.  At this stage, if you are not happy with the selection, you can re-highlight cells to amend it.  Do not worry about the Series tab.  See how the dot in the rows or column spaces change the appearance.  We want rows.

NEXT

Step 3 of 4:

Chart Options.  Box with 6 tabs:

Titles                Name your chart, and the data in it. (Months/quantity) etc.

Axes                Fairly irrelevant if you have already chosen names/titles in the spreadsheet – switch the tick on & off 2 see what happens.

Gridlines          Put ticks in the boxes of the gridlines you want – be aware – too many lines will make it awkward to read.

Legend                        You can choose to have a legend or not, & where to put it.

Data Labels     Puts the figures over the bars – again, can make it cluttered.

Data Table       you can choose to put the data table in there too, but that tends to look awful.

NEXT

Step 4 of 4 – Chart Location

You can either put it in your current sheet or put it into a new page in your workbook.

Put – as new sheet.  Then FINISH & your chart SHOULD appear.

Now you can change the appearance of your chart.  A toolbar may have appeared with it, if not go to VIEW, TOOLBARS then put a tick next to CHART and a floating toolbar should appear.  You can put this at the edge if you find it distracting.

This floating toolbar gives you one way to enhance your chart.  The other 2 are mentioned in a minute.

The dropdown on the right tells you what elements of the chart you can choose.  Click on a few of them and the element of that chart should see black boxes round it telling us that it's selected.

Choose CHART AREA.  (From the floating toolbar) then the little fingery thing on the page next to it allows you to actually change the formatting.  You can work through like this, useful if there are lots of fiddly bits on the screen to be formatted, or you can DBLE click each part of the chart and a list of options will appear, or right click and a list will appear.

A right click on the different parts of the chart will bring up a menu with various options.

DBLE (LEFT) click on a chart element and a formatting option box will appear straight away.

Click once on a series, count to 2 in your head and click it again and you will select just one column in that set.  You can also adjust this manually, which will also change the figures in the data table.

To save chart as a TEMPLATE or default chart (one that is chosen automatically when you work through the wizard), go to CHART, CHART TYPE and choose user defined, press ADD, name the chart and give it a brief description, and when you OK it, the chart is added.  Then, when you go through the chart wizard, your chart is already there.

Go to Word, insert a clip art, Copy It, go to Excel, Click on one of your series of data and paste it, and your chart can have clip art series too(!)

Another way to brighten up the charts: DBLE click an element and select PATTERNS.  Under the colour chart on the right – choose fill effects.

4 tabs appear.

Gradient – can choose a blend of 1,2 colours or a pre-set colour scheme.

Texture – brings up some various textures to use

Pattern – allows you to fill a series with a hideous pattern

Picture – allows you to put a picture in there, if you have one available.

►Reprint Rights◄

Log in to become a member of John Caulfield's Fan Club!

Comments on this article: (1 total)

» left by Anonymous (298 days 22 hours ago.)

I like the notes but where is the same for 2007?

Respond to this comment

Was this article helpful to you? Leave a Public Comment or Question: