2012-0725

How to change the formula to value

3 Comments[Author: Category: Quick Tips ]

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

2012-0719

Quick copy values or formulas in large table

Add a Comment[Author: Category: Quick Tips ]

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.

Simple large table

End of large table Continue reading “Quick copy values or formulas in large table” »

2012-0718

How to use Combo Box (Form Control)

4 Comments[Author: Category: Form Controls ]

Combo Box is one of the ways to create drop down menu in Excel. More about drop down menus is on https://excel-example.com/other-tutorial/how-to-create-drop-down-menu-in-excel-worksheet.
In this example we create a simple Order Form that calculates final prize. Combo boxes will be used for choosing goods. Here is simple list that will be source for Order Form.
simple list of goods Continue reading “How to use Combo Box (Form Control)” »

Tags: ,
2012-0712

How to create drop down menu in Excel worksheet

7 Comments[Author: Category: Other ]

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.

Example

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

Tags: ,
2012-0704

Selecting cells using keyboard shortcuts

Add a Comment[Author: Category: Quick Tips ]

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

2012-0704

Automatic change in the chart according to the current date

2 Comments[Author: Category: Charts ]

Let’s have a list of some values. The values are taken every day. The task is to create chart showing only last 5 days from today. Charts must be updated automatically.

Important: I wrote this on 4.7.2012, so for this example is 4.7.2012 the current date.

List of values by date
Continue reading “Automatic change in the chart according to the current date” »

Tags: ,
2012-0702

Automatic change of data source in a pivot table

2 Comments[Author: Category: Other ]

There is a problem with Pivot Table. When you add new row or column to source table, the pivot table won’t expand range. You must do it manually using menu “Change Data Source”. So this is the question, how can I do it automatically? Continue reading “Automatic change of data source in a pivot table” »

Tags:
2012-0702

How to hide error values

3 Comments[Author: Category: Other ]

In Excel table may occur some error values like #DIV/0, #N/A … But it does not necessarily mean an error in a formula. It could mean that there is some unfilled value or some value is not in table and so on. In this cases is better display nothing or some specific word or number instead of error value.  I have prepared table containing #DIV/0 values and two ways how to solve it. The first is using formula and the second is made by conditional formatting.

Table with error values

The bottom table have simple formula for calculating percentage of items revenue. For example in B9 is formula =B2/B$6. So, when cells in the top table is empty, result of formula is #DIV/0. Continue reading “How to hide error values” »

Tags: , ,
2012-0629

How to quickly lock (protect) all of the cells containing formula

1 Comment[Author: Category: Other ]

There are many values and formulas on the sheet. We want to protect all formulas against overwriting. Other cells must remain unlocked for entry. We don’t need to manually go through cell by cell.

This is a table with some formulas:

Let’s see how to do that: Continue reading “How to quickly lock (protect) all of the cells containing formula” »

2012-0622

Average with multiple conditions

2 Comments[Author: Category: Other ]

There is problem with average for some specific values in a table. We need to make average of the items that meet several criteria. 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 and year.

Average with multiple conditions example

 How to do average of specific branch offices for specific year? Continue reading “Average with multiple conditions” »

Tags: ,
« Previous Entries
Next Entries »

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