Bute Tipster - Microsoft Excel
Bute Tipster is a Knowledge Database giving information, hints and tips on Microsoft Office applications and Personal Skills. Bute Tipster is updated daily and you can follow the daily entries on Twitter, Facebook and LinkedIn.
Microsoft Excel
Quick start: Apply conditional formatting
By applying conditional formatting to your data, you can quickly identify variances in a range of values with a quick glance.
This graphic shows temperature data with conditional formatting that uses a colour scale to differentiate high, medium, and low values. The following procedure uses that data.
How?
 |
Select the data that you want to conditionally format
|
 |
Apply the conditional formatting
In a three-colour scale, the top colour represents higher values, the middle colour represents medium values, and the bottom colour represents lower values. This example uses the Red-Yellow-Blue colour scale. |
 |
Experiment with the conditional formatting On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then experiment with the available styles.
|
See Also
With thanks to Microsoft Office and their fantastic Help for this example.
Custom Formats
Custom formats allow you to set the exact appearance that you require for your data. For example, you may want to add text to values to indicate exactly what the value represents, such as kg or lb. You may want to display the name of a day, rather than the date. You may want to separate the £ sign from numbers by one space. These can all be achieved using custom formats.
To find out more about the options, symbols used, and syntax for custom formats, search in Help using the keywords Custom Number Format.
For Your Reference... To create a custom format: 1. Select the cell(s) to format 2. Select Format > Cells 3. Click on the Number tab 4. Select Custom, type the new format, then click on [OK]
To review the complete Excel workshop containing Creating Custom Formats for 2003 :Click here.
To review the complete Excel workshop containing Creating Custom Formats for 2007:Click here.
What If Formulas
The real magic of Excel lies in the use of formulas. If all Excel could do was allow you to input numbers and words it would be virtually no different to a word processing package – without the fancy features for processing words!
Just as Word allows you to work with words, Excel allows you to process numbers. This is done with formulas that are used to perform calculations.
What-if Formulas When you’ve added formulas to your worksheet you have a calculation model. Every time you change one of the dependent values that are used in a formula, that formula, and every one that is dependent on it will update instantly. This allows you to perform what-if testing. For example, you can enter what if formulas that answer questions such as ‘what if inflation goes up by 2%?’
Three What-If Functions Excel has three different functions that can be applied for what-if testing: • SUMIF calculates a total amount based on a single condition. • COUNTIF counts the number of times a value appears in a range of cells. • IF is used for either/or scenarios.
To review the complete Excel workshop containing What-If Formulas for 2003 :Click here.
To review the complete Excel workshop containing What-If Formulas for 2007:Click here.
PivotTables
A PivotTable is a summary of the data stored in a list in a workbook. A list is often a large amount of data in a worksheet with multiple rows and columns. The sheer volume of data held in the list can mean that analysis by the usual techniques in Excel would be tedious, time-consuming and open to errors. PivotTables ensure that you can perform a large number of calculations quickly, easily and accurately. They are dynamic tables of information that can be updated and modified with minimum effort.
PivotTable Theory
A PivotTable is created by nominating row fields, column fields, page fields and data items. The terminology is very similar to that used to describe data in a worksheet with multiple rows and columns. The sheer volume of data held in the list can mean that analysis by the usual techniques in Excel would be tedious, time-consuming and open to errors. PivotTables ensure that you can perform a large number of calculations quickly, easily and accurately. They are dynamic tables of information that can be updated and modified with minimum effort.
PivotTable Theory A PivotTable is created by nominating row fields, column fields, page fields and data items. The terminology is very similar to that used to describe databases, and in effect, a list in Excel can be considered a database. The following example shows how the different parts of the PivotTable are built from a list of data, or database, in or database, in Excel. If you accidentally drop a field in the wrong place, you can remove it by dragging it back down to the PivotTable Field List. Excel.
If you accidentally drop a field in the wrong place, you can remove it by dragging it back down to the PivotTable Field List.
To review the complete Excel workshop Outline containing Pivot Tables for 2003 :Click here.
To review the complete Excel workshop Outline containing Pivot Tables for 2007:Click here.
Excel AutoSum
Excel AutoSum a sum function for a contiguous range. If required, drag the pointer to select the right cells. http://buteltd.com/butetipster
Functions are pre-programmed formulas that are already provided for you in Excel. There are over 250 functions available to you covering a wide range of categories including statistics, date Functions are pre-programmed formulas that are already provided for you in Excel.
To review the complete Excel workshop containing Formulas and Function for 2003. Click on the following link: http://buteltd.com/Microsoft_Excel_2003_L1_Introduction
To review the complete Excel workshop containing Formulas and Functions for 2007. Click on the following link: http://buteltd.com/microsoft_excel_2007_level_one
Excel Chart
Excel Chart Wizard walks you through the steps to create a chart. Create one without the wizard, F11.
Chart Wizard The easiest way to create a chart is with the Chart Wizard. The Wizard walks you through the process, prompting you for the information that you need to create the chart. You can also create a chart without the wizard. To do this, select the data to be charted and then press F11.
Chart Wizard The Chart Wizard can be accessed using the Chart Wizard tool on the toolbar, or from the Chart command on the Insert menu. While it is not totally necessary, it’s better to select the cell range that contains the data. This makes it easier for the Wizard to create the chart, and also allows you to preview various chart types from within the Wizard using the actual data that will be plotted.
The easiest way to create a chart is with the Chart Wizard. The Wizard walks you through the process, prompting you for the information that you need to create the chart. You can also create a chart without the wizard. To do this, select the data to be charted and then press
To review the complete Excel workshops containing Charts for 2003. Click on the following link: http://buteltd.com/Microsoft_Excel_2003_L2_Intermediate
To review the complete Excel workshops containing Charts for 2003. Click on the following link: http://buteltd.com/microsoft_excel_2007_level_two