## How to exclude hidden rows in pivot table

Normally, pivot table counts the hidden rows. We have to add a flag to each row to determine if it is hidden or not. How to do that shows this example. Continue reading “How to exclude hidden rows in pivot table” »

## Value of the last filled cell in a column or in a row

I’m using Array Formulas to find the last filled or the last non empty cell ,it’s the same :). Important: Each formula must be confirmed by Ctrl+Shift+Enter (not just Enter). Continue reading “Value of the last filled cell in a column or in a row” »

## Pareto Analysis in Excel (part 1)

Pareto principle is formulated as: 80% of the consequences (eg profit or number of rejects) comes from 20% of causes. It can be used in different sectors of human activity (eg, economy, quality of production). We will construct a graph that will show us the major causes. Continue reading “Pareto Analysis in Excel (part 1)” »

## How to get first characters or numbers from the value

In Excel there is a simple function LEFT, which is used to get first characters of value or to remove unwanted characters from the right side. Continue reading “How to get first characters or numbers from the value” »

## How to highlight first Monday of the month in Excel table

Let’s have a list of dates or some kind of calendar. We want to highlight first Mondays or Tuesdays or Wednesdays etc. in each month. The example shows how to use conditional formatting to solve this task. Continue reading “How to highlight first Monday of the month in Excel table” »

## How to calculate the Grand Total using SUMPRODUCT

SUMPRODUCT is very useful Excel function. It helps us to find the grand total of the table without need auxiliary calculations. This function multiplies corresponding items in arrays and then it sums their results. In math it’s called scalar product or dot product. Continue reading “How to calculate the Grand Total using SUMPRODUCT” »

## How to display the current totals in the filter

Common Excel functions like SUM, AVERAGE, MAX, MIN or COUNT always calculate with the range of cells that you give to them. It does not matter if cells are visible or not. When we use the filter to select some of data, we want to calculate only the visible cells. There is one nice function called SUBTOTAL that solves this. It can replace 11 other functions and you can choose if it calculates hidden cells or not. Continue reading “How to display the current totals in the filter” »

## Causes and solutions of #REF! errors in Excel

This is one of the most problematic errors in Excel. There is no direct way how to fix it. I show you some of the frequent examples with #REF errors. The worst case is if you open the file and you see the # REF error in it. At first you may try to contact the file creator and ask him about the original formula. If you are the creator of the file, you have to decipher the composition of the original formula. I hope these examples will help you. Continue reading “Causes and solutions of #REF! errors in Excel” »

## Free planning calendar 2013

I have created the very simple planning calendar for 2013. Its advantage is that it can adapt to all language versions of Excel. So you will see descriptions written in your own language. Continue reading “Free planning calendar 2013” »

## Missing date in the chart

The table in this example shows dates and values. There is a missing dates. It causes a big blank space in the chart. Continue reading “Missing date in the chart” »