2013-0225

How to display the current totals in the filter

1 Comment[Author: Category: Other ]

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

Tags:
2013-0223

Free planning calendar 2013

2 Comments[Author: Category: Templates ]

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

2012-0930

Custom format of date and time in MS Excel

7 Comments[Author: Category: Other ]

MS Excel offers many possibilities how display date and time. The custom format can be set in the Format Cells… menu. Do the right-click and select Format Cells… .

Continue reading “Custom format of date and time in MS Excel” »

2012-0921

How to split the value

Add a Comment[Author: Category: Other ]

In this article I want to show the possibilities of splitting values to the multiple parts. There are several ways how to do it. It depends on the value you want to split. I will show you how to use Text to Columns and text functions LEFT, RIGHT, FIND, LEN. Continue reading “How to split the value” »

Tags: , , ,
2012-0911

Formula for the cells on the same address, but on the different sheets

1 Comment[Author: Category: Quick Tips ]

Let us have several identical tables that are on different Excel worksheets, but always on the same address. How to make SUM or AVERAGE of these tables? Continue reading “Formula for the cells on the same address, but on the different sheets” »

2012-0830

How many times are the values in the table?

1 Comment[Author: Category: Duplicates ]

There is an easy way to find number of occurrences of items in the table. For example, the following table contains repeated Products. We want to know, how many times was each product ordered.

Table with duplicates

Continue reading “How many times are the values in the table?” »

Tags:
2012-0827

How to highlight a specific day in the calendar

2 Comments[Author: Category: Other ]

In this example, I will use conditional formatting to color weekends in the calendar. Let’s have following calendar for checking attendance.


Attendance calendar

Continue reading “How to highlight a specific day in the calendar” »

Tags: ,
2012-0823

Chart: Automatic update when data source changes

1 Comment[Author: Category: Charts ]

Chart created from a simple data table is static and when the source changes, the chart has to be update manually. There is a possibility how to update the chart automatically. Table on the following picture is the source of the chart.

Simple table

 

The chart can be simple like this:

Chart from simple table

Continue reading “Chart: Automatic update when data source changes” »

2012-0725

How to keep column width when you copy a table

Add a Comment[Author: Category: Quick Tips ]

There is a problem with copying table having different width of columns. We want to copy the following table to another sheet.

Table with different width of columns

Continue reading “How to keep column width when you copy a table” »

2012-0725

How to change the formula to value

3 Comments[Author: Category: Quick Tips ]

There are many reasons for replacing formula with its result.

For example 1: If you delete cells belonging to the formula, the result of the formula goes wrong. For preservation the result, you have to change the formula to the value. After that other changes in cells will have no effect.

For example 2: When you copy a cell with a formula, the source cells are changing and therefore the result is changing too. However, this behavior is not always appropriate. You can solve it by adding letter $ into the formula or by changing the formula to a value. Continue reading “How to change the formula to value” »

« Previous Entries
Next Entries »

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