MS Excel for School and College Students

Microsoft Excel for school (ICSE, CBSE, IGCSE) students and College (Commerce, Economics) students. Find Excel tips and tutorials.

Introduction to Excel

Microsoft Excel is probably one of the top 5 most used software packages in use today. It is a spreadsheet program, and an incredibly powerful and versatile one (its essentially, a great big real-time calculator). It has many uses, from simple calculation tasks and data management to massive number-crunching exercises and highly complex mathematical modelling.

It is, quite simply, about the most useful software package you can learn if you are intending to work in a business related area. So over the coming weeks you are going to learn how Excel can be used to simplify complex mathematical tasks, produce graphs and charts, and more.

Use it for:

• Simple calculation tasks
• Data management
• Massive number-crunching exercises
• Highly complex mathematical modelling

Excel is not the only spreadsheet around, although it is by far the most commonly used, and is available throughout the University. It is also quite expensive.

There are other spreadsheets available. Some are very basic, and may not be able to do everything you need. But many are more than capable of handling everything you are likely to want to do, and are a lot cheaper, and in some cases, free!

Using Excel for Statistics

Excel is excellent for handling lots of numbers and doing complex number-crunching. But statistics can get complex at times, and there are some types of statistical analysis that Excel does not do well.

There are times when getting a really basic graph out of Excel is very hard work – and there are some types of graph Excel can’t produce at all.

How to Use Excel to do Calculations

Excel is like a great big calculator and can be used for all kinds of calculations.

Starting with the “=” is vital in Excel. It tells Excel that you want it to do some kind of calculation.

If you add 2 and 3 together, you will get 5. Begin with a clean worksheet. In cell D4 type =2+3, then press the return key to get the total.

When you type in the formula you should notice two things.

First, as soon as you type the “=” sign, the Name Box changes from a cell reference to a word. It will probably be “sum”, but when you’ve done some more work on the spreadsheet it may be something else.

Second, the cell should contain the answer. But if you click back onto the cell, the formula bar tells you where the cell contents comes from – the expression “2+3”.

That’s pretty simple, but a little bit limited. The only way we know where the number 5 came from is by clicking on the cell.

What if we wanted to change a number?

It would be better if we could see what numbers were being used in a calculation all the time. So let’s try doing that.

In cell D2 type “2, and in cell D3 type “3”.

Now in D4 type “=D2+D3”.

Note that Excel is not case sensitive here. Nor is it bothered by spaces (as long as you keep the two parts of each cell reference together). You can type “= d2 + d3” and get the same answer – Excel will automatically capitalise the letters for you.

If you want to see why this is useful, try changing the 2 into a 4. Excel instantly updates the answer for you.

It can get a little bit cumbersome typing in cell references though, so here’s a handy alternative. Instead of typing the cell reference, click on the cell itself. So in cell E4 type “=”, then click on cell D2. Now type “+” and click on D3. Press return.

You need to be careful doing this. If you’re not careful you can end up clicking on a sequence of cells that causes Excel to get confused. When this happens, simply press the Escape key at the top left of your keyboard.

Formatting in Excel: Tips & Tricks

Excel can be used to store, manipulate and analyse data. It can perform calculations in a variety of ways that can make it easier to extract information from the raw data.

A well-designed spreadsheet should be easy to read, with the data and any calculated output arranged logically and clearly. It should be clear how the spreadsheet works, and be easy to adapt or adjust if there are new requirements.

Borders and shading are functions in Excel that are concerned with formatting, and helps keep track of what the spreadsheet is doing. They affect the way that a spreadsheet looks, which can have a huge affect on its usability, but without affecting its functionality. When you use formatting, the data remains the same, and the results of any functions or formulas are also unchanged, but you can dramatically alter the way the spreadsheet can be viewed or understood.

Poor formatting can make a spreadsheet more difficult to use, which in general is not what we want. So it important to know how good formatting can make a spreadsheet easier to use and understand.

Some simple formatting

The Format Cells menu is easy to access. Simply right-click on any cell of your spreadsheet, and select the Format Cells option. You will see the Format Cells menu.

