Nine things everyone should know how to do with a spreadsheet

By Rob Griffiths, Macworld |  Enterprise Software

Excel: Select the cell or range you'd like to name, then select Insert > Name > Define, which will pop up a new window. Type the name you'd like to create in the first box, then click Add. Repeat for as many names as you'd like to define. Once you've defined all your names, Excel even provides a way to apply them to existing functions. Select Insert > Name > Apply, and you'll get a little window showing all your named cells and ranges. Hold down the <Shift> key, click on the first name in the list, then click the last name in the list to select them all. Click OK, and Excel will insert the names into any function that references a named cell or range.

Once you've named a cell or range, the spreadsheet always uses it in formulas--even if you click on a cell, Excel will insert its name in the formula.

Numbers: Sadly, it doesn't support named ranges.

Sheets: Select the cell or range you'd like to name, then select Data > Named Range. This will display a sidebar where you can type the name of the range and (if necessary) change the cell reference. Click Done, and you've created a named range (even if it's just one cell). I'm not aware of any way to apply newly created names to existing formulas. Unlike Excel, Sheets won't use a name unless you specifically type it in.

6. Extract Data From Ranges

One of the most-common uses of a spreadsheet is to create tabular data and then extract values from that data. Consider the following worksheet for a company that sells shipping supplies:

Your job is to answer coworkers' queries, such as "What's our cost on the packing peanuts?" and "How many rolls of tape do we have on hand?" You could, of course, just look at the table every time someone asked a question, but consider that the real-world version of the table may have hundreds or thousands of rows. There has to be a better way.

And there is: The VLOOKUP and HLOOKUP functions pull data out of tables, by matching a lookup value to a value in the table. (These functions are identical in all three apps, so I'll explain how they work in Numbers.)

VLOOKUP is used when your data is as shown in the table above: each item is on its own row, with multiple columns of associated data. HLOOKUP is used when each item is in its own column, with multiple rows of associated data.

The layout of the formula is the same in each app:

VLOOKUP(LOOKUP_VALUE, COLUMN_NUMBER (ROW_NUMBER for HLOOKUP) TO RETURN, REQUIRE EXACT MATCH)


Originally published on Macworld |  Click here to read the original story.
Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Answers - Powered by ITworld

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Ask a Question