Excel: 5 timesaving power tips - lieberknespolow

There's no much thing as too fast when you're crunching numbers on deadline in Excel. Whatever of these power tips leave speed your tasks. Did we miss an even better one? Let us know in the comments.

1. FORMULATEXT() adds notes to formulas

If you and your colleagues share spreadsheets, it's nice to have notes that explain what your formulas are doing (addition a simulate of the formula). Some organizations equal require it, specially if you're a software engineer or analyst.

This little formula and the +N social function are the flying answer to your spreadsheet documentation needs. Actuate your cursor to the column beside the formula column. If that column of cells has additional data in IT, you can insert another column (which you can hide when you're working Oregon printing the spreadsheet), or you send away create a separate "FORMULATEXT" matrix bent on the side of your first spreadsheet.

The spreadsheet shown downstairs occupies A1 through D15. Move your pointer to E5 and select the FORMULATEXT() function from Formulas > Function Library > Search & Reference. In the Reference field of the Function dialog, enter the cell address D5 or just manoeuver to information technology and click OK. Notice that a text version of the actual chemical formula prints in cell E5.

JD Sartain

Use the FORMULATEXT() function to display existent formulas

2. N() function: Other way to add notes

In this example, the normal is self-explanatory, so additional comments aren't really needed. However, if this were a long, complicated formula, you could add comments that explain what the rule is doing by just entering +N plus the comment, wrong quotes inside parentheses, at the end of your formula in D5. (Note: You wouldnot put IT at the end of the Extension in E5.)

E.g., move your cursor back to cell D5 and press Function key F2 (to edit your formula). Then type +N("your comm ents present") at the end of your formula (with no spaces). And, if you'd like (although it's redundant), replicate and paste the normal down to D15 and E15.

JD Sartain

Use the n go to insert comments at the end of your formulas

3. Case Functions fix superior- and lowercase messes

Have you ever typed an entire paragraph with the caps lock key on? In Word, the solution is an easy shortcut key (Shift+F3 repeated/cycled until the rectify casing displays). In Excel, IT's a simple function: The UPPER() function converts all characters to uppercase. The LOWER() function converts all characters to lowercase.

Popping quiz: How does one convert pep pill- or lowercase, or a mixture of some, to what typographers call the Title, Name, OR Condemnation case? The command in Stand out (which is also the preferred full term) is Proper case, or PROPER() when left-slanting as a function.

In the sample spreadsheet under, the name calling are entered in various pep pill, lower, and proper case formats. Use the Proper function to repair these mistyped names: Move your cursor to cell B4, and then detent Formulas > Function Library > Textual matter. From the Textual matter dropdown, prime Suited.

The Function Arguments dialog box leave seem. In the Text field, enter Oregon point to cell A4, and then click OK. Copy B4 low-spirited through B10 and notice how the Proper occasion has repaired all the mistyped names in this list.

Next move your cursor to cell C4 and enter the Lower function, which you can find out in the same Text dropdown menu Beaver State enter IT manually in cell C4: =LOWER(B4) and mechanical press Enter.

Move your cursor to D4 and enroll the Upper function in that cellphone, or write =UPPER(B4)and press Enter. Copy cells C4 and D4 down thru C15 and D15. Now each bring up in each list is displayed correctly. Note that these functions likewise operate for imported or copied text.

JD Sartain

Use the Vitrine functions UPPER(), LOWER(), AND PROPER() to repair case typing errors.

03 Use the Case functions Pep pill(), LOWER(), AND PROPER() to repair case typing errors

4. Transpose feature to rearrange columns and rows

Completely super users know that in Surpass William Claude Dukenfield are placed in columns and records are placed in rows. However, sometimes you inherit a spreadsheet from a rookie World Health Organization has it reversed. Retyping all that data is unthinkable, and exploitation written matter/paste one row at a fourth dimension would be horribly tedious. This doesn't seem look-alike a big mete out connected a small spreadsheet, but imagine reorganizing 40 columns and 200 rows and you'll like this tilt much better.

Highlight the data matrix you want to transpose (in our example, IT's A1 through F6). Select Copy. Motility your cursor to the new, target location. Decease to Home > Clipboard and click Paste > Paste Special. In the Paste Special dialog box, check the Transfer field loge, and click Sooner State. And that's IT! The data moves to the new location with the columns and rows transposed.

JD Sartain

The Permute feature easily converts rows to columns or columns to rows.

5. Save more seconds with Autofill

Everyone who handles series data should practice Autofill to keep open typing and retyping for things that are e'er the unvarying—for example, a list of succeeding numbers or letters, months of the yr, or days of the week.

1. Enter a twenty-four hour period of the hebdomad in cell A3.

2. Hover the pointer over the rear right corner of the cell until information technology changes to a black cross.

3. Click and retarding force horizontally or vertically to re-create the content down or over.

Notice the tag following your cursor as it drags. The info indoors the tag changes to the next item in the series (in this case, the next daytime of the calendar week).

JD Sartain

Use Autofill to automatically enter items in a series.

4. When you release the left mouse release, the Autofill Options icon appears (overwriting the black cross). Click the down arrow and note the options available:

• Copy Cells
• Fill Series
• Satiate Formatting Only
• Fill Without Format
• Tacky Fill

If not already elite, click theFill Series button.

The serial publication displays (in succeeding dictate) leading pertinent where you stopped dragging the pointer.

JD Sartain

How Autofill enters a sequence (mechanically)

Note: If the Autofill Options icon does non seem when you stop dragging the Afro-American track, Select Filing cabinet > Options> Advanced. Curlicue fine-tune to the Ignore, Simulate, & Paste section.

JD Sartain

Autofill options

You'll see these three options with radio buttons:

• Show Paste Options button when content is pasted
• Show Stick in Options buttons
• Cut, written matter, and sort inserted objects with their parent cells

Ensure that the first and second buttons are both clicked connected. (The third button is nonobligatory.)

Source: https://www.pcworld.com/article/415387/excel-5-timesaving-power-tips.html

Posted by: lieberknespolow.blogspot.com

Share this post