If you work in consulting or some other profession that requires you to analyze data, sooner or later, you'll need to build a histogram. This is because the histogram is one of the most effective methods to visualize and understand a data set.

Conceptually, a histogram is fairly simple, but building a histogram is often a clunky process. This article describes a histogram builder template that makes the process easy. It includes adjustable bin sizes and a chart with dynamic ranges to automatically show only the selected number of bins.

Data visualization basically comes down to two techniques: simplification and emphasis.

This article illustrates the techniques via an example:

• Simplify the graph: Remove the border, horizontal grid lines, vertical axis, legend, title, and the color!
• Emphasize your message with text: Add direct labels, a descriptive title, and a descriptive subtitle. Make sure the text is hierarchical.
• Emphasize your message with color: Match your graph's color palette to your organization's logo.

In this tutorial, you will learn a few quick and efficient ways to check and debug formulas in Excel:

• Use F2 to edit Excel formulas.
• Use F9 to evaluate and debug formula parts.
• Debug a formula by using the Evaluate Formula feature.
• Highlight and match parenthesis pairs in Excel formulas.
• Highlight all cells a given formula refers to.
• Highlight all formulas that reference a given cell.
• Trace formula relationships in Excel (Trace Precedents and Trace Dependents).
• Monitor formulas and their calculated values (Watch Window).

Microsoft are adding new features to Excel, in part driven by user feedback via Excel UserVoice.

Recently added features are:

• Funnel charts. Show values across multiple stages in a process, typically resembling a funnel.
• Improved autocomplete. Searches all functions containing a partial name, rather than requiring an exact match.
• TEXTJOIN. Combines text from multiple ranges with each item separated by a delimiter that you specify.
• CONCAT. Like CONCATENATE, but supports range references in addition to cell references.
• IFS. Boolean conditions are tested in the order that you specify, including an else "catch all".
• SWITCH. Evaluates an expression against a list of values in order and returns the first matching result.
• MAXIFS. Returns the largest number in a range that meets a single or multiple criteria.
• MINIFS. Returns the smallest number in a range that meets a single or multiple criteria.

There are a few rules for model design that should be followed when designing the layout of a model. Most experienced modellers will follow these instinctively, as they are generally common sense:

• Separate inputs, calculations, and results, where possible.
• Use each column for the same purpose.
• Use one formula per row or column.
• Refer to the left and above.
• Use multiple worksheets.
• Include documentation sheets.
• Design issues: time series, data collection, model purpose, and model audience.

Coders have long banned Hungarian notation from their VBA modules. It's meaningless when the code editor is slick enough to show information about a variable.

But now a new crop of equally meaningless, over-used coding errors have begun to litter code modules... and these need to go, too:

• Hungarian notation.
• Long Sub and Function procedures.
• Meaningless naming of variables and procedures.
• Using error handlers, just for the sake of it.
• Using comments, just for the sake of it.
• Not validating data.
• Too many parameters.
• Using magic values.
• Declaring variables incorrectly.
• Coding more than you have to.

The VBA Like operator is something so useful I am often surprised how rarely it is used in Excel and Access VBA.

I often tend to see the Like operator as the last resort before using Regular Expressions in VBA. It replaces greatly the VBA InStr function when needing to check if a certain substring is present within a certain string.

The main take-aways are:

• Use the VBA Like instead of the InStr function to check if a string contains a substring.
• Consider using the VBA Like before resorting to VBA Regular Expressions.
• Be sure to master string manipulation too!

There comes a time in many Excel users' careers where we start to write incredibly complex Excel formulas to summarise or extract data from poorly structured workbooks.

The true Excel Guru knows that if the data were in a tabular format we'd be able to use the amazing tools Excel has available. Tools that make mincemeat of complex formulas.

So, next time you're faced with a complicated formula you should ask yourself if changing the layout of the workbook could be the solution instead of writing a mind bending formula that is likely to end up broken the minute a less experienced Excel user tries to edit it.