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” »
Tags: COUNTIF
How to resize the table to fit the page
Setting the width of the table to one page is a common problem. You can do it without changing font size or changing the width of each column. As well as the width, can be adjusted the height. Let’s see how. Continue reading “How to resize the table to fit the page” »
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” »
Tags: Conditional Formatting
How to print names of columns or rows on every page
Table is sometimes longer than one page. It is very useful to print names of columns or rows on every page. This example shows how to do that. Continue reading “How to print names of columns or rows on every page” »
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” »
Tags: Column chart
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” »
Tags: Pivot Table, SUBTOTAL
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)” »
Tags: COUNTIF
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” »
Tags: LEFT