The default starting point for this menu is the Number tab. You can see that there are quite a few options here.

There is an easier way of accessing the Number functions. On the Home tab of the ribbon there is a Number section. This contains buttons for the most commonly used options – Accounting, Percentages, Separators and two for increasing or decreasing the number of decimal places used. The dropdown menu above these buttons gives access to the full range of options, although you still need to use the full menu to get all the available functionality – you can get this by clicking on the little arrow at the bottom of the section.

Percentages in Excel

A percentage is just a particular way of looking at a decimal fraction. Suppose you want to use an interest rate in a calculation, say, 5%. You know that in your calculations you don’t use the number 5 to represent the interest rate, because the percentage is just a convenience. The number you should actually use is 0.05.

To turn a decimal into a percentage you normally multiply by 100, but it is the decimal that should be used in calculations. So rather than type 5% in a cell, you should type 0.05, then format the cell to look like a percentage. That way, you can see a percentage, but Excel will (correctly) use the decimal.

Type a decimal into an empty cell and format it to look like a percentage. Check that the cell behaves correctly when you use it in calculations. Also check that you can change the number of decimal places used in the percentage.

Currency in Excel

One of the most common uses of spreadsheets is for doing calculations involving money. Although there are lots of specialist accounting packages, many people use spreadsheets for this. So it’s unfortunate that Excel doesn’t always handle money values as you might expect.

Activity:

• Type $32 in cell C4 and below type =2*C4
• There was a sign that something was wrong
• Type 32 in cell C4
• Go to Accounting button (the one with little coins on it)
• Clicking on the button, it will insert pound sign and 2 decimal places.
• If you click on the arrow, you have a choice of currencies

Dates in Excel

You can easily type dates in Excel. In fact, you may remember that Excel can recognise the names of the months, and can work with them when you use the Fill tool.

In fact, Excel can work with dates as if they were numbers, which can be very useful, but also very confusing.

In cells D4:D6 are the numbers 1 to 3.

Highlight these cells, and from the dropdown menu select Short date. The numbers will change to dates – 01/01/00 to 03/01/00.

Before you get too excited about these dates, click on D4 and check what the date is in the formula bar. The dates actually start from 1st January 1900. The dates are just a formatting tool that counts the calendar days from that date. Remarkably, it even gets the number of days in February right – leap years have been programmed in.

(Unfortunately, there is an error in the way the dates are calculated because the year 1900 is assumed to be a leap year with an extra day in February. In fact, it wasn’t, which means that if you try to calculate a date based on the number of days since the beginning of 1900, you will be out by one day. This is not actually Microsoft’s fault. All spreadsheet programmes do this. The error was programmed into the very first spreadsheet, and newer software has stuck with it to ensure compatibility between programmes.)

The sequence actually starts from 0 = 00/01/00, a non-existent date, and it doesn’t work at all with negative numbers, although there is an add-on programme that will allow dates to go back as far as AD100.

Even though the cells look like they contain dates, they still behave like numbers.

You can also change the formatting to Long date. But if you go to the Format Cells menu you can fine-tune the appearance of dates even more.

Time in Excel

Enter a series of times in column F – hours from midnight to midnight.

Highlight the cells with times in them and reformat them as General. Look at the numbers that represented 06:00, 12:00 and 18:00 – that will give you a clue.

Now use the spreadsheet to work out the following fractions – 1/24, 1/12, 3/24, 1/6. Excel works out times using fractions of the 24 hour day. So zero represents midnight, 0.25 (a quarter of the day) is 6 am, 0.5 is midday, etc.

The times are formatted as hours: minutes:seconds.

Dates and times together in Excel – Custom Formats

You will see that there is no option in Dates to include time, or in Time to include dates. There are however, some additional options under Custom, some of which allow you to combine the two. These are not always easy to use, and in fact, they are intended as starting points for you to create your own formatting options.

In your own time, try working out the number that represents both the time and date of your birth, and use a Custom format to check if you’re correct.

Functions and Features

Using the Sum() function in Excel

Here’s how to use the sum function in excel.

