How to exclude hidden rows in pivot table

Normally, pivot table counts the hidden rows. We have to add a flag to each row to determine if it is hidden or not. How to do that shows this example. Continue reading “How to exclude hidden rows in pivot table” »

How to get first characters or numbers from the value

In Excel there is a simple function LEFT, which is used to get first characters of value or to remove unwanted characters from the right side. Continue reading “How to get first characters or numbers from the value” »

Free training diary 2013

This training diary is designed for all kinds of sports. You can write the plan to the table and control its implementation and monitor your progress. The template is especially suitable for recording numbers of pushups, chin-ups, running, cycling miles (kilometers), pools, every fitness performances and many others sports activities. This Excel file offers weeks and months statistics of your training.

Continue reading “Free training diary 2013” »

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

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

Selecting cells using keyboard shortcuts

This is a list of some shortcuts I consider useful. If you have some other, please leave comment. Continue reading “Selecting cells using keyboard shortcuts” »

Average with condition

There is a problem with average for some specific value in a table. Following table represents data of some worldwide company. You can see branch offices in the cities and number of their new customers per each month.

Average with condition example

How to do average for specific items (London, Prague and Paris)? Continue reading “Average with condition” »

Conditional Formatting in column (bar) charts

Excel does not support conditional formatting in charts, but we can solve it with a little trick. Original data will be split into a new table and then will be displayed by Stacked Column chart. The result will look like conditional formatting.

Example

Let us have following data table. We want to highlight a column in chart having a value less than 20.

Source table - example1

Simple chart for this table looks like this:

Simple column chart - example1

How to automatically highlight columns? Continue reading “Conditional Formatting in column (bar) charts” »

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