Spotlight on Excel
Picture the scenario. Your manager has just asked you to create an Excel spreadsheet to calculate the number of orders generated by the sales team in the past quarter, both individually and as a group. What's more, she'd like the figures shown graphically as a bar chart to present to the Board tomorrow morning. Are you filled with a sense of horror? Or do you secretly delight in being given the opportunity to show off your Excel skills?
If you've hardly ever opened Excel or by contrast you've used it for a long time but have only inputted figures into existing spreadsheets, this would be a particularly daunting prospect. When I ask delegates at the beginning of my Introduction course for their individual impressions of Excel invariably I get one or more of the following responses:
“I’ve opened it but quickly closed it again. It looks huge!”
“It’s all maths and I don’t like maths”
"I’m always worried that I’ll do something wrong or delete important information and then not be able to put it right again”
"I don’t know where I can use it in my job”
On first impressions Excel can appear to be a rather intimidating package - it does use maths and the files can contain many sheets with a seemingly never-ending number of rows and columns. Nevertheless, when working with figures and performing calculations, using Excel can save you a considerable amount of time. Put simply, if you can do it with a calculator, pen and paper, you can do it in Excel - but much more quickly and accurately. If you get the wrong figures, for example, or if they change over time, there's no need to perform the calculation again - Excel will recalculate it for you.
As for where and when to use it, Excel is so versatile that the possibilities are endless. Although it's great for performing complicated financial calculations, such as budgets and profit & loss reports, I used it last week to print off a list of people who had paid their deposits for the Christmas Do I'm organising. I also created a seating plan for my friend’s wedding and I'm in the process of copying and pasting Spanish vocabulary from different websites into a single sheet and using the sorting and filtering functions to ensure that I am not repeating myself. In short, the more you know about Excel and the way it works, the more uses you will find for it.
If you're just starting out and the opening scenario had you breaking out in a cold sweat, the following tips will help you set up a spreadsheet from scratch, understand how existing spreadsheets 'work' and most importantly, give you the confidence to know that you won't 'break' existing Excel files.
Tips for Creating Effective Spreadsheets:
-
Always try and layout your data in a tabular format, with headings going across a row and your data entered in columns
- Give every column a heading contained in a single cell
-
NEVER use more than one row for your titles ‑ Excel will not know which row to use as your headings
-
If your headings are large either expand the column width or wrap your text within one cell by typing the first line and pressing Alt + Enter to go to the next line. Repeat this as many times as you need and press Enter once you have completed your heading
-
Remember that the grid lines do not print so add borders to your table ‑ simple thin black lines inside and a thick border on the outside are always effective
-
NEVER leave completely empty columns within your table as Excel will see this as separate collections of data
-
If you want an overall heading always leave at least one row between it and your table ‑ you can decrease the height of the row or hide it if you wish but it must be there in the first place
-
If you will be sorting or filtering your data make sure that you leave a row between your table and any calculations at the bottom as Excel will sort up to the first empty row it finds and you could end up with your total calculations being sorted within your data
-
Colour the background of cells that contain formulas to make these stand out and if you are creating a spreadsheet for others to fill in you may want to fill the cells in which you want them to enter data in a different colour
-
To total rows and columns use the AutoSum
-
Highlight all the cells with your figures up to and including the cells where you wish the totals to appear and click the AutoSum Icon
-
In the Example above you would highlight B4 upto E10 – this will put the totals in the darker blue cells
Confident Excel User
As a confident Excel User you would have approached the task in the opening scenario as follows:
Step 1: Collated sales figures
Step 2: Entered them into a Spreadsheet
Step 3: Used Autosum to total the figures for each person, each month and overall
Step 4: Used Chart Wizard to create a formatted Bar Chart
Having easily ploughed ahead with the task, you could then go above and beyond what has been asked of you, by incorporating IF Statements and Conditional Formatting as outlined in the Taking Spreadsheets Further section below.
Taking Spreadsheets Further:
- Using IF Statements to indicate which team member has the highest and lowest figures in separate columns
- To indicate the highest you will need to use a MAX function nested within an IF –
- To indicate the lowest you will need to use a MIN function nested with in an IF –
- You can indicate both of these in one column using a multiple IF statement –
-
Using Conditional Formatting you could make the sales figures that are above 500 appear in dark blue
- Use Format + Conditional Formatting
- Change Between to Greater than or Equal
- Type 500 in the right hand side text box
- Click Format and choose which ever font and background colour you like
So, whether you’re a Beginner or an Old Hand with Excel there are always further skills which can be added to your repertoire. Even now, having taught Excel for over ten years, I still manage to come across new hints and tips and ways of using the package which enhance my existing knowledge. Although I do have a considerable soft spot for Access (but more of that in a future article), I have to say that Excel is by far my favourite Microsoft package, both to use and to teach. It's appeal lies in the fact that it's a very logical, straightforward package, with its own rules and conventions. From both the Users and the Tutors point of view, as long as these conventions are taught well, and adhered to religiously, however you chose to use them, the end result will be effective, professional spreadsheets.
