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” »
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” »
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” »
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” »
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” »
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 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.
Rules:
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.
The following table presents a list of customers. They are collecting the points.
Membership depends on the number of points. The task is to assign correct membership from points intervals.
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.
Continue reading “Joining tables with the corresponding values” »
In this example, I will use conditional formatting to color weekends in the calendar. Let’s have following calendar for checking attendance.
Continue reading “How to highlight a specific day in the calendar” »
Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress
