Free exercises COUNTIF(S) and SUMIF(S) functions

I have prepared two Excel tables with data and many tasks to use COUNTIF(S) and SUMIF(S). Each task contains also the result. Continue reading “Free exercises COUNTIF(S) and SUMIF(S) functions” »

The difference between a line chart and a scatter chart

People often ask: Should I use the line or the scatter? In this article, I try to explain the difference and when to use each chart type. Continue reading “The difference between a line chart and a scatter chart” »

SUMIF and COUNTIF with date

Date can be used as a condition in the Excel functions SUMIF and COUNTIF. But there can be some problems. I show you examples. Continue reading “SUMIF and COUNTIF with date” »

How to highlight whole row using Conditional Formatting

Conditional formatting is very useful tool for highlighting cells in MS Excel. But there is the problem with coloring whole row or whole column of table. This example shows how to do that. Continue reading “How to highlight whole row using Conditional Formatting” »

Excel always changes numbers to dates or currency – solution

Excel changes cell format to date or currency always when you write some value? This problem can have several reasons. Continue reading “Excel always changes numbers to dates or currency – solution” »

Two different chart types in one graph

In this example, I will show you how to combine column chart with line chart. For the rest of chart types is the same procedure. Continue reading “Two different chart types in one graph” »

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 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” »

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” »

Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress