An Outline is the grouping of row or column data so that you can manipulate the data to create summarized lists when necessary. You can then show or hide the levels (groups) of data as needed. This is very handy when working with long lists of data that you have totaled and you need to […]

Continue reading

There is no function that calculates the distinct count for a range of values in MS Excel. However by using a combination of functions the Distinct Count can be calculated.  In the example below by using a combination of the SUM, IF and FREQUENCY functions we are going to calculate the total number of clients […]

Continue reading

Do you intend setting up different views on how you would like your worksheet to appear? Then the custom view is the solution to your problem.  You can use a custom view to save specific display settings (such as column widths, row heights, hidden rows and columns, cell selections, filter settings, and window settings) and […]

Continue reading

Do you want to see an up to date Product Sales list as you transact every day. Then using a running total is the solution to your problem. A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding […]

Continue reading

Are there parts of the worksheet that you want protected? For instance you may want to protect cells that contain formulae. If that is what you intend to do, then Worksheet Protection is the answer. Worksheet Protection prevents a user from accidentally or deliberately changing, moving, or deleting important data from a worksheet or workbook. […]

Continue reading

Whilst this is not a PivotTable tool, it is a great tool to use with PivotTables. New in MS Excel 2010, a sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic […]

Continue reading

Are you looking for an alternative to the Sum function that can also ignore hidden values? Then look no further than the Subtotal function. The Subtotal is a versatile function that: Can be used for a wide range of numerical functions Ignores other Subtotals within the specified data range The SUBTOTAL function syntax has the […]

Continue reading

Ever tried to create an Excel graph from scratch? You can actually add your own Legend Entries (Series) and Horizontal (Category) Axis Labels. The location of the data should not hinder one from creating a graph. In the example below we explain how one can create a Sales Vs Purchases graph from scratch with the […]

Continue reading

When you have many different PivotTables in one report, such as a Business Intelligence (BI) report that you are working with, it is likely that you will want to apply the same filter to some or all of those PivotTables. You can share a slicer that you created in one PivotTable with other PivotTables. No […]

Continue reading

Ensuring the data that users enter into a worksheet conforms to certain standards is difficult, if you have no way of enforcing the rule. With Data Validation you can: Provide users with a drop down list of choices (so they can’t input data incorrectly) Restrict entries to a specific type or size In the example […]

Continue reading