Filtering, sorting, and conditionally formatting data Max Ms-Excel 2010 – 3

Now you are going to find out how to control the way numbers are displayed, and make Excel automatically calculate columns totals. You are going to create a spread to hold data about the height, weight and foot size of people in a group.
*** Open a new Excel workbook. Select File and the click on New.

Filtering, sorting, and conditionally formatting data

*** Select Blank workbook and click on Create.
*** Type the word Measurements in cell A1. It will overflow the cell, but that’s OK.
*** Press Enter
*** Select Cell A1 again and make it bold by clicking the BOLD button on Font section of the
home tab.

Filtering, sorting, and conditionally formatting data2

*** Position the pointer with your mouse so that it is on the line between column headers A
and B. The pointer will change to a double-headed arrow.

Filtering, sorting, and conditionally formatting data3

*** Use the left mouse button and hold it down while you drag to the right. The column will
widen. Make it wide enough to fit the heading.

Filtering, sorting, and conditionally formatting data4

*** Now type the other headings in the top row, use your own name. Make up a group name for
the title in C1.

Filtering, sorting, and conditionally formatting data5

*** Highlight the whole row 1

Filtering, sorting, and conditionally formatting data6

*** Click on the bold button on the home tab.Filtering, sorting, and conditionally formatting data7

*** Now we are going to widen the column a second way. Position the pointer between the
column headers F and G containing your name.

*** Double click. The column automatically widens so that the name will fit the cell.

Filtering, sorting, and conditionally formatting data8

Save your workbook, call it Measurements.

Filtering, sorting, and conditionally formatting data9

Formatting

*** Fill in the rest of your workbook like this

Filtering, sorting, and conditionally formatting data10

*** You will need to make the columns wider to fit the headings. Use the double click on the
header to make the column width change automatically.

Filtering, sorting, and conditionally formatting data11

Notice that the cells that have numbers are on the right of the cell, this is called right justified. The
text you enter is always left justified.
The measurements would look much easier to read if they were all shown to 1 decimal place. Excel
will ignore if you enter 127.0 and display 127.
*** Select the cells B4 to D17 by clicking in B4 and holding down your left mouse button and
dragging down to D17

Filtering, sorting, and conditionally formatting data12

*** Click on the home tab and look for the section called number.

Filtering, sorting, and conditionally formatting data13

*** Select the Increase decimal button

Filtering, sorting, and conditionally formatting data14

t will now look like this.

Filtering, sorting, and conditionally formatting data15

*** Now click on the decrease decimal button. To show the numbers to 1 decimal place.

Filtering, sorting, and conditionally formatting data16

*** Now your work book should look like this.

Filtering, sorting, and conditionally formatting data17

*** Save your workbook again.

Try holding down Ctrl and the letter S.

Filtering, sorting, and conditionally formatting data18

Adding a column of numbers together
We want to add up the heights of everyone in the group and put the total in cell B11. To do this we use
the Autosum button.
The sign ∑ (pronounced Sigma) on the Autosum button is the Greek letter S. Greek letters are used a
lot in maths.  π is the Greek letter p. The area of a circle  = πr²
*** Click in cell B11 to make it the active cell.
*** Click on the formula tab

Filtering, sorting, and conditionally formatting data18

*** Click on the Autosum button
*** Excel will guess which cells you want to sum. Your screen will look like the one below.

Filtering, sorting, and conditionally formatting data19

*** Press Enter. You will now see the answer.
*** Now do the same in cell C11 to find out the total weight.

Filtering, sorting, and conditionally formatting data20

*** Now do the same in cell D11.

Filtering, sorting, and conditionally formatting data21

This time Excell thinks you want to find the sum total of the cells to the left.

*** Select cells D4 to D9 by click in D4 and dragging your mouse down to D9.

Filtering, sorting, and conditionally formatting data22

*** Press Enter and your answer will apear.
*** Save your worksheet

 

 

Previews || Next || Fist

Thanks #Internet

Write Your Comments

comments

Leave a Reply