Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

11 Excel tips for power users

Sharon Machlis | May 2, 2016
Learn how to make tables more effective, reshape data, easily look up data and more.

That's where MATCH comes in. MATCH searches for a value in a range of cells and returns the location of what's matched, using the following format:

=MATCH(SearchValue,RangeToSearch,MatchType)

(Match type can either be 0 for exactly equal, 1 for largest value less than or equal to what you're searching for or -1 for the smallest value that is greater than or equal to your lookup value.)

So, if you wanted to find the location of a cell in column B that was exactly 999, you could use:

=MATCH(999, B2:B79, 0).

And, so the combination: MATCH, looking for a specific value based on a search term, returns a cell location; and INDEX needs a location as its second formula argument.

10. Watch a formula be evaluated step by step (for Windows only). Have a complicated formula? If you want to see how it gets evaluated, go to Formulas > Evaluate Formula to see the calculations run step by step.

11. Import and refresh data from the Web into Excel. This works best when you've got well-formatted HTML tables on a Web page; with more free-form text (or even poorly formatted tables), you'll need to do a fair amount of additional editing to get your data into a form you can analyze.

With that warning in mind, if you want to pull an HTML table from the Web into Excel, head to the Data tab on Excel for Windows and select: New Query > From Other Sources > From Web

Enter the URL of the appropriate Web page. Excel will look for and list available HTML tables on that page. Click on a table to see a preview; when you find the one you want, click Load.

Why not just copy and paste a well-formatted HTML table into Excel? If the data updates frequently, you can easily refresh it by right-clicking in the table and selecting Refresh instead of having to copy and paste new data.

For more on the conference, check out the Microsoft Data Insights videos on YouTube.

Excel tips resource list

Videos

Tips and Tricks for Working With Data in Excel
Matt Fichtner and Chris Gross
Microsoft

Cool Tips And Tricks With Formulas in Excel
Wayne Winston
Indiana University

Using INDEX/MATCH to look up without using left-most column
Lynda.com

More videos
Microsoft Data Insights Summit 2016

Articles

AGGREGATE function
Microsoft

Unpivot columns (Power Query)
Microsoft

Excel 2010 cheat sheet
Preston Gralla and Rich Ericson
Computerworld

Your Excel formulas cheat sheet: 15 tips for calculations and common tasks
JD Sartain
PC World

 

Previous Page  1  2  3  4  5 

Sign up for CIO Asia eNewsletters.