MS Excel: Tutorials for Students

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

What is Excel?

Microsoft Excel is probably one of the top 5 most used software packages in use today.

It is a powerful and versatile spreadsheet program that allows organizing and analyzing data. Its essentially, a great big real-time calculator. It can be used to create reports, charts, automate tasks and much more.

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, the most useful software package you can learn if you are intending to work in a business related area.

Use it for:

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

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.

Layout & Key Concepts

The main components of the Excel window are:

• WorkBook and WorkSheet: An Excel WORKBOOK is a file created in Excel (with .xlsx extension). It is made up of several worksheets.
  Cell is the basic unit of a work sheet. Cells are organized by columns and rows.
• Quick Access toolbar: Lets you add commands that are always visible to you while working with the Workbook. Commonly found commands are Save, Undo, Redo, and the New Workbook commands.
• Ribbon: Located at the top of the Microsoft Excel window, the Ribbon allows you to access most of the commands available to you in Excel. The Ribbon is composed of three parts: Tabs, Groups, and Commands.
• Name Box: is located to the left of the formula bar (this is located immediately below the Ribbon). The Name Box displays the name (or cell reference).
• Formula Bar: Located immediately above the spreadsheet, it is labeled with function symbol (fx). The formula bar gets activated when you type an equal sign in any cell or click anywhere within the bar.

Cell Reference

A cell reference (or cell address) is a combination of a column letter and a row number that identifies a cell on a worksheet. For example, B2 refers to the cell at the intersection of column B and row 2.

• Relative reference: By default, all cell addresses in Excel are relative. Cell references change when you move or copy across multiple cells.
• Absolute cell reference: An absolute cell reference remains unchanged when filling other cells with the same formula. For absolute reference, you use the dollar sign ($) in the row or column coordinates.

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

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

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.

We want to create a formatting rule for a particular value in the selected 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.

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?