16 sim­ple, yet pow­er­ful, Ex­cel func­tions you need to know

These easy starters will get you on your way to mas­ter­ing spread­sheets.

PCWorld (USA) - - Here’s How - BY IAN PAUL

Power users love to talk about how pow­er­ful and awe­some Ex­cel is ( go.pc­world.com/ expw), what with its Pivot Ta­bles, nested for­mu­las, and Boolean logic ( go.pc­world.com/blgc). But many of us barely know how to find the Au­to­sum fea­ture, let alone use Ex­cel’s func­tions to cre­ate pow­er­ful for­mu­las.

If you’re part of the Ex­cel 99 per­cent, here are 16 handy Ex­cel func­tions that will get you well on your way to spread­sheet mas­tery.

1. =SUM()

The first func­tion any­one should know tells Ex­cel to carry out ba­sic arith­metic. Let’s say

you wanted the num­bers in cell A2 and cell B2 to be added to­gether in cell C2. All you’d do is en­ter =SUM(A2:B2) into C2, then hit En­ter. In an in­stant you’d see the sum of the two cells ap­pear.

You can use this func­tion, and most of the func­tions listed here, to en­com­pass as many cells as you re­quire. You can also select them with your mouse, but I find it’s far eas­ier to type in a range of cells.

2. =AV­ER­AGE()

Av­er­age does ex­actly what it says, and works sim­i­larly to SUM. If you have a work­sheet con­tain­ing your monthly earn­ings over the past year in col­umns A2 through A13, type =AV­ER­AGE(A2:A13) into A14 to get the av­er­age of all monthly earn­ings.

Again, you can also use your mouse cur­sor to click-and-drag to high­light a range of cells, or Ctrl

+ click to hand­pick in­di­vid­ual cells.

3. =ME­DIAN()

The me­dian and the av­er­age are of­ten con­fused, but they are not the same thing. The me­dian takes a group of num­bers such as (in our ex­am­ple be­low) 2, 6, 15, 31, and 56, and fig­ures out the mid­dle point of the group. In other words, half the num­bers are above the me­dian, and half are be­low. We’ve com­pared the me­dian to the av­er­age in the screen­shot to show you the dif­fer­ence.

To get the me­dian for the group in column E, in cell E7 we typed the for­mula =ME­DIAN(E2:C6) and hit En­ter. In our ex­am­ple case, the me­dian is 15. Had there been an even num­ber of nu­mer­als in the series, the me­dian would have been the mid­point be­tween the two mid­dle num­bers.

4. =MIN()

If you need to find the small­est num­ber in a range of cells, MIN can help you do that. Us­ing =MIN(B3:B39) will give you the small­est num­ber con­tained in those cells.

5. =MAX()

Max is the coun­ter­part to MIN and does the

op­po­site show­ing you the largest num­ber con­tained in a range of cells.

6. =TRIM()

If you copy text from another pro­gram into Ex­cel you can of­ten end up with ex­cess white­space that turns your spread­sheet into a visual hor­ror. TRIM can help you clean it up.

TRIM can only deal with text from a sin­gle cell. So start by clean­ing up the text in cell B1 by typ­ing =TRIM(B1) into cell C1, for ex­am­ple. The re­sult will be cleaned up text in cell C1. Re­peat the func­tion for any other cells you need ti­died up.

If you’re look­ing to clean up line breaks, try CLEAN in­stead of TRIM.

7. =COUNT()

If you need to know how many cells in a given range con­tain num­bers, don’t bother count­ing by hand—just use the COUNT func­tion. If you have a mix of num­bers and text in cells A21-A50, for ex­am­ple, type into cell A51 =COUNT(A21:50) and you’ll have the an­swer in no time.

8. =COUNTA()

Sim­i­lar to the above ex­am­ple, you can use =COUNTA() to count the num­ber of cells in a given range that con­tain char­ac­ters such as num­bers, text, or sym­bols, as well as er­ror val­ues.

9. =LEN()

If you want to count the num­ber of char­ac­ters in a sin­gle cell, in­clud­ing white spa­ces, check out LEN. Want to know how many char­ac­ters are in cell A1? Just type =LEN(A1) into a dif­fer­ent cell and you’ll find out.

10. =CONCATENATE()

This takes data from two cells and turns it into one. Check out our pre­vi­ous look at Ex­cel func­tions to see how this can be use­ful ( go.pc­world.com/15ex).

11. =DAYS()

Want to know the num­ber of days be­tween

two dates in a spread­sheet? If you had, for ex­am­ple, Septem­ber 5, 2018, in cell A4 and De­cem­ber 27, 2018, in A5, then just use =DAYS(A5, A4) to get the an­swer as a neg­a­tive num­ber—re­vers­ing the or­der of cells would give you a pos­i­tive num­ber.

12. =NETWORKDAYS()

Know­ing the num­ber of days is great, but if you need to know how many week­days that range en­com­passes, turn to NETWORKDAYS (that’s ‘net work days’) in­stead. This func­tion uses the same for­mat as DAYS, but you need to use an as­cend­ing cell or­der to get a non­neg­a­tive num­ber. So =NETWORKDAYS(A4, A5) will give you 80 and not –80.

13. =SQRT()

Need to know the square root of 1764? Type =SQRT(1764) into a cell, hit En­ter, and you’ll find the an­swer to life, the uni­verse, and ev­ery­thing—in­clud­ing the ex­am­ple for­mula.

14. =NOW()

Want to see the cur­rent date and time when­ever you open a par­tic­u­lar work­sheet? Type =NOW() into the cell where you want the date and you’re done. If you want to see a fu­ture date from right now, you can use some­thing like =NOW()+32. The now func­tion does not take any ar­gu­ments so don’t put any­thing in be­tween the brack­ets.

15. =ROUND()

As its name sug­gests, this func­tion lets you round off num­bers. ROUND re­quires two ar­gu­ments: a num­ber or cell, and the num­ber of dig­its to round to. If you have the num­ber 231.852645 in A1, for ex­am­ple,

=ROUND(A1, 0) gives you 232,

=ROUND(A1, 1) gives you 232.9, and

=ROUND(A1, -1) re­turns 230.

16. =ROUNDUP(), =ROUNDDOWN()

If you want more di­rect con­trol over round­ing up or down there are func­tions for that too. ROUNDUP and ROUNDDOWN use ex­actly the same ar­gu­ment for­mat as ROUND. To learn more about ROUND and its coun­ter­parts, check out Microsoft’s sup­port pages ( go. pc­world.com/mspt).

3.Av­er­age and me­dian are of­ten con­fused, but this ex­am­ple shows how the av­er­age rep­re­sents a sum of num­bers di­vided by their quan­tity. The me­dian rep­re­sents a mid­dle point in the series of nu­mer­als, re­gard­less of how they add up.

7.Ex­cel’s COUNT func­tion can tell you how many cells in a series con­tain num­bers rather than words, dates or other kinds of en­tries. This can be help­ful when you’re manag­ing a com­plex spread­sheet.

12. Cal­cu­lat­ing the week­days in 2019.

Newspapers in English

Newspapers from USA

© PressReader. All rights reserved.