Suppose we wanted to add more than two numbers together. We might have a long sequence of numbers, maybe a whole column of sales figures, or expenses. Clearly it would be annoying to have to add them up on a spreadsheet in this way.

Fortunately, Excel comes complete with a whole load of pre-written functions to make our lives easier, and the simplest of these is the Sum( ) function. (The reason I’ve put the brackets after the word “Sum” is really computing terminology. People who are used to programming will instantly recognise this as a function that requires some input, in this case, the numbers to be added together. It’s not necessary to always do this when describing functions, but it does make it clearer that that’s what you’re doing.)

In an unused column, type a series of five or six numbers. For now, keep the numbers small, so that you can check that Excel gets the answer right. It will, of course, but this is just for your own confidence in the software.

Now we will look at three different ways of getting Excel to do the addition for us.

1. In the cell immediately below the last of your numbers type =sum(, then highlight the range of cells to be added together, and finally type ). When you press return, you should find the right answer in the cell with the formula in it.

Look at the formula in the answer cell. How does Excel describe the range of cells?

Now delete the cell contents and re-enter the formula, but this time, pay attention to what happens as you type. Think about how Excel tries to help you do things more efficiently.

2. Remember the word “sum” appears in the name box when you type in the equals sign? It will usually show the last function used on the spreadsheet, but because we hadn’t used anything before, Excel gave its default function, which just happens to be Sum( ). So since it’s there, let’s use it.

Clear the answer cell, and again type “=”. Now, instead of typing anything, click on the Name box. A dialogue box will appear prompting you to tell it what numbers you want added together. You should see that Excel has already guessed which numbers you want to add. Check that Excel has got it right, then click OK, and you should get the answer.

This dialogue box might seem a little over complicated for such a simple function, but there are many far more complicated functions where the dialogue boxes can be very helpful.

3. Some functions are so useful, or so frequently used, that Excel gives you quick and easy access to them. The sum function is one of these.

Clear the answer cell again, and just click in it without typing anything.

With the Home tab selected in the ribbon, there’s a group on the far right called Editing. One of the options here is AutoSum, and it has the commonly used symbol ∑ (the capital Greek letter sigma). Click on this, and Excel automatically adds together the numbers above the answer cell. It makes an assumption about what you want to do. This assumption isn’t always right, so you may have to adjust the range if you want to do something different.

Clear the answer cell again, and just click in it without typing anything.

With the Home tab selected in the ribbon, there’s a group on the far right called Editing. One of the options here is AutoSum, and it has the commonly used symbol ∑ (the capital Greek letter sigma). Click on this, and Excel automatically adds together the numbers above the answer cell. It makes an assumption about what you want to do. This assumption isn’t always right, so you may have to adjust the range if you want to do something different.

Some other functions in Excel.

Look at the AutoSum symbol again. There’s a little arrow next to it.

Click the arrow, and you will see some other commonly used functions. You can use these functions on the column of numbers you typed. Try them out, and make sure you understand what they all do.

Try these functions using different cells for your answers. How does Excel cope when the data you want to use isn’t immediately above the answer cell? Make sure you know how to get Excel to do what you want it to do.

Logic functions in Excel

There is another, very powerful formatting function in Excel, called Conditional formatting. It allows you to change the appearance of parts of the spreadsheet depending on the values or types of data in the spreadsheet.

It is quite simple to use, but to really understand it, you first need to become familiar with some important ideas to do with logic. This shouldn’t be entirely new to you, as the work you did using Venn diagrams in the Probability lectures is directly related to logic.

Excel has its own special logic functions, and we will look at them briefly now, so select the worksheet labeled Logic.

To see the logic functions that are available, go to the Formulas tab on the ribbon, and select the Logical button. Here you can see all the logic functions, though we are only going to consider three of them.

The OR() function

The OR( ) function basically asks a question. It does this by making two statements and checking to see whether one or the other is true.

To do this, we can use the OR( ) function. Go to cell B3 and type =or(A3>6, A3<4). Only one of the statements is true, but that’s all we need – Excel returns the answer – TRUE.

What if we ask the same question about the number 5? Or 7? Use the fill tool to find out.

