Excel has built-in tools that are very useful for troubleshooting formulas, even if the formulas are complex:

• Trace Precedents.
• Evaluate Formula.

The Evaluate Formula window and the tracer arrows are great tools that can be used to help you find the reason for an error.

You can give your worksheets form-like functionality without ever having to use VBA. In order to achieve this, you will have to use form controls.

Using formulas in combination with form controls, one can further extend the form control functionality and form-like appearance of the worksheet at hand.

This article uses a few simple examples to illustrate how to use form controls in Excel.

This article describes several alternative approaches to solving a lookup problem in Excel.

Specifically, the problem is: given an on-going list of invoices, create a lookup formula to find the product price based on the invoice date.

Also have a look at the article's comments, where readers propose additional solutions.

Data cleaning is one of the necessary excel skills that you are expected to possess today.

A data set might be having certain inconsistencies, and to make it more presentable and to help proper understanding of the data it is necessary to make the data look better and error-free.

This article describes 11 techniques to help you 'clean' data in Excel:

• Run a spell check.
• Make the text case consistent.
• Remove any extra spaces.
• Clear all formatting, comments, hyperlinks.
• Treat cells containing errors.
• Use 'Find and Replace' feature to make data look better.
• Replace or substitute text.
• Replace blank cells with '0', 'No Value', 'Nothing', 'Not Available', etc.
• Treat duplicates.
• Merging contents of columns.
• Splitting contents of a column.

The tutorial explains the basics of lookup in Excel, shows the strengths and weaknesses of each Excel lookup function and provides a number of examples to help you decide which lookup formula is best to be used in a particular situation.

The lookup functions discussed are:

• LOOKUP.
• VLOOKUP.
• HLOOKUP.
• VLOOKUP MATCH.
• HLOOKUP MATCH.
• OFFSET MATCH.
• OFFSET MATCH MATCH.
• INDEX MATCH.
• INDEX MATCH MATCH.

This article explains how to consolidate data from numerous Excel files using Power Query.

The situation:

• A folder contains hundreds of Excel files.
• New workbooks can be added to the folder, or some workbooks can be removed.
• Workbooks do not contain the same number of worksheets.
• Worksheets may be added or removed at any time.
• There is no naming convention for the worksheets.
• Each worksheet may contain one or more values to retrieve.
• The data needs to be consolidated often.

The solution: Use Power Query to import and consolidate the data automatically.

This tutorial explains the basics of the Excel HYPERLINK function and provides a few tips and formula examples to use it most efficiently.

Topics include:

• Excel HYPERLINK function - syntax and basic uses.
• How to use HYPERLINK in Excel - formula examples.
• Create hyperlinks to different items.
• VLOOKUP and insert a hyperlink to the first match.
• How to edit multiple HYPERLINK formulas at a time.
• Excel HYPERLINK not working - reasons and solutions.

New in Excel 2016 (for Office 365 subscription users only) are Map Charts.

Excel map charts can display your data encoded by:

• Values with a 2-color scale.
• Values with a 3-colour diverging scale.
• By category.

This article shows some examples of how to create and format Excel map charts.