Pivot table is a feature of Excel that takes a long list of data, summarizes it into a shorter list that is more easy to analyze and assimilate.
The best way to explain how to create a pivot table in Excel 2007 is by using an example.
Below is a list of products, salesmen and total sales [...]
To hide rows based on cell value in Excel 2007 follow these steps:
Prepare your data including a header row.
In the Home tab, Editing group, click Sort and Filter then select Filter from the menu.
Your data will now look like this:
In this example we want to hide rows where the sales are greater than or equal [...]
One of the newly introduced functions in Excel 2007 is the IFERROR Function. If you are using a formula in a cell, this function allows you to trap formula errors and specify a value to be substituted in the cell in case of an error.
Syntax
IFERROR(value,value_if_error)
Value is the argument (formula) that is checked for an error.
Value_if_error [...]
To copy data and then paste it as a picture (image) follow these steps:
Select the range you want to copy.
In the Home tab, Clipboard group, click Copy.
Select a cell in the range where you want to paste the picture.
In the Home tab, Clipboard group, click on the arrow below the Paste icon, point to As [...]
If you want to validate a cell to restrict data entry to a date range then follow these steps:
Select the cell you want to validate.
On the Data tab, in the Data Tools group, click Data Validation.
The Data Validation dialog box will open.
In the Data Validation dialog box, click the Settings tab.
Click on the Allow box [...]
If you want to validate a cell to restrict data entry to values in a drop-down list follow these steps:
Select the cell you want to validate.
On the Data tab, in the Data Tools group, click Data Validation.
The Data Validation dialog box will open.
In the Data Validation dialog box, click the Settings tab.
Click on the Allow [...]
To calculate a running average, or a moving average, for a range of cells (a column), you use the AVERAGE function. The only trick you need to apply is to make your range changing continuously.
In the example below we want to calculate the running average of the sales from January to June.
Our ranges for the [...]
To average cells based on multiple criteria in Excel 2007 you use the AVERAGEIFS function.
Syntax
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Average_range is the range of cells to average, including numbers or names, arrays, or references that contain numbers.
Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.
Criteria1, criteria2, … [...]
To average a range of values that meet a given criteria or condition, you use the AVERAGEIF function.
Syntax
AVERAGEIF(range,criteria,average_range)
Range : The group of cells to be evaluated for criteria.
Criteria: is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged.
Average_range: is the actual set of [...]
1. Select the range from which you want to delete duplicate rows.
2. In the Data tab, Data Tools group click Remove Duplicates.
3. The Remove Duplicates window will open.
4. Specify whether your columns have headers or not.
5. Select the column(s) from which you want to remove duplicates then click OK.
6. The rows with duplicate data will be deleted and you will get [...]