You should find that for 5 we have the answer FALSE, because 5 is not greater than 6, and neither is it smaller than 4.

However, 7 returns TRUE, because although it isn’t smaller that 4, it is greater than 6.

So the OR( ) function tests to see if at least one statement is true. But what happens if both statements are true? Usually when we use the word “or” we mean one or the other, but not both.

The AND() function

The AND( ) function is very similar to the OR( ) function, but instead of testing to see if at least one statement is true, it tests to see if both are. To see how this works, we check the following statements – a number is greater than 4, and a number is greater than 6. To test this for the number 3 (in cell A3), type the following in cell C3: =and(A3>4, A3>6).

The answer is FALSE – neither statement is true.

If you test this for the numbers 5 and 7, 5 should be FALSE as well (only one statement is true) and 7 should be TRUE.

The IF() function

The IF( ) function works in a similar way to both the AND( ) and OR( ) functions, in that it tests statements to see if they are true. But what makes this so powerful is that it doesn’t just let you know if the test shows TRUE or FALSE, but can do other things, depending on the answer.

So going back to the OR( ) function, suppose that instead of just wanting to know if something is true, we wanted to type a response based on the outcome. Well, now we could.

The syntax for the IF( ) function is that we first say what it is we want to test, then what we want to happen if the test is TRUE, and finally what we want to happen if the test is FALSE.

For example, in cell D3 type =if(C3=TRUE, “Hurrah”, “Never mind”).

Use the fill tool so that you can see what happens with different results.

Logical functions are extremely powerful, because they can be nested. So, for example, having tested something using IF, if it isn’t true, the response can be another IF statement, and another, and another.

Conditional formatting in Excel

We can use almost any formatting option available to us, but only if certain conditions are met. So we could use it to highlight a cell containing a particular value, or range of values. Or we could use it to help identify the minimum or maximum value in a data set.

Highlighting a particular value with conditional formatting. Go to cell C3. From the Styles menu select Conditional Formatting. The menu does look rather confusing at first, (especially if you have used conditional formatting on an older version of Excel), but we will only be using a few of the options.

We want to create a formatting rule for a particular value in the seleted cell, so select Highlight Cell Rules, then Equal To…

In the dialogue box, type 23, and then go to the drop down menu on the right. You could choose one of the standard options, but instead, select Custom Format, Fill, and select the red box on the palette. (Note that you had the full range of formatting options available in the various menus.)

Click on OK for each box. Now try typing different numbers into C3. Nothing will happen until you type in the magic number…then the box will turn red.

Identifying an equality using conditional formatting

We aren’t limited to formatting specific values in a cell. We can also highlight a value if it is equal to that in some other cell. So, for example, we can make the text in cell C5 bold if it it is equal to the text in cell C6.

Click on C5 and select Conditional Formatting – Highlight Cell Rules – Equal To… Now select cell C6, either by typing or clicking on it. This time, from the Custom Format box select Font and click on Bold.

Now the text in cell C5 will be bold when it’s the same as C6, but only then.

Complex conditions

It is also possible to format a cell based on the outcome of some calculation, even one done in a different cell.

Example: =If(C9=2*(C10+D10),1,0)

Here is where using an IF( ) function can be useful.

For this we will have to go to another section of the Conditional Formatting menu, so we will need to select New Rule at the bottom. You can see that there are quite a few types of rules available. The one we want is at the bottom – Use a formula to determine which cells to format. The formula you need to make this work is =IF(C9=2*(C10+D10),1,0). The 1 and 0 at the end simply stand for TRUE and FALSE, so the formatting will occur if it is TRUE that C10 is equal to 2 × (C10 + D10).

Use the Border formatting options to put a blue border around the cell if the condition is met.

Multiple rules for conditional formatting

You are not limited to just one formatting rule per cell or group of cells. It is possible to test for two or more conditions, each with it’s own formatting result, although you do have to be careful that your formatting options don’t conflict with each other. The formats are applied in a strict order.

So we could use two different rules to identify both the maximum and minimum values in a set of data.

