Sundry Excel Tips

• Sharebar

I read bunches of excel tips and hints from other websites and every now and again I post links and details of the best ones. Here's a few good ones I have picked up recently.

Counting number of unique entries in a specified range From ExcelTip
Counting number of unique entries in a specified range is simple by using the SUMPRODUCT and COUNT functions.
The range can be all in one column or row, or may span multiple (adjoining) columns/rows.
The formula would be written as: =SUMPRODUCT((1/COUNTIF(A1:C5,A1:C5&""))) where A1:C5 is replaced with the range you want to apply the count to.

Ben Rampson, a Program Manager on the Excel team writes a detailed tutorial on how to create an excel chart template that you can reuse. Here is his rationale for using this feature. Click through to see the full tutorial.

A company policy may require that a chart use particular colors, a project may demand a necessary chart layout, or a complex chart may necessitate the setting of many specific element properties. While there is always a onetime cost required to set up a chart for the first time, there is an easy way avoid these costs in the future when you need to re-create a similar chart.

One frequently underutilized feature in charting is the Chart Template. A chart template allows a user to quickly save and reapply settings from a previously created chart. Chart templates are standalone .crtx files that can be applied in a similar manner as chart types. In addition to saving the chart type, template files also contain chart element setting and formatting information. This information includes settings such as line and fill properties, axis settings, and element placement.

It also should be noted that chart templates do not save customer data, preventing customer information from being copied and saved into template files (customer data includes: data point and series values, text on titles, etc.). Although data is not saved, the existence, formatting, and layout of all elements is stored in the template. Assume a template is created from a chart that contains a title. When the template is applied the resulting chart will have a title in same location and with the same formatting as the chart used to generate the template, however, the default text of "Title" will appear in the element since the title's data has not been stored in the template.