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

Custom format of date and time in MS Excel

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

How to split the value

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

Data Validation in Excel

Data validation is used when it is necessary to keep an eye on what values are entered into the cell. This tool allows you to specify the values that may be written into the cell. This helps for example to avoid entering wrong date or entering text, where is supposed to be a number, etc.


There is a list of employees. Each column in the following Excel table have some rule for writing values. We have to modify Data Validation to be able to enter only correct values into the cells.

List of employees


Employee number is exactly 5 characters long.

Salary can be set in the range 600-2000.

Bonus cannot be greater than 10 % of the salary.

Date of entry can be set only as today.

Department must be one of the values from the list of Departments.

Continue reading “Data Validation in Excel” »

How to assign values from intervals

The following table presents a list of customers. They are collecting the points.

Customers list

Membership depends on the number of points.  The task is to assign correct membership from points intervals.

Points intervals

Continue reading “How to assign values from intervals” »

Joining tables with the corresponding values

There are two tables. The first contains list of products with Product ID. The second is the actual quantity of products in the stock. Our task is to assign correct Product Name to Product ID in the second table.

List of products and actual quantity

Continue reading “Joining tables with the corresponding values” »

How to highlight a specific day in the calendar

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

How to join several text strings into the one text string

There are two ways to merge text strings into one Excel cell. You can use the Concatenate function or operator &. Let’s see how.


Text to join

The task is to create Order Number consisting of the Store Sequence and the Customer No. Strings have to be separated by character

Merge result Continue reading “How to join several text strings into the one text string” »

How to create drop down menu in Excel worksheet

There are two ways how to insert drop down menu to the sheet. You can use either Data Validation – List or Form Control – Combo Box. Both have their advantages.


The following table represents a simple order form. The task is to create a drop-down menu for selecting the method of payment in the cell E3. Method can be VISA, MASTERCARD or AMERICAN EXPRESS.

Simple order form

Continue reading “How to create drop down menu in Excel worksheet” »

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