Ex­cel time-sav­ing tips

There’s no such thing as too fast when you’re crunch­ing num­bers on dead­line in Ex­cel. JD Sar­tain’s power tips will speed up your tasks

PC Advisor - - CONTENTS -

For­mu­la­text() adds notes to for­mu­las

If you and your col­leagues share spread­sheets, it’s handy to have notes that ex­plain what your for­mu­lae are do­ing (plus a copy of the for­mula). Some or­gan­i­sa­tions even re­quire it, es­pe­cially if you’re a pro­gram­mer or an­a­lyst.

This for­mula and the +N func­tion are the quick an­swer to your spread­sheet doc­u­men­ta­tion needs. Move your cur­sor to the col­umn be­side the for­mula col­umn. If that col­umn of cells has ad­di­tional data in it, you can in­sert an­other col­umn (which you can hide when you’re work­ing or print­ing the spread­sheet), or you can cre­ate a sep­a­rate FOR­MU­LA­TEXT ma­trix out to the side of your orig­i­nal spread­sheet.

The spread­sheet shown here oc­cu­pies A1 through D15. Move your cur­sor to E5 and se­lect the FOR­MU­LA­TEXT() func­tion from For­mu­las > Func­tion Li­brary > Lookup & Ref­er­ence. In the Ref­er­ence field of the Func­tion dia­log, en­ter the cell ad­dress D5 or just point to it and click OK. No­tice that a text ver­sion of the ac­tual for­mula prints in cell E5.

N() func­tion: an­other way to add notes

In this ex­am­ple, the for­mula is self­ex­plana­tory, so ad­di­tional com­ments aren’t re­ally nec­es­sary. How­ever, if this were a long, com­pli­cated for­mula, you could add com­ments that ex­plain what the for­mula is do­ing by just en­ter­ing +N plus the com­ment, in­side quotes in­side paren­the­ses, at the end of your for­mula in D5. (Note: you would not put it at the end of the Ref­er­ence in E5.)

For ex­am­ple, move your cur­sor back to cell D5 and press Func­tion key F2 (to edit your for­mula). Then type +N(“your com­ments here”) at the end of your for­mula (with no spa­ces). And, if you’d like (although it’s re­dun­dant), copy and paste the for­mula down to D15 and E15.

Case Func­tions fix up­per- and low­er­case messes

Have you ever typed an en­tire para­graph with the caps lock key on? In Word, the so­lu­tion is an easy short­cut key (Shift+F3 re­peated/cy­cled un­til the cor­rect case dis­plays). In Ex­cel, it’s a sim­ple func­tion: the UP­PER() func­tion con­verts all char­ac­ters to up­per­case. The LOWER() func­tion con­verts all char­ac­ters to low­er­case.

Pop quiz: how does one con­vert up­per- or low­er­case, or a mix­ture of both, to what ty­pog­ra­phers call the Ti­tle, Name, or Sen­tence case? The com­mand in Ex­cel (which is also the pre­ferred term) is Proper case, or PROPER() when writ­ten as a func­tion.

In the sam­ple spread­sheet on the next page, the names are en­tered in var­i­ous up­per-, lower-, and proper-case for­mats. Use the PROPER() func­tion to re­pair these mistyped names: move your cur­sor to cell B4, then click For­mu­las > Func­tion Li­brary > Text. From the Text drop-down, se­lect Proper.

The Func­tion Ar­gu­ments dia­log box will ap­pear. In the Text field, en­ter or point to cell A4, then click OK. Copy B4 down through B10 and no­tice how the PROPER() func­tion has re­paired all the mistyped names in this list.

Next move your cur­sor to cell C4 and en­ter the LOWER() func­tion, which you can find in the same Text drop-down menu or en­ter it man­u­ally in cell C4: =LOWER(B4) and press En­ter.

Move your cur­sor to D4 and en­ter the Up­per func­tion in this cell, or type =UP­PER(B4) and press En­ter. Copy cells C4 and D4 down through C15 and D15. Now each name in each list is dis­played cor­rectly. These func­tions also work for im­ported or copied text.

Trans­pose fea­ture to re­ar­range col­umns and rows

All ex­pe­ri­enced users know that in Ex­cel, fields are placed in col­umns and records are placed in rows. How­ever, some­times you in­herit a spread­sheet from a be­gin­ner who has it back­ward. Re­typ­ing all that data is out of the ques­tion, and us­ing copy/ paste one row at a time would be hor­ri­bly te­dious. This doesn’t seem like a big deal on a small spread­sheet, but imag­ine re­or­gan­is­ing 40 col­umns and 200 rows and you’ll like this tip much bet­ter.

High­light the data ma­trix you want to trans­pose (in our ex­am­ple, it’s A1 through F6). Se­lect Copy and move your cur­sor to the new, tar­get lo­ca­tion. Go to Home > Clip­board and click Paste > Paste Spe­cial. In the Paste Spe­cial dia­log box, check the Trans­pose field box and click OK. And that’s it. The data moves to the new lo­ca­tion with the col­umns and rows trans­posed.

Save more sec­onds with Aut­ofill

Ev­ery­one who han­dles series data should use Aut­ofill to save typ­ing and re­typ­ing for things that are al­ways the same – for ex­am­ple, a list of con­sec­u­tive num­bers or let­ters or days of the week.

1. En­ter a day of the week in cell A3. 2. Hover the cur­sor over the bot­tom right cor­ner of the cell un­til it changes to a black cross. 3. Click and drag hor­i­zon­tally or ver­ti­cally to copy the con­tent down or over. No­tice the tag fol­low­ing your cur­sor as it drags. The in­for­ma­tion in­side the tag changes to the next item in the series (in this case, the next day of the week). 4. When you re­lease the left-mouse but­ton, the Aut­ofill Op­tions icon ap­pears (over­writ­ing the black cross). Click the down ar­row and note the op­tions avail­able:

Copy Cells Fill Series Fill For­mat­ting Only Fill With­out For­mat­ting Flash Fill

If not al­ready se­lected, click the Fill Series but­ton. The series dis­plays (in con­sec­u­tive or­der) up to the point where you stopped drag­ging the cur­sor. If the Aut­ofill Op­tions icon does not ap­pear when you stop drag­ging the black cross, Se­lect File > Op­tions > Ad­vanced. Scroll down to the Cut, Copy, And Paste sec­tion. You’ll see these three op­tions with ra­dio but­tons:

Show Paste Op­tions But­ton When Con­tent Is Pasted Show In­sert Op­tions But­tons Cut, Copy, And Sort In­serted Ob­jects With Their Par­ent Cells

En­sure that the first and sec­ond but­tons are both clicked on. (The third but­ton is op­tional.)

Use the FOR­MU­LA­TEXT() func­tion to dis­play ac­tual for­mu­lae

Use the N func­tion to in­sert com­ments at the end of your for­mu­lae Use the Case func­tions UP­PER(), LOWER(), and PROPER() to re­pair case typ­ing er­rors

Use Aut­ofill to au­to­mat­i­cally en­ter items in a series How Aut­ofill en­ters a se­quence (au­to­mat­i­cally) Aut­ofill op­tions The Trans­pose fea­ture eas­ily con­verts rows to col­umns or col­umns to rows

Newspapers in English

Newspapers from UK

© PressReader. All rights reserved.