## Calculations in Excel

Learn the 4 steps that make entering a calculation into an Excel spreadsheet work reliably every time.

Never entered a calculation into an Excel spreadsheet? When you’ve finished this short tutorial, you’ll be able to create formulas in Excel to make your business life just a little bit easier…

The video refers to buttons below – these only appear when viewing from the www.tekbites.com website.

We’re just getting started (this is our first video) so the resources available on the tekbites.com website will be quite limited to begin with – apologies in advance.

## The first Excel mistake everyone makes

Most people who want to produce an Excel spreadsheet make their first mistake when they fire up Excel.

So why is that a mistake and what should we be doing instead?

What we all do when creating a spreadsheet is to start up Excel, enter some row headings, enter some column headings, put in some data – real or sample – and then analyse the results with some calculations. This inevitably means the results or ‘answers’ will be far over to the right hand side of the sheet or way down at the bottom.

How many of your spreadsheets have the answers way down the sheet at some arbitrary row. And that always needs moving as soon as you add or remove data.

The best place for the answers is always at the top left hand corner of the sheet because that’s always the easiest place to get to. And you don’t need to keep on moving it.

I think creating a spreadsheet is a four step process.

Step 2 is switching on Excel and actually building the spreadsheet.

Step 1 is grabbing a pen and paper and sketching out a design for your spreadsheet. Take the time to decide what your output needs to look like and you’re far more likely to have a working and workable spreadsheet.

What questions are you answering? How are people going to use the information you’re collecting and analysing. Which bits of the data need to be the most visible? Always think about the output first.

And don’t feel you have to think (or act) conventionally because many of the reasons we do things the ‘usual’ way have more to do with pen and ink than modern day electronics.

Totals, and other statistics, often end up at the bottom of columns of numbers because that’s where they always appear, but the original reason for putting the total at the bottom has far more to do with adding up the way you did when you first went to school than any sort of conscious decision.

If you put the totals (and other summary statistics) at the top of the sheet, above the data, you can lock the first few rows on the screen using Freeze Panes so any changes to the data are immediately reflected in the summary statistics. You also get the added benefit that the answers are easy to find and you don’t have to keep on moving them every time you add some data.

If you do spend some time designing your spreadsheet before you start building it, you will definitely save yourself time, effort and aggravation in the long run.

And the two missing steps – that’s for another post!

## 3 Powerpoint mistakes to avoid

OK, without spending too much time playing bad slide, good slide with you, here are the three things people get wrong most often when creating PowerPoint presentations.

#### Too much information on each slide

But what’s so wrong about that?

Inevitably, the more text you put on a slide, the smaller the size of the text. Even in a small presentation venue some members of your audience may not be able to read your slides – they may have forgotten their glasses, be sitting too far away from the screen or they may not even realise they need corrected eyesight.

Less and therefore larger text has far more impact and is so much easier to read.

And what does your audience do as soon as you show them a slide? Every one of them will read the whole slide. The more text on your slide the longer they take to read it and the more time you have to waste waiting for them to finish. There is absolutely no point in talking to them until they have finished reading because their attention is on the slide and not on you! And surely it’s pointless telling them what they’ve just read…

To avoid these problems, don’t write down everything you are going to say. This has two advantages. Firstly, you naturally have less to include on each slide. The second and more important advantage is that you can adjust the content of your presentation to suit the audience as you go.

#### Too much variation in colours and fonts

If the main point on your first slide is in red, on the second it’s in blue and on the third it’s in green, by the fourth slide your audience will be taking bets with each other about the colour you are going to use next – and that means they are not paying attention to you and the important point you are trying to make.

Anyone who drives knows what is happening when a large red vehicle with blue flashing lights appears in their rear view mirror even if they cannot read the word FIRE written on the front. Sport is consistently located at the back of newspapers to make it easy to find. A handle on a swing door means PULL while a metal plate means PUSH.

None of these and many other examples are accidental. They are all designed to make it easier to communicate important (or not so important) information quickly and easily.

Consistency improves the communication between you and your audience. As long as the style, layout and colour scheme of your slides is consistent, after the first few slides you show your audience will have learnt the colour, textual and visual clues that you are using.

#### Too much distraction

Many presentation courses will suggest that you make your presentation graphics ‘interesting’, explaining that lots of movement makes the slides less boring. Video clips, strange cartoon creatures running around and animated, looping and spinning text are all recommended.

All these techniques do is to cause distraction. Your audience have come to see you, not your PowerPoint presentation.

The fact that a PowerPoint presentation can be distracting comes as a great relief to many presenters as many people would prefer to hide in a corner and let the audience watch their slides rather than have any attention focussed on them.

But never forget, however bad you think you are as a presenter, you are what is interesting. You are the person your audience want to see and hear. They came to listen to you, not to marvel at how good you are at making PowerPoint jump through hoops…

## Percentage difference between two numbers in Excel

#### We served 2,800 customers last month and 3,200 customers this month. What percentage increase is that?

This calculation requires three steps.

First calculate the difference between the two numbers. Then, calculate this value as a ‘fraction’ of the earlier number. Finally ask Excel to display this result as a percentage.

The difference between the two numbers is always the later or more recent number minus the earlier or older number.

In the example below, customer numbers have increased from 2,800 last month to 3,200 this month. First, calculate the difference.

Next, take the difference between the two numbers and divide by the original number.

Finally, change the format of the answer so that Excel displays it as a percentage. This can be done using the percentage icon on the toolbar.

It is usually worthwhile increasing the number of decimal places displayed so that a more accurate value is shown.

Customer numbers changing from 2,800 to 3,200 represents an increase of 14.29%.

#### We served 3,500 customers last month and 3,400 customers this month. What percentage decrease is that?

Where the number decreases, the steps are the same.

In the example below, customer numbers have decreased from 3,500 last month to 3,400 this month. First, calculate the difference.

Here, the difference is a negative number, representing a decrease in numbers.

Next, take the difference between the two numbers and divide by the original number.

Finally, change the format of the answer so that Excel displays it as a percentage. This can be done using the percentage icon on the toolbar.

Again, it is usually worthwhile increasing the number of decimal places displayed so that a more accurate value is shown.

Customer numbers changing from 3,500 to 3,400 represents a decrease of 2.86%.

Try it yourself using the examples below…

## What percentage does a value represent of the total amount?

#### 7 answers out of 12 were correct. What is the percentage score?

This type of calculation is best approached as a fraction.

The partial value, in this case 7, is divided by the total value. Formatting is then used to display the resulting number as a percentage.

In the example below, 7 answers were correct out of a possible 12.

Finally, change the format of the answer so that Excel displays it as a percentage. This can be done using the percentage icon on the toolbar.

It is usually worthwhile increasing the number of decimal places displayed so that a more accurate value is shown.

A score of 7 correct out of 12 questions represents a total score of 58.33%.

Try it yourself using the examples below…

## Decrease a number by a percentage

#### Calculate a discount of 9% on a purchase of 120

This is best approached in two stages.

First, calculate the percentage required using the technique described here – Finding a percentage of a number. Then subtract this amount from the original.

In the example below a 9% discount is applied to a purchase of 120.

If you get a 9% discount on a purchase of 120, you only pay 109.20.

Try it yourself using the examples below…

## Increase a number by a percentage

#### Increase 24,000 by 4%

This is best approached in two stages.

First, calculate the percentage required using the technique described here – Finding a percentage of a number. Then add this amount to the original.

In the example below, an annual salary of 24,000 has been increased by 4%.

A 4% increase on a salary of 24,000 is 24,960.

Try it yourself using the examples below…