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.

Example

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

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.

Continue reading “Data Validation in Excel” »

How many times are the values in the table?

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

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

Delete duplicate rows

This example shows how to remove duplicate rows from a table. In the following table you can see two duplicate rows. It’s row number 2, 9, 10 and 3, 6.

Table with duplicates
Continue reading “Delete duplicate rows” »

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

Chart: Automatic update when data source changes

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

How to rotate and transpose a table

The table in this example should be rotated in such a way that we change rows per columns. We will use Transpose from the Excel menu to solve it.

Table to rotate

Continue reading “How to rotate and transpose a table” »

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.

Example

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 keep column width when you copy a table

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

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