Unfortunately there doesn’t appear to be a way to use the MIN( ) and MAX( ) functions directly – if there is, I haven’t found it. But you can use values in other cells to help you. So with cells C14 and D14 identifying the minimum and maximum values in C12 to F12, you can apply two separate rules.

You should be able to do this for yourself. Don’t forget to highlight the whole range, C12:F12. Otherwise you will have to create the rules for each individual cell.

As you can see, there are a great many conditional formatting options available to you. Many are quite obscure or specialised, but many are extremely useful.

Make sure you go through all these options so that you are familiar with what Excel offers, so that you can make your spreadsheets work better.

Useful Tools in Excel

Using the fill tool for numbers

Try out the following steps in excel:

• In cell A1 type in the number 1.

  In the bottom right hand corner of the cell is a little black box, called the fill handle.

• Grab the fill handle with the mouse by clicking on it and holding the mouse button down (You’ll know you’ve grabbed it when the white cursor cross turns to black.)
• Keeping the button down, drag the mouse down for a few cells.

What happens?

The number 1 is repeated in all the cells.

Now, try out this variation:

• Type 1 in cell A1 again, but now also type 2 in the cell just below it.
• Highlight both cells by clicking on the top one and dragging the mouse down to include the cell below.
• Now grab the fill handle and drag down.

What happens this time?

The numbers increase in a sequence (1,2,3,4,5 and so on).

Now, try out another variation:

From the numbers, highlight a few cells in the column (say 1,2,3), try filling to the right.

The numbers 1,2,3 are copied in the adjacent cells.

(You can try filling up and to the left also).

Try it with a sequence. For example 2, 4, 6… or 10, 20, 30…

Now try it with a less obvious sequence: 1, 2, 4, 8…

As you can see, Excel gets it right every time.

Using the fill tool for labels

In cell A1, type in your name. Use the fill tool on the cell. Does it work?

Now try with labels with an obvious sequence: “January”. Try using abbreviation also?

Excel will fill the rest of the months on its own.

What other words could this work for (Week days)? Try them out. Try using abbreviations.

What happens if you use a sequence that includes gaps? Think about how this could be useful.

Activities

You may start with simple activities such as:

• Renaming Worksheets
• Saving an Excel File
• Identifying Cells
• Entering Data
• Using the fill tool for numbers
• Using the fill tool for labels
• Using Excel to do calculations
• Using the Sum() function

Activity: Write the output of the following functions:

• =TODAY()
• =NOW()
• =DATE(2024,2,15)

Activity: Type the following arithmetic functions in an Excel worksheet and write the output: Write the output of the following functions:

• =ABS(-234)
• =SQRT(144)
• =ROUND(12.267,2)
• =SUM(0.20,30,40,50)
• =POWER(3,4)
• =PRODUCT(2,3,4,5)
• =MOD(17,3)

Activity: Type the following functions in Excel worksheet and write the output:

• =CONCATENATE(‘Microsoft’,’Excel’)
• =UPPER(‘How are you?’)
• =LOWER(‘HOW ARE YOU?’)
• =LEFT(‘How are you?’,3)
• =RIGHT(‘How are you?’,8)
• =LEN(‘How are you?’)

Activity: Enter the following data in Excel worksheet and create a column (bar) chart:

Month	Rainfall (mm)
January	80
February	75
March	95
April	60
May	120
June	150

Activity: The table given below shows sports liked by the students. Create a pie chart based on the table.

Sports	Polo	Wrestling	Boxing	Cricket	Hockey
Number of Students	2	4	7	12	10

Activity: Make a list of 20 plants name and their family name.

• Sort plant name alphabetically.
• Colour first row of the list.
• Save file with the name ‘Plants’.
• Take printout of the list.

Assignment and Projects

Project (Class 8: Chapter Spreadsheet – Functions and Charts)

Q. What will be the result of the following –
i) =LEN(“STATUE OF LIBERTY!”)
ii) =TODAY()
iii) =MONTH(TODAY())
Q. Emily wants to emphasize the volume of changes of data in her spreadsheet. Which type of chart should she use to accomplish this?
Q. When does the #REF! error occur?
Q. How can you reference other worksheets?