How to assign values from intervals
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.
Tags: VLOOKUP
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.
Continue reading “Joining tables with the corresponding values” »
Tags: VLOOKUP
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.
Tags: Advanced Filter
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.
Continue reading “How to highlight a specific day in the calendar” »
Tags: Conditional Formatting, WEEKDAY
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.
The chart can be simple like this:
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.
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
The task is to create Order Number consisting of the Store Sequence and the Customer No. Strings have to be separated by character –
Continue reading “How to join several text strings into the one text string” »
Tags: CONCATENATE
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.
Continue reading “How to keep column width when you copy a table” »
How to change the formula to value
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” »
Quick copy values or formulas in large table
There is an easy way how to do copy faster. Let’s have large table like on the following picture. We want copy formula for TOTAL PRICE to all items in the column E. As you see table contains nearly 17000 rows.
Continue reading “Quick copy values or formulas in large table” »
