EXCEL tutorial data entry form


Télécharger EXCEL tutorial data entry form

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


"Microsoft Excel: Knowing the Screen Elements, Entering and Saving Your Spreadsheet Data"

Microsoft Excel is an advanced computer-based spreadsheet, which is used to store data in columns and rows which can then be organized and/or processed. Spreadsheet is simply a sheet that contains many columns and rows.

To get started, follow the guides below:

To start Excel using the Windows Start menu

 Click on the  Start  button, point to  Programs  , following by  Microsoft Office  and click on  Microsoft Office Excel 2003.

Note: For previous version of Office - Click on the Start button, point to Programs and click on Microsoft Excel.

 Immediately you will see the screen shown below. In the right hand side of the screen, the  Getting Started task pane provides help to you.

•     If you are not using it at the moment, click on the  Close   icon  to close it.

•     The  Microsoft Office   Excel 2003 screen elements   :

 

By default, Microsoft Excel workbook contains 3 blank worksheets, which are identified by tabs displaying along the bottom of the screen.

To enter text into a worksheet

•     Select the cell in which you want to enter the text and then type in the text.

•     Text entries are left aligned by default.

To enter numbers into a worksheet

•     Select the cell in which you want to enter a number and type in the number.

•     If you want to enter a negative number, type a minus sign in front of it or enclose it in  parentheses (bracket), e.g. -15 or (15).

•     To indicate decimal places, you type a full stop such as 125.89.

•     The numbers will be right aligned by default.

To save a workbook

•     From  File  menu, click  Save .

 

•     If it is a new file, the  Save As dialog box will appear. If necessary select a different folder that  you may wish to save the file in.

•     Enter a name in the  File name:  text box.

•     Click on the  Save  button.

To minimize a workbook window

               •      Click on the  Minimize  button in the top right-hand corner of the workbook window.

 

Note: If you minimize the window, the file is not close yet. To re-open the program, just click on the 'minimized program icon' on the window taskbar.

To close a file

•     From the  File  menu, click  Close .

•     You will be asked if you wish to save any changes you have made to the file. Select  Yes   to save, or No to ignore the changes.

•     If you are saving a new file, the  Save As dialog box will display. In the  File name  text box,  enter a name and click on the Save button.

To exit Microsoft Excel

•     From the  File  menu, click  Exit .

•     If you have saved all the changes made in the active workbook  files  , Microsoft Excel will  close.

•     If you have not saved all the changes, the  Save confirmation box will be displayed.

•     To save the current workbook file before exiting, select  Yes   .

•     To exit without saving the file, select   No .

•     To cancel the exit command, select  Cancel or press  Esc   .

Start Exploring Microsoft Excel 2003

Once the Microsoft Excel is launch, you can directly use it by entering data or you can choose to open the existing Excel file. Below are the step-by-step guides:

Creating and Opening Workbooks

To start using Microsoft Excel, you can start with a new blank workbook or open the existing Excel file.

To create a new default workbook

•   Click on the New icon located on the Standard toolbar

OR press Ctrl+N

To open a file

•   From the File menu, click Open.

•   From the Open dialog box as displayed, use the Look in: drop down menu to select the drive or folder that contains the file you want.

•   To open the file you can either double-click on the file name

OR select the file name by clicking on it, and then click on the Open button.

Microsoft Excel - Data Entry Techniques

Here is the guide that shows the easy ways that you can use to enter data into the worksheet.

To fill a range of cells with the same data ?  Highlight the cells you wish to fill.

•   Enter the information that you wish to fill the selected range with. ?  Press Ctrl+Shift+Enter and the range will be filled.

To fill a range of cells with the 'automatic' data ?  Highlight the cells you wish to fill.

•   Enter the data into the cells as follow: A1: 1; A2: 2 ?  Use the mouse to drag from cell A1 to A2.

•   Place your mouse in the bottom right corner of the selected cells until you see the “+” sign appear as follow:

•   Click and drag the mouse down the cells as you wish. You will see the selected range will fill with continuous numbers 3, 4, 5, 6,…

Note: You also can replace the above A1 and A2 cells with days of week such as Monday, Tuesday, and months like January, February,

Microsoft Excel - Navigating in the Worksheet


How you can move from one place to another in Microsoft Excel? Here is the ways: To move to a particular cell (quick way)

•   Enter the cell that you wish to jump to into the Name Box (at the top, left of the screen). In the example shown the cell reference J50 has been entered. When you press the Enter key you will jump to the cell you entered.

To move from cell to cell using the keyboard

•   To move from cell to cell, use one of the following key or combination keys:

Icon

Function

 

?

One cell to the right.

 

?

One cell to the left.

 

?

One cell down.

 

?

One cell up.

 

Ctrl + ?

To the right-hand edge of the current region.

 

Ctrl + ?

To the left-hand edge of the current region.

 

Ctrl + ?

To the bottom edge of the current region.

 

Ctrl + ?

To the top edge of the current region.

 

Home

To the first cell in the row.

 

Ctrl + Home

To the first cell in the worksheet.

 

Ctrl + End

To the lowest right-hand cell in the worksheet that contains a data entry.

 
 
   

Page Down

One screen down.

 

Page Up

One screen up.

 

Alt + Page Down

One screen to the right.

 

Alt + Page Up

One screen to the left.

 

Microsoft Excel - Selection Techniques

The selection techniques in Microsoft Excel allow you to select or highlight the cell(s) quickly so that you can perform the tasks such as deleting, copying, etc. To select a cell

•   Click on the cell you wish to select.

To select a range of cells by dragging the mouse ?  Click on the first cell in the range.

•   Hold down the left-hand mouse button and drag over the cells you wish to include in the selection.

To select a range of cells (making up a rectangular block)

•   Click on the first cell of the rectangular block that you wish to select (i.e. the left top-left hand corner)

•   Move down to the cell that marks the bottom-right corner of the rectangular block.

•   Depress the Shift key (and keep it depress).

•   Click once on the last cell of the required block and release the Shift key.

To select a non-contiguous range

•   Select the first cell or range.

•   While holding down the Ctrl key, select the next range of cells.

To select a row

•   Click on the row heading number.

To select a column

•   Click on the column heading letter.

To select an entire worksheet

•   Press Ctrl+A

Microsoft Excel - Inserting and Deleting

Microsoft Excel offers a convenient way for you to either insert or delete cell(s), column(s), row(s), or entire worksheet.

To insert a row(s) into a worksheet

•   Select the row you want to move down when the new row is inserted above it. ?  To select more than one row, drag the mouse pointer across the required row headings.

•   Right-click on the selected row(s) to display a pop-up menu.

•   Select Insert. Any existing data will move down to accommodate the new cells.

 

To insert column(s) into a worksheet

•   Select the column you want to move to the right when the new column is inserted. ?  To select more than one column, drag the mouse pointer across the required column headings.

•   Right-click on the selected column(s) to display a pop-up menu.

•   Select Insert. Any existing data will move down to accommodate the new cells.

To delete a row or column

•   Use the same techniques outlined above, but select the Delete command from the pop-up menu.

To insert cells or range of cells

•   Select the cell where you want to insert a new cell.

•   From the Insert menu, click Cells.

•   From the Insert dialog box, select one of the following options:

Entire column       Moves all cells in the column to the right and inserts a new column.

Entire row

Moves all cells in the row down and inserts a new row.

Shift cells down

Shift cells right

Moves existing cells down and inserts one cell.

Moves existing cells to the right and inserts one cell.

•   Click on the OK.

To delete cells or ranges of cells ?  Select the cell(s) you want to delete.

•   From the Edit menu, click Delete.

•   From the Delete dialog box displayed, select one of the options. ?  Click on the OK button.

To insert a worksheet

•   Click the sheet tab to select the worksheet where you want to insert a new one. The new worksheet will be inserted before this worksheet. ?  From the Insert menu, click Worksheet.

To delete a worksheet

•   Click on the sheet tab to select the worksheet you wish to delete.

•   From the Edit menu, click Delete Sheet.

•   Select OK to permanently delete the selected sheet.


COPY and PASTE

What is the Mystery Concept behind the Copy and Paste?

Yes, it’s clipboard.

and…What is the relationship between clipboard and copy and paste?

The clipboard is an area of memory in which you can store text, graphics or any other items in it.

When you perform copy in the Excel spreadsheet, actually the copied item is store in the clipboard temporarily, and then when you paste the item, it is from the clipboard and paste the item to the intended location.

Cut, Copy and Paste Data or Objects

To use the clipboard to copy data to other program ?  Select the cell or range you wish to copy.

•   From the Edit menu, click Copy.

OR click on the Copy icon on the Standard toolbar.

•   Switch to the required destination program (e.g. Microsoft Word).

•   Place the cursor where you want the data to appear.

•   From the Edit menu, click Paste.

OR click on the Paste icon on the Standard toolbar.

To use the clipboard to cut data to other program ?  Select the cell or range you wish to cut.

•   From the Edit menu, click Cut.

OR click on the Cut icon on the Standard toolbar.

•   Switch to the required destination program (e.g. Microsoft Word).

•   Place the cursor where you want the data to appear.

•   From the Edit menu, click Paste.

OR click on the Paste icon on the Standard toolbar.

Note: You noticed that cut and paste is different from copy and paste. Cut and paste will let the original data disappear when you paste it to the destination program/location.

To use the clipboard to copy an object between worksheet or workbook

•   Click on the object (e.g. oval) to select it.

•   From the Edit menu, click Copy.

•   Move to a new worksheet or workbook.

•   Select a location for the top left-hand corner of the object. ?  From the Edit menu, click Paste.

To use the clipboard to cut an object between worksheet or workbook ?  Click on the object (e.g. arrow) to select it.

•   From the Edit menu, click Cut.

•   Move to a new worksheet or workbook.

•   Select a location for the top left-hand corner of the object.

•   From the Edit menu, click Paste.

To copy a numeric value down a column

•   In the first cell of the column, enter the first numeric value.

•   Select the second cell in the same column and press Ctrl+Shift+’ (Apostrophe). ?  Press Enter to insert the value into the selected cell.

To copy a column of text into a row

•   Select the range of data you wish to copy and transpose.

•   From the Edit menu, click Copy.

•   Highlight the cell where you want to place the range.

•   From the Edit menu, click Paste Special.

•   From the Paste Special dialog box displayed, tick the Transpose check box. ?  Select OK.

EXCEL FORMATTING

There is So Many Things to do With Excel Formatting?

The Excel Formatting Toolbar

Excel formatting toolbar normally located near to the top of your screen as shown here. The toolbar icons have a specific purpose that can change the outlook of your spreadsheet when you apply formatting to it.

 

The formatting toolbar icons and functions:

•   Font – to select fonts from a drop-down list.

•   Font Size – to select a font size from a drop-down list.

•   Bold – to apply bold to a selected range.

•   Italic – to apply italic to a selected range.

•   Underline – to underline a selected range.

•   Align Left – to align a selected range to the left.

•   Center – to center a selected range

•   Align Right – to align a selected range to the right.

•   Merge and Center – to merge cells and center text across a selected range.

•   Currency Style – to apply currency style to a selected range.

•   Percent Style – to apply percentage style to a selected range.

•   Comma Style – to apply comma style to a selected range.

•   Increase Decimal – to decrease the number of decimal points displayed in a selected range.

•   Decrease Decimal – to increase the number of decimal points displayed in a selected range.

•   Decrease Indent – to decrease the level of indentation in a selected range.

•   Increase Indent – to increase the level of indentation in a selected range.

•   Borders – to select and apply borders to a selected range.

•   Fill Color – to select and apply color to a selected range.

•   Font Color – to select and apply color to text in a selected range.

The Excel Formatting - Fonts

To change the font used in a cell or range of cells ? Select the cell or range of cells you wish to change.

•   On the Excel Formatting toolbar choose a font from the Font drop down list.

To change other font characteristics ?  Select the cell or range you wish to change.

•   From the Format menu, click Cells.

•   From the Format Cells dialog box displayed, select the Font tab to change the Font, font style, or size.

•   Select the Underline: drop down list box to select a style of underline or None to remove the underlining.

•   Select the Color: drop down list box to choose a font color.

•   In the Effects section, you can tick Strikethrough, Superscript, or Subscript. ?  To change all the settings in the dialog box back to the Normal style which is set in the Style dialog box, tick the Normal font check box.

•   Select OK to close the Format Cells dialog box.

The Excel Formatting - Alignment

To align data between the left and right sides of a cell ?  Select the cell, or cells, you wish to align.

•   On the Excel Formatting toolbar, click on the Align Left icon to align data with the left edge of the cell.

•   Click on the Align Right icon to align data with the right edge of the cell. ?  Click on the Center icon to center data in the cell.

To align data between the top and bottom of a cell ?  Select the cell, or cells, you wish to align.

•   From the Format menu, click Cells.

•   From the Format Cells dialog box displayed, click on the Alignment tab.

•   In the Text alignment section, choose the Top, Center, or Bottom option in the Vertical area to align the data in the cell.

•   To make the lines of data fit evenly within the height of a cell, tick the Justify distributed option and click on OK.

To change the orientation of data cells ?  Select the cell, or cells, you wish to change.

•   From the Format menu, click Cells.

•   From the Format Cells dialog box displayed, click on the Alignment tab.

•   In the Orientation section, you can change the Degrees of the text or use the mouse to drag the 'red small diamond' up or down. When satisfied, click on OK.

To wrap multiple lines of data in a cell

•   Type the data you require into the cell and press Enter. The entry will appear as one long line and select the cell, or cells, you wish to format.

•   From the Format menu, click Cells.

•   From the Format Cells dialog box displayed, click on the Alignment tab.

•   In the Text control section, tick the Wrap text check box and click on OK.

To shrink the text into one cell

•   Type the data you require into the cell and press Enter.

•   From the Format menu, click Cells.

•   From the Format Cells dialog box displayed, click on the Alignment tab.

•   In the Text control section, tick the Shrink to fit check box and click on OK.

To merge several cells

•   Select the cells that you wish to merge to become one cell.

•   Right-click on the selected cells, click Format Cells.

•   From the Format Cells dialog box displayed, click on the Alignment tab.

•   In the Text control section, tick the Merge cells check box and click on OK.

Note: You also can use the Merge and Center icon on the Excel Formatting toolbar.

The Excel Formatting - Colors

To change the color of text

•   Select the cells containing the data you want to change the color.

•   On the Excel Formatting toolbar, click the down arrow next to the Font Color icon.

•   Choose a color from the Font Color palette.

•   The color you selected from the Font Color palette will be displayed on the new icon face. Click on the icon face to apply the new color to the cell.

To apply colors to cell(s)

•   Select the cell or range of cells you wish to color.

•   On the Excel Formatting toolbar, click the down arrow next to the Fill Color icon. ?  Choose a color from the Fill Color palette.

To turn off the cell(s) color

•   Select the cell or range of cells you wish to color.

•   On the Excel Formatting toolbar, click the down arrow next to the Fill Color icon. ?  Choose No fill.

The Excel Formatting - Numbers

To change number formatting using the formatting icons

•   You can quickly change the formatting of a cell or selected range by using the following icons on the Formatting toolbar.

 

Currency

12345 will become $12,345.00

Percent

.25 will become 25%

Comma

98765 will become 98,765.00

Increase Decimal 12,345.00 will become 12,345.000

Decrease Decimal

98,765.00 will become 98,765.0

To format a number as a currency

•   Select the cell or range of cells you want to format.

•   From the Format menu, click Cells.

•   From the Format Cells dialog box displayed, select the Number tab. ?  In the Category: section, select Currency.

 

•   Select the number of decimal places you require by using the Decimal places: spin box arrows.

•   In the Symbol: section drop down list, select the type of currency. ?  Click OK.

To format a number as a percentage ?  Select the cell or range of cells you wish to format.

•   From the Format menu, click Cells.

•   From the Format Cells dialog box displayed, select the Number tab.

•   In the Category: section, select Percentage.

•   Select the number of decimal places you require by using the Decimal places: spin box arrows.

•   Click OK.

Excel Formula and Function

Excel Formula

A formula is an equation that performs operations on worksheet data. Excel formula can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text.

To enter a formula

•   Place the cursor in the cell where the formula will appear.

•   Enter an = sign.

•   Enter the expression that will produce the result you want, e.g. A5+E5. This will add the value in A5 with the value in E5.

•   When the formula is complete, press Enter. The result of the formula will be calculated and displayed in the cell.

•   If there is an error in an Excel formula, an error message which begin with a # sign will display.

To enter a cell or range reference by pointing

•   Place the cursor in the cell where the formula will appear.

•   Enter the formula up to the point of the cell or range reference, e.g. to enter the formula =E2+E5, only enter the = sign.

•   Using the arrow keys, move the cell pointer to the first cell reference, in this case E2. The formula will track your progress and enter the current address into the formula.

•   Enter the operand, + sign.

•   Using the arrow keys, move the cell pointer to the second cell reference, in this case E5. If you are calculating a range of cells, hold down the Shift key while using the arrow keys to move to the intended cells.

•   Press Enter to complete the formula when you have reached the cell you require.

Excel Function

Functions are special commands used in formulas to perform mathematical processes.

To enter functions directly into the worksheet cell ? Select the cell into which the formula will be entered.

•   Insert an equal (=) sign to begin the formula. The formula toolbar buttons will appear.

•   Enter the name of the function [e.g. SUM], followed by an opening parenthesis [(], any arguments required for the function [e.g. E2:E5], and closing parenthesis[)]. ? Press Enter. If there are no errors in the formula, the result of the function will be entered in the cell. If you activate the cell again, the function will be displayed in the formula bar.

To use the AutoSum function

•   The functions can be accessed through the AutoSum icon on the Standard toolbar. ? The functions included in the AutoSum drop-down menu will insert the function and predict the arguments.

•   For example, if the active cell is positioned at the bottom of the list of values, AutoSum will display a sum function with the list of the arguments.

•   Sum -- add the contents of the list of arguments.

•   Average -- determine the average value of the list of arguments.

•   Count -- count the number of values in the list of arguments. ?  Max -- return the maximum number in the list of arguments.

•   Min -- return the minimum number in the list of arguments

Example: Using the Average function

•   Enter the values as shown below from B1 to B5 and select the

cell B6 which the formula will be entered.

 

•   Click the down arrow beside the AutoSum icon on the Standard toolbar and choose Average from the drop-down menu.

•   If the predicted range is correct, press the Enter key. If it is incorrect, select (click and drag) the range you want with the mouse and press the Enter key.

•   The result will show in cell B6. You can use the same method to do other functions such as SUM, Max, etc.

Note: Excel formula and function is one of the most powerful feature in Microsoft Excel.

It is important for you to master it.

Create an Excel Chart: The 4 Easy Steps!

Here is the step-by-step guides to create an outstanding Excel chart:

Key in the data illustrated below accordingly as we will use it to demonstrate how to create a chart in Microsoft Office Excel.

Note:

We will use the step-by-step Excel Chart Wizard to create a chart that related to the Student exam results for the subject English, Science and Mathematics.

 

Step 1: The Chart Wizard (1) – Chart Type

•   Click on any cell within the data containing the information that you wish to display as a chart, or highlight the exact data that you wish to display as a chart.

•   Click the Chart Wizard icon on the Standard toolbar. This will display the Excel Chart Wizard as show below:

 

•   From the Chart type: section you can select the type of chart that you wish to create.

•   Then from the Chart sub-type: section you can select the exact format that you required for the selected chart type.

•   To see how the selected chart will look, use the Press and Hold to View Sample button within the dialog box. In our example, we accept the default selection. ?  Click the Next button to see the next page of the dialog box - Chart Source Data.

Step 2: The Excel Chart Wizard (2) – Data Source

•   The Data Range tab allows you to specify the exact data that you wish to display within your chart.

•   You can choose to display Series in Rows or Columns. In the case of the example data used, the two effects will be as illustrated. Choose the Series in Rows.

 

•   When you click on the Next button, this will display the Chart Options dialog box.

Step 3: The Excel Chart Wizard (3) – Chart Options

From the chart options dialog box displayed, you can select Titles, Axes, Gridlines, Legend, Data Labels and Data Table tabs and make the necessary changes.

To add title to a chart

•   In the Chart title: text box, enter the name for the chart, i.e. Exam results. ?  In the Category (X) axis: text box, enter a title for the X axis, i.e. Subject.

•   In the Category (Y) axis: text box, enter a title for the Y axis, i.e. Mark score.

•   In the example used, the screen will be as illustrated.

 

To customize the chart axis

•   From the Chart Option dialog box displayed, click on the Axes tab.

•   It allows you to control the way the axes are displayed, you can tick or un-tick the check box to see the effects on the chart.

To customize chart gridlines

•   From the Chart Option dialog box displayed, click on the Gridlines tab.

•   You can choose to display the major and minor X and Y gridlines for the axis by clicking on the particular check box.

To customize chart legend

•   From the Chart Option dialog box displayed, click on the Legend tab.

•   You can choose to display or not the chart legend and the placement of the legend in the chart by clicking on the radio button.

To customize data labels

•   From the Chart Option dialog box displayed, click on the Data Labels tab.

•   You can choose to display or not the chart data labels by clicking on the radio button.

To display a data table

•   From the Chart Option dialog box displayed, click on the Data Table tab.

•   You can choose to display or not the chart data table by ticking on the check box. ?  Click on the Next button to continue and this will display the final page of the Chart Wizard – Chart Location.

Step 4: The Excel Chart Wizard (4) – Chart Location

To define the chart location

•   You can choose to place the chart on an existing worksheet as an object, or you can place it on a new worksheet. Two options for you to choose:

As new sheet: – It will place the chart in the new worksheet.

As object in: - It will place the chart in the existing worksheet.

 

•   Click on the Finish button and the chart will be created as you have specified during the Chart Wizard creation process.

Create an Excel Chart: The 4 Easy Steps!

Here is the step-by-step guides to create an outstanding Excel chart:

Key in the data illustrated below accordingly as we will use it to demonstrate how to create a chart in Microsoft Office Excel.

Note:

We will use the step-by-step Excel Chart Wizard to create a chart that related to the Student exam results for the subject English, Science and Mathematics.

 

Step 1: The Chart Wizard (1) – Chart Type

•   Click on any cell within the data containing the information that you wish to display as a chart, or highlight the exact data that you wish to display as a chart.

•   Click the Chart Wizard icon on the Standard toolbar. This will display the Excel Chart Wizard as show below:

 

•   From the Chart type: section you can select the type of chart that you wish to create.

•   Then from the Chart sub-type: section you can select the exact format that you required for the selected chart type.

•   To see how the selected chart will look, use the Press and Hold to View Sample button within the dialog box. In our example, we accept the default selection. ?  Click the Next button to see the next page of the dialog box - Chart Source Data.

Step 2: The Excel Chart Wizard (2) – Data Source

•   The Data Range tab allows you to specify the exact data that you wish to display within your chart.

•   You can choose to display Series in Rows or Columns. In the case of the example data used, the two effects will be as illustrated. Choose the Series in Rows.

 

•   When you click on the Next button, this will display the Chart Options dialog box.

Step 3: The Excel Chart Wizard (3) – Chart Options

From the chart options dialog box displayed, you can select Titles, Axes, Gridlines, Legend, Data Labels and Data Table tabs and make the necessary changes.

To add title to a chart

•   In the Chart title: text box, enter the name for the chart, i.e. Exam results. ?  In the Category (X) axis: text box, enter a title for the X axis, i.e. Subject.

•   In the Category (Y) axis: text box, enter a title for the Y axis, i.e. Mark score.

•   In the example used, the screen will be as illustrated.

 

To customize the chart axis

•   From the Chart Option dialog box displayed, click on the Axes tab.

•   It allows you to control the way the axes are displayed, you can tick or un-tick the check box to see the effects on the chart.

To customize chart gridlines

•   From the Chart Option dialog box displayed, click on the Gridlines tab.

•   You can choose to display the major and minor X and Y gridlines for the axis by clicking on the particular check box.

To customize chart legend

•   From the Chart Option dialog box displayed, click on the Legend tab.

•   You can choose to display or not the chart legend and the placement of the legend in the chart by clicking on the radio button.

To customize data labels

•   From the Chart Option dialog box displayed, click on the Data Labels tab.

•   You can choose to display or not the chart data labels by clicking on the radio button.

To display a data table

•   From the Chart Option dialog box displayed, click on the Data Table tab.

•   You can choose to display or not the chart data table by ticking on the check box. ?  Click on the Next button to continue and this will display the final page of the Chart Wizard – Chart Location.

Step 4: The Excel Chart Wizard (4) – Chart Location

To define the chart location

•   You can choose to place the chart on an existing worksheet as an object, or you can place it on a new worksheet. Two options for you to choose:

As new sheet: – It will place the chart in the new worksheet.

As object in: - It will place the chart in the existing worksheet.

 

•   Click on the Finish button and the chart will be created as you have specified during the Chart Wizard creation process.

Excel Template – How to Create and Use it?

Here is your definite solution for your Excel template questions!!

When working with spreadsheets you may want to use the same layout or design. You can re-create the design every time you want to use it, or you can create a template.

Yes, it's Excel template

You can create templates for workbooks and worksheets. The default template for workbooks is called . The default template for worksheets is called .

Creating templates can include the following elements:

•   Text and graphics

•   Layouts and styles formatting

•   Headers and Footers

•   Formulas

•   Macros

To create a template

•   Create the workbook that contains all layout and formatting elements you wish to save as a template.

•   From the File menu, click Save As.

•   Type the name for the template in the File name: text box.

•   From the Save as type: drop down list box, select Template.

 

•   Select Save. The extension .XLT is added to the file name and the template is saved in the Templates folder.

To use a template

•   From the File menu, click New to display the New Workbook pane.

•   From the Template section of the New Workbook pane, click on the blue On my computer… link display the Templates dialog box:

 

•   Click on the General or the Spreadsheet Solutions tab to locate the template you want. For default template, click on the General tab and select the Workbook icon.

Note: In the Spreadsheet Solutions tab, you can see 5 templates available. Most of the times you need to use the Microsoft Office CD in order to install the features and use it.

•   Click OK to open a copy of the template.

Note: Excel 2003 allows you to access additional templates on the Microsoft Office website (required Internet connection). Just click on the Templates on Office Online link in the New Workbook pane, and you will be directed to the website and search for the template that you need.

To download different types of free excel templates,

Template vs Style

What is the different between Excel template and Excel style?

Style is a collection of cell formatting information such as font size, patterns, alignment, etc that you can define and save as a group.

If formatting information is assigned to cells using styles it is possible to easily update the appearance of a sheet by modifying the styles.

Excel comes with a number of styles pre-defined, by default all cells have the Normal style assigned to them. It is possible to copy styles from one Workbook to another.

To create a style

•   Select a cell formatted with the attributes you required.

•   From the Format menu, click Style.

 

•   From the Style dialog box displayed, type the style name in the Style name: drop down list.

•   Select the Modify button to change any of the attributes. The Format Cells dialog box will be displayed.

•   Click on the Number, Alignment, Font, Border, Patterns and Protection tabs and make any changes required. Click OK to return to the Style dialog box. ? Click OK.

To apply a style

•   Select the range of cells that you wish to format.

•   From the Format menu, click Style.

•   Select the style from the Style name: drop down list box.

•   Select OK.

Excel Macro – Make Your Complex Tasks Automated

The Excel Macro…What is that?

•   A macro is a series of commands and functions that can be run whenever you need to perform the task.

•   Macros can automate complex tasks and reduce the number of steps required to complete tasks that you perform frequently.

•   The easiest way to create an Excel macro is to have Excel records the actions and then store these actions as a macro.

•   Macros can be played back or modified at any time.

To record a macro

•   From the Tools menu, point to Macro and click on Record New Macro.

•   From the Record Macro dialog box displayed, enter a name for the macro in the Macro name: text box.

 

•   The default description is displayed in the Description: text box, and contains the date and user name. If required, change it.

•   To begin recording, click OK.

•   Perform the actions you want the macro to record. Actions can be any combinations of Excel commands.

•   To stop recording, from the Tools menu, point to Macro and click on Stop Recording.

To assign a shortcut key to the macro

•   From the Tools menu, point to Macro and click on Macros.

•   From the Macro dialog box displayed, select the macro name (if recorded before) to which you want to assign a shortcut key.

 

•   Click the Options button to display the Macro Options dialog box.

•   Type a letter into the Ctrl+ text box. This combination key will be used to invoke the macro.

•   Click OK to return to the Macro dialog box. ?  Click on the Cancel button to close.

To run a macro using the Tools Macro command

•   From the Tools menu, point to Macro and click on Macros.

•   From the Macro dialog box displayed, click on the name of the macro you wish to run in the Macro name: list box.

•   Click the Run : If you wish to edit or delete a macro, just click on the particular button.

To run a macro using the assigned shortcut key ?  Press Ctrl+?, with ? is the letter which you assigned.

To create a button and assign an Excel macro to it

•   If the Forms toolbar is not display, right click on any toolbar that is visible and from the pop-up menu that is displayed select Forms.

•   To create a button, click the Button icon on the Forms toolbar.

 

•   Then, click and drag the mouse to the intended button size.

•   When release the mouse button, the Assign Macro dialog box will be displayed.

 

•   Click on the Record button and the Record Macro dialog box will be displayed.

•   Enter a name for the macro in the Macro name: text box.

•   Click on the OK button.

•   Record your macro in the normal way, and click on the button and select Edit Text from the pop-up menu.

•   To run the macro, simply click on the button.

To change a button name

•   Double click on the button to highlight the button name.

•   Type in a new name and click on any worksheet cell to deselect the button.

To delete a button

•   Click on button and press the Delete key.

Excel 2003 Worksheet and Workbook Protection

Excel worksheet and workbook protection feature allows you to protect your file being open by unauthorised person. You can set the password so that anyone that intend to open the file required the password to open it.

To specify a password for opening and modifying a workbook ?  From the File menu, click Save As.

•   From the Save As dialog box displayed, click on the Tools icon and from the drop down list displayed, select General Options.

 

•   From the Save Options dialog box displayed, enter a password into the Password to open: text box. In future you will be required to enter this password in order to open the file.

 

•   If you enter a password into the Password to modify: text box, this gives others the ability to open, view and edit a workbook, but not to save it with the same name. They must save a modified version of the workbook with a different name.

•   Click on the OK button. You will be asked to re-type the password to ensure that it is consistent.

Note:

(1)   The check box "Always create backup" forces Excel to create a backup copy of the file every time a worksheet file is saved. The file extension BAK is used, and the backup file is saved in the same folder as the original file.

(2)   If the "Read-only recommended" check box is ticked, the following dialog box is displayed when the file is re-opened.

 

•   Click Yes.

•   The file is reopen but with the feature ‘read-only’.

•   If you needed to make changes on the worksheet, you cannot save the file with the same file name. So, changing the file name allows you to save the file.

To remove a password from a workbook

•   Open the workbook containing the password you wish to remove (by entering the password when prompted).

•   From the File menu, click Save As.

•   From the Save As dialog box displayed, click on the Tools icon, and from the drop down list displayed select General Options.

•   From the Save Options dialog box displayed, clear either or both passwords text box.

•   Click on the OK button to close the Save Options dialog box.

•   Click on the Save button to save the file, which will display a dialog box. ?  Click on the Yes button.

To protect a workbook

•   From the Tools menu, point to Protection and click on Protect Workbook. ?  This will display the Protect Workbook dialog box.

 

•   From the Protect Workbook dialog box displayed, you have two options:

Structure

Prevents any changes to the structure of the workbook if checked. i.e. you will be unable to edit, insert, delete, rename, copy, move or hide sheets.

 

Windows

Prevents changes to the workbooks size. The windows re-sizing controls (close, maximize, minimize and restore) are hidden.

•   In the Password column, you can enter a password (case sensitive). Click OK. ?  You will be asked to re-type the password to ensure that it is consistent. ?  Click OK.

To unprotect a workbook

•   From the Tools menu, point to Protection and click on Unprotect Workbook. ?  Enter the required password and click on OK.

To protect a worksheet

•   From the Tools menu, point to Protection and click on Protect Sheet. ?  This will displays the Protect Sheet dialog box.

 

•   From the Protect Sheet dialog box displayed, you can choose any option from the lists if you ALLOW other users to modify the checked option in the worksheet.

•   In the Password to unprotect sheet: column, you can enter a password (case sensitive). Click OK.

•   You will be asked to re-type the password to ensure that it is consistent. ?  Click OK.

To unprotect a worksheet

•   From the Tools menu, point to Protection and click on Unprotect Worksheet.

•   Enter the required password and click on OK.

Excel 2003 Worksheet and Workbook Protection

Excel worksheet and workbook protection feature allows you to protect your file being open by unauthorised person. You can set the password so that anyone that intend to open the file required the password to open it.

To specify a password for opening and modifying a workbook ?  From the File menu, click Save As.

•      From the Save As dialog box displayed, click on the Tools icon and from the drop down list displayed, select General Options.

 

•      From the Save Options dialog box displayed, enter a password into the Password to open: text box. In future you will be required to enter this password in order to open the file.

 

•      If you enter a password into the Password to modify: text box, this gives others the ability to open, view and edit a workbook, but not to save it with the same name. They must save a modified version of the workbook with a different name.

•      Click on the OK button. You will be asked to re-type the password to ensure that it is consistent.

Note:

(1)   The check box "Always create backup" forces Excel to create a backup copy of the file every time a worksheet file is saved. The file extension BAK is used, and the backup file is saved in the same folder as the original file.

(2)   If the "Read-only recommended" check box is ticked, the following dialog box is displayed when the file is re-opened.

 

•   Click Yes.

•   The file is reopen but with the feature ‘read-only’.

•   If you needed to make changes on the worksheet, you cannot save the file with the same file name. So, changing the file name allows you to save the file.

To remove a password from a workbook

•   Open the workbook containing the password you wish to remove (by entering the password when prompted).

•   From the File menu, click Save As.

•   From the Save As dialog box displayed, click on the Tools icon, and from the drop down list displayed select General Options.

•   From the Save Options dialog box displayed, clear either or both passwords text box.

•   Click on the OK button to close the Save Options dialog box.

•   Click on the Save button to save the file, which will display a dialog box. ?  Click on the Yes button.

To protect a workbook

•   From the Tools menu, point to Protection and click on Protect Workbook. ?  This will display the Protect Workbook dialog box.

 

•   From the Protect Workbook dialog box displayed, you have two options:

Structure

Prevents any changes to the structure of the workbook if checked. i.e. you will be unable to edit, insert, delete, rename, copy, move or hide sheets.

 

Windows

Prevents changes to the workbooks size. The windows re-sizing controls (close, maximize, minimize and restore) are hidden.

•   In the Password column, you can enter a password (case sensitive). Click OK. ?  You will be asked to re-type the password to ensure that it is consistent. ?  Click OK.

To unprotect a workbook

•   From the Tools menu, point to Protection and click on Unprotect Workbook. ?  Enter the required password and click on OK.

To protect a worksheet

•   From the Tools menu, point to Protection and click on Protect Sheet. ?  This will displays the Protect Sheet dialog box.

 

•   From the Protect Sheet dialog box displayed, you can choose any option from the lists if you ALLOW other users to modify the checked option in the worksheet.

•   In the Password to unprotect sheet: column, you can enter a password (case sensitive). Click OK.

•   You will be asked to re-type the password to ensure that it is consistent. ?  Click OK.

To unprotect a worksheet

•   From the Tools menu, point to Protection and click on Unprotect Worksheet.

•   Enter the required password and click on OK.

Step-by-step Guides to Excel Printing!

Excel Printing or how to print the Excel spreadsheet?

In order to print, Microsoft Excel requires that a printer driver has been installed that matches the printer you are currently attached to your computer.

If you are sure on that, then ON the printer and load some plain papers to the printer tray.

Then, follow the step-by-step instructions below to print.

Excel Printing - Page Settings

The options that can change here includes the paper orientation, set the page scale and size, and print quality.



To change Page Setup options

•   From the File menu, click Page Setup.

•   From the Page Setup dialog box displayed, click on the Page tab.

 

To set the printing paper orientation

•   In the Orientation section, select Portrait or Landscape by clicking the option button.

To set the scale of the page

•   In the Scaling section, choose the Adjust to: option button and enter a percentage to scale (in conjunction with normal size) in the Adjust to spin box.

•   Choose the Fit to: option and enter the dimensions of the pages in the wide and tall spin boxes.

To set other printing options

Paper size

In the Paper size: drop-down list box, select the size you require.

Print quality

In the Print quality: drop-down list box, choose the quality you require (higher dpi – better quality).

Page numbering

To begin page numbering with a different number, select the First page number: text box and enter the number you want to use.

Excel Printing - Margins Settings

Proper Microsoft Excel printing that involves margins is to set the top, bottom, left and right margins of the page. You also can choose to center the data of a page. To change the page margins

•   From the Page Setup dialog box displayed, select the Margins tab.

 

•   Click on the Top, Bottom, Left, or Right margin spin box to change the settings.

•   To change header and footer margins, click on the Header: or Footer: spin box. ? Click OK.Note: At the bottom Center on page section, you can choose Horizontally or Vertically to reflect the best position of your data in a page.

Excel Printing - Header/Footer Settings

Proper settings of page header and footer are important as it will affect the overall look of a page in Excel printing.

To change headers and footers

•   From the Page Setup dialog box displayed, select the Header/Footer tab. ? Click on the down arrow to the right of the Header: list box to reveal a list of available headers. Click on the header required to select it.

•   Click on the Custom Header or Custom Footer button to display the Header (or Footer) dialog box.

 

•   In the Left section: box, enter any data you want to appear at the left margin of the header or footer.

•   In the Center section: box, enter any data you want to appear at the center of the header or footer.

•   In the Right section: box, enter any data you want to appear at the right margin of the header or footer.

•   You also offered the following options:

 

Format font

Click this button after highlighting the text to change the font, size, and style.

Page number

Insert the page number of each page.

Number      of

pages

Use this feature along with the page number to create strings such as "page 1 of 15".

Date

Add the current date.

Time

Add the current time.

File path

Add the file path name (location of the file)

File name

Add the name of the workbook file.

Tab name

Add the name of the worksheet’s tab.

Insert picture

Bring up insert picture window and you can choose a location to insert picture.

Format picture

Format the inserted picture.

•   When you have finished, click on OK.

•   Your new header (or footer) will be displayed in the Page Setup dialog box in the Header or Footer list box.

•   Click OK to close the Page Setup dialog box.

Excel Printing - Sheet Settings

In Excel printing, sheet settings is very important as it will control what and where to print of a page. So make sure you follow the steps here closely. To change sheet options

•   From the File menu, click Page Setup.

•   From the Page Setup dialog box displayed, select the Sheet tab.

 

•   Make changes to any of the following:

Print area

Enter the worksheet range you want to print, or click on the icon in the right of the text box and drag through the worksheet areas you wish to print.

Rows to repeat at top

Click on the icon in the right of the text box and drag over the rows you wish to repeat at the top of the page. It enables you to print the selected top row appear on every printing sheet.

Columns     to

repeat at left

Click on the icon in the right of the text box and drag over the columns

you wish to repeat at the left of the page. It enables you to print the selected left columns appear on every printing sheet.

Elements that

will print

Tick on the element you wish to print, i.e. Gridlines, Black and White, Draft Quality, Row and Column Headings (see below).

Page order

Select Down, then over, or Over, then down. You only can see the effect if you have data across a sheet that cannot fit into one printing page.

•   More on printing elements (when you tick on the following option):

Gridlines

Will print the Excel gridlines.

Black and white

Will print the data in blank and white without color.

Draft quality

The printing will be in draft quality, not the normal quality.

     

Row     and

headings

column

Will print the worksheet including the row and column headings.

•   In the right hand side, there are three buttons:

Options

Do necessary adjustment with the printer settings.

Print Preview

Preview your worksheet before printing.

Print

Print the worksheet.

To change the Excel printing settings ?  From the File menu, click Print.

•   From the Print dialog box displayed, do the necessary changes as follow:

 

•   From the Printer section, the Name: list box, choose the printer you wish to use. ?  In the Page range section, select whether you want to print All pages in a range, or enter the starting and ending page numbers in the From: and To: spin boxes.

•   Select what you want to print in the Print what section, i.e. Selection, Active sheet(s), or Entire workbook.

•   Specify the number of copies you want to print in the Number of copies: spin box.

•   Click the Properties button to display the Properties dialog box for the printer. ? Change the options required. The options will vary depending on what type of printer you have.

•   Click OK to close the printer properties dialog box. ?  Click OK again to start printing the worksheet.

Note: Before hit the OK button, ensure that you have put the plain papers to the printer tray.

"How to Solve Your Problem with Excel Help Feature?"

Having trouble using Excel? Try the Microsoft Excel Help feature!

With this feature, you can almost solve all Excel related problem. It is also one of the most important features whereby you can discover the Excel rich features from the basic to advance.

So, just follow the step-by-step guide here.

Note: It’s recommended that you connect to the Internet to use the Microsoft Excel 2003 Help feature.

To start the Microsoft Excel Help

•   Click on the Microsoft Excel Help icon located on the Standard toolbar.

 

•   Then you will see the Excel Help task pane display at the right hand side of your Excel screen.

•   Type in the relevant text that you would like to get help in the Search for: text box.

 

•   For example, we type in 'excel chart' and press Enter. Then, the search results are displayed, normally with 20 results as shown here.

 

•   Click on the particular topic, let say we would like to learn how to create a chart in Excel, then click on the Create a chart.

•   This will bring you to another window that guides you to create a chart. There are 2 steps to create a chart.

 

•   You can click on the blue color link to get more information. When finish, click on the Close button. This will bring you back to the Search Results pane.

•   To go back search for other topic, just click on the Back button from the Search Results pane. You also can use the Forward button.

 

How to Solve Your Problem with Microsoft Office Assistant?

                                                                    Yes, it’s actually the Microsoft Office Assistant feature in Excel.

By default this friendly little creature will watch what you do and offer tips on how to work more productively.

Occasionally the Office Assistant will display information on the screen. If you are unsure about how to use this feature you should always read the help offered.

Note: You are required to connect to the internet when you use the Office Assistant feature.

To show the Office Assistant

•   From the Excel Help menu, click on the Show the Office Assistant.

Note: The Office Assistant feature required you to put in the Microsoft Office 2003 installation CD to install it first only that you can use it.

•   This will display the Office Assistant on the screen and you can move it to any place by clicking and drag to the intended location.

 

To use the Office Assistant

•   Once you click on the Office Assistant, it will show something like this:

 

•   You see the highlighted texts that say ‘Type your question here and then click Search’.

•   Type in any question or term that you wish to search such as ‘toolbar’. It can be one word or combination of few words. Click the Search button.

•   You will see something like the screen below. Click on the link title that you would like to know. When finish, just close it.

 

To hide the Office Assistant

•   Right-click on the Office Assistant and from the pop-up menu displayed, select Hide.

Using The Excel Conditional Formatting

 Excel conditional formatting allows you to apply different formatting

options, such as color, to a cell or cells based on the data in the cell(s).

Here are the 2 easy steps to implement the conditional formatting:

1.  Create a set of conditions that control formatting changes in the target cells.

2.  Enter your data. If the conditions you set are met by the data, then the formatting is applied.

Note: Up to three conditions can be set for a cell, so it is possible to vary the formatting as the contents of a cell changes.

To format cells using conditional formatting

•   If we enter the data as illustrated, we can then apply conditional formatting to it.

 

•   Select the range of cells which you wish to apply conditional formatting. In this example the range would be A1:C5.

•   From the Format menu, click Conditional Formatting.

 

•   Lets say we wish to highlight all values between 4 and 6, then we would enter the numbers in the correct fields, as illustrated above.

•   If we click on the OK button, then no special formatting would be applied to these values, so next we need to click on the Format button within this dialog box.

 

•   From the normal Format Cells dialog box is displayed, from which we can specify how data that meets the specified criteria will be displayed.

•   Select a color for the conditional formatting; choose red, for this example.

•   When finished click on the OK button to close this dialog box, which will return to the Conditional Formatting dialog box.

•   To add the other conditional formats, click the Add button. Otherwise, click on OK button to close this dialog box.

How to Install, Remove and Get Free Excel Add-in?

Excel add-in is the 'extension' of the Excel program. It’s a small application that when you install it, the will add an extra function to the Excel that can ease the complex tasks.

To install Excel Add-ins

•   From the Tools menu, click Add-ins to display the Add-ins dialog box.

 

•   If the add-in you want to install is already in the list then tick the corresponding check box. Click OK button.

•   You will prompt another dialog box asking you to install the add-in as follow:

 

•   Click Yes to continue. It will take some time and you may need the Office 2003 installation CD to proceed.

•   If the add-in you want to install is not in the list then click the Browse button will displays a dialog box allowing you to browse for an additional Excel add-in.

•   The Automation button will displays another dialog box that lists all registered COM automation servers available on the system.

Note: If you install an additional add-in the changes are not saved until you actually close Excel completely.

To uninstall Excel Add-ins

•   From the Tools menu, click Add-ins to display the Add-ins dialog box.

•   If the add-in you want to uninstall is in the list then uncheck the corresponding check box. Click OK button to uninstall the add-in.

To get free Excel Add-ins

•   - Allows you to export and save to the PDF format from your Office applications.

•   - Helps to remove any excess formatting in yourworkbooks.

•   - Removes password protected workbook or worksheet.

Discover the Full Potential of Excel IF Function!

 The Excel IF Function checks a condition that must be either true or

false. If the condition is true, the function returns one value; if the condition is false, the function returns another value.

The function has three arguments: the condition you want to check, the value to return if the condition is true, and the value to return if the condition is false.

Here is the Excel IF()Syntax:

IF(Logical_test, Ation_if_true, Action_if_false)

Logical _test

The logical_test evaluates an expression to see if it passes the test, i.e. is TRUE or does not pass the test, i.e. is FALSE.

Logical operators

 

Action_if_true

Action_if_true can be a value or an operation. Whichever, the result is placed in the cell that contains the IF ( ) Function if the logical_test is true.

Action_if_false

Action_if_false can be a value or an operation. Whichever, the result is placed in the cell that contains the IF ( ) Function if the logical_test is false.

To demonstrate the Excel IF function

Lets look at an example for calculating a bonuses based on total sales. A company offers its salesman a 5% bonus if the value of the total sales is below $5,000 per month. If the total sales exceed $5,000 per month, the bonus will be 10%.

When translated into the IF ( ) function it looks like the following: IF(TotalSales>5000,TotalSales * 10%, TotalSales * 5%)

•   Enter the following data for column A and column B accordingly.

 

•   Click on the cell to contain the IF( ) function. In this case, cells C2.

•   From the Insert menu, click on Function…to display the Insert Function dialog box.

OR press the Shift+F3

 

•   From the Insert Function dialog box displayed, select the IF option and then click OK button. The following dialog box will be displayed.

 

•   Enter B2 > 5000 into the Logical_test text area. Press the Tab key.

•   Enter 10% into the Value_if_true text area. Press the Tab key.

•   Enter 5% into the Value_if_false text area. Click OK. ?  Follow the same procedure for the cells C3 to C6.

Note: Every time, you just need to change the data from B2 to B6, then the bonus will be automatically calculated.


 

Discover the Full Potential of Excel IF Function!

 The Excel IF Function checks a condition that must be either true or

false. If the condition is true, the function returns one value; if the condition is false, the function returns another value.

The function has three arguments: the condition you want to check, the value to return if the condition is true, and the value to return if the condition is false.

Here is the Excel IF()Syntax:

IF(Logical_test, Ation_if_true, Action_if_false)

Logical _test

The logical_test evaluates an expression to see if it passes the test, i.e. is TRUE or does not pass the test, i.e. is FALSE.

Logical operators

 

Action_if_true

Action_if_true can be a value or an operation. Whichever, the result is placed in the cell that contains the IF ( ) Function if the logical_test is true.

Action_if_false

Action_if_false can be a value or an operation. Whichever, the result is placed in the cell that contains the IF ( ) Function if the logical_test is false.

To demonstrate the Excel IF function

Lets look at an example for calculating a bonuses based on total sales. A company offers its salesman a 5% bonus if the value of the total sales is below $5,000 per month. If the total sales exceed $5,000 per month, the bonus will be 10%.

When translated into the IF ( ) function it looks like the following: IF(TotalSales>5000,TotalSales * 10%, TotalSales * 5%)

•   Enter the following data for column A and column B accordingly.

 

•   Click on the cell to contain the IF( ) function. In this case, cells C2.

•   From the Insert menu, click on Function…to display the Insert Function dialog box.

OR press the Shift+F3

 

•   From the Insert Function dialog box displayed, select the IF option and then click OK button. The following dialog box will be displayed.

 

•   Enter B2 > 5000 into the Logical_test text area. Press the Tab key.

•   Enter 10% into the Value_if_true text area. Press the Tab key.

•   Enter 5% into the Value_if_false text area. Click OK. ?  Follow the same procedure for the cells C3 to C6.

Note: Every time, you just need to change the data from B2 to B6, then the bonus will be automatically calculated.

Excel CHOOSE Function

 The Excel Choose function allows you to select a value from a list of up

to 29 items.

It uses index_num to return a value from the list of value arguments.

For example, if value1 through value7 are the days of the week, CHOOSE() returns one of the days when a number between 1 and 7 is used as index_num.

Here is the Excel CHOOSE( ) Syntax:

CHOOSE(Index_num, Value1, Value2, Value3, …)

Index_num

This specifies which of the set of values is returned by this function. It must be a number between 1 and 29, or a formula or reference to a cell containing a number between 1 and 29.

•   If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.

•   If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.

•   If index_num is a fraction, it is truncated to the lowest integer before being used.

Value1, Value2, Value3…

A set of values from which the result is returned.

To use the Excel CHOOSE function (an example) ? Click on the cell where the function is to go.

•   From the Insert menu, click on Function…to display the Insert Function dialog box.

 

•   From the Insert Function dialog box displayed, under the Or select a category: box, select the Lookup & Reference.

•   In the Select a function: section, select the CHOOSE and click OK to display the CHOOSE dialog box.

 

•   Enter either a cell reference or value into the Index_num text entry box. In our case, enter the word 3.

•   Press the Tab key and enter the first value to be chosen from into the Value1 text entry box. Enter the word Monday.

•   Press the Tab key again and enter the second value to be chosen from into the Value2 text entry box. Enter the word Tuesday.

•   Repeat the above step until finish i.e. from Monday to Sunday.

•   Click OK when complete. You will see the result display Wednesday.

Note: If we enter the 5 in the Index_num text entry box, then it will return the Friday.

The Excel Date Function

Here is the guide to the Excel Date function:

Excel stores dates as a serial number giving each day of each year a unique number. The numbering system starts with 'day 1' being the 1st January 1900, 'day 2' being the 2nd January 1900, and so on.

Here is the Excel DATE Function Syntax:

DATE(year,month,day)

Year The year argument can be one to four digits.

•   If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(100,1,2) returns January 2, 2000 (1900+100). ?  If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2000,1,2) returns January 2, 2000.

•   If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.

Month

Month is a number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. Example: DATE(1996,14,2) returns the serial number representing February 2, 1997.

Day

Day is a number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. Example: DATE(1996,1,35) returns the serial number representing February 4, 1996.

Mathematical Date Functions

To calculate the difference between two dates in days or weeks

•   In the cell A1 enter the first date. In the cell A2 enter the second date.

•   In the cell A3 enter the formula =A2-A1 to calculate the difference between the second date and the first date.

•   This formula calculates the difference between the two dates in days. ?  To calculate the difference in weeks use the formula =(A2-A1)/7.

To add days or weeks to a date ?  Enter a date in cell A1.

•   In cell A2 enter the following formula: =A1+120. This adds 120 days to the date. ?  To add weeks to a date, multiply the number of weeks by seven to calculate the number of days, i.e. =A1+(10*7) adds ten weeks to the date.

To add months to a date ?  Enter a date in cell A1.

•   In cell A2 enter the following formula: =DATE(Year(A1), MONTH(A1)+1, DAY(A1)) ?  If you change the +1 value to +2 or +3, you will add two months or three months on respectively.

To find out the last day of the month ?  Enter a date in cell A1.

•   In cell A2 enter the following formula: =DATE(Year(A1), MONTH(A1)+1, 1)-1

•   This will give you a date that represents the last day of the month found in cell A1.

How to Create an Excel Pie Chart?

Here is the step-by-step guide to create an outstanding Excel pie chart:

Key in the data illustrated below accordingly as we will use it as our example to create chart.

 

Step 1: The Chart Wizard (1) – Chart Type

•   Click on any cell within the data containing the information that you wish to display as a chart, or highlight the exact data that you wish to display as a chart. ?  Click the Chart Wizard icon on the Standard toolbar.

 

•   From the Chart type: section you can select the chart type: Pie.

•   Then from the Chart sub-type: section you can select the exact format that you required for the selected chart type.

•   To see how the selected chart will look, use the Press and Hold to View Sample button within the dialog box. In our example, we accept the default selection. ?  Click the Next button to see the next page of the dialog box - Chart Source Data.

Step 2: The Chart Wizard (2) – Data Source

•   The Data Range tab allows you to specify the exact data that you wish to display within your chart.

•   You can choose to display Series in Rows or Columns. In our case here, choose the Series in Columns will be more appropriate.

 

•   When you click on the Next button, this will display the Chart Options dialog box.

Step 3: The Chart Wizard (3) – Chart Options

From the chart options dialog box displayed, you can select Titles, Legend, and Data Labels tabs and make the necessary changes.

To add title to a chart

•   In the Chart title: text box, enter the name for the chart, i.e. Score Marks.

•   In the example used, the screen will be as illustrated. To customize chart legend

•   From the Chart Option dialog box displayed, click on the Legend tab.

•   You can choose to display or not the chart legend and the placement of the legend in the chart by clicking on the radio button. To customize data labels

•   From the Chart Option dialog box displayed, click on the Data Labels tab.

•   You can choose to display or not the chart data labels by clicking on the radio button. In our example, tick the Percentage check box to display the percentage info.

 

•   When you click on the Next button, this will display the Chart Location dialog box.

Step 4: The Chart Wizard (4) – Chart Location

To define the chart location

•   You can choose to place the Excel pie chart on an existing worksheet as an object, or you can place it on a new worksheet. Two options for you to choose:

As new sheet: – It will place the chart in the new worksheet.

As object in: - It will place the chart in the existing worksheet.

 

•   Click on the Finish button and the Excel pie chart will be created as you have specified during the Chart Wizard creation process.

Use the Excel Paste Special Feature

                                                                         After copying data, you can use the

Excel Paste Special command to

paste specific cell contents such as formulas, formats, or comments from the clipboard into an Excel worksheet.

You can also use Paste Special to paste a link to Excel data or data from another program, such as Microsoft Word.

To use Paste Special to link to Excel data

•   Select the range of data that contain the items or attributes you wish to copy. ?  From the Edit menu, click Copy.OR press Ctrl+C ?  Highlight the cell where you want to place the range. ?  From the Edit menu, click Paste Special.

 

•   From the Paste Special dialog box displayed, under the Paste section, choose All or All except borders option. Other options included:

 

•   Under Operation section, specify which mathematical operation, if any, you want to apply to the copied data.

 

Note: Mathematical operations can be applied only to values. To use an option other than None, you must choose All, Values, All except border, or Values and number formats under Paste section.

•   To avoid replacing values in your paste area when blank cells occur in the copy area, select Skip blanks check box.

•   To change columns of copied data to rows, or vice versa, select Transpose check box.

•   Click OK button when finish.

To use Paste Special to link data from another program (MS Word) ? In Microsoft Word, highlight the range of text that you wish to copy.

•   From the Edit menu, click Copy.OR press Ctrl+C

•   Highlight the cell where you want to place the range in Excel.

•   From the Edit menu, click Paste Special to display the dialog box.

 

•   In the As box, click the Microsoft Office Word Document Object.

•   Choose the Paste link: option to link the pasted data to its source document. To paste and embed the information without creating a link, choose Paste: option. ? Click OK button.

Excel AND Function

 The Excel AND function returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE.

To determine whether the return will be TRUE or FALSE, the function evaluates at least one mathematical expression located in another cell in the spreadsheet.

The syntax for the AND function is:

=AND (Condition1, Condition2, ) condition is something that you want to test that can either be TRUE or FALSE.

Note: In Excel 2007, the AND function can contain a maximum of 255 conditions. For earlier versions of Excel, the limit is 30 conditions.

To use the AND() function (an example) ?  Type in the text in Excel A1 and A2 as follow:

 

•   Click on any cell that you wish the result to appears. Type the functions below, the AND function will return the following results:

=AND(A1>10, A1<100) would return TRUE.

=AND(A1=88, A2="Microsoft") would return FALSE.

=AND(A1>=50, A1<=88, A2="Microsoft Excel") would return TRUE.

To combine the AND function with IF function

•   Let’s look at the following example that shows nesting the AND function inside the IF function.

•   In the spreadsheet, type-in the following values accordingly:

Cell C1: 75

Cell C2: 95

Cell C3: 85

•   Click on the cell C5 and enter the function below.

=IF(AND(B1>80,B2>80,B3>80),"Good","Not Good")

•   If all three of these cells (C1,C2 and C3) contains a value greater than 80, the IF function will show the statement Good in cell C5.

•   If any of these three cells contain a number less than or equal to 80, the IF function shows the statement Not Good in cell C5.

•   So, the result in cell C5 will be Not Good since the cell C1 contains value that is less that 80.

Excel MIN Function

The Excel MIN function is used to find the smallest value in the selected range of cells or a given list of arguments. Text and blank entries are not included in the calculations of the MIN function.

The syntax for the MIN function is:

=MIN (Argument1, Argument2, Argument30)

Argument1, Argument2, Argument30 can be numbers, named ranges, arrays, or cell references. Up to 30 arguments can be entered in Excel 2003.

To use the MIN function (an example)

•   Enter the following data into cells B1 to B5:

 

•   Click on cell B7 where the results will be displayed.

•   Type =MIN(B1:B5) in cell B7.

•   Once finish, press the Enter key on the keyboard.

•   The answer 2177 appears in cell B7 which is the smallest number in the list.

Excel MAX Function

 The Excel MAX function is used to find the largest value in the selected

range of cells or a given list of arguments. Text and blank entries are not included in the calculations of the MAX function.

The syntax for the MAX function is:

=MAX(Argument1, Argument2, Argument30)

Argument1, Argument2, Argument30 can be numbers, named ranges, arrays, or cell references. Up to 30 arguments can be entered in Excel 2003.

To use the MAX function (an example)

•   Enter the following data into cells C1 to C5:

 

•   Click on cell C7 where the results will be displayed.

•   Type =MAX(C1:C5) in cell C7.

•   Once finish, press the Enter key on the keyboard.

•   The answer 953.5 appears in cell C7 which is the largest number in the list.

Excel COUNT Function

Excel COUNT function will return the number of entries (actually counts each cell that contains number data) in the selected range of cells.

Text and blank entries are not counted. If number data is later added to an empty cell in the range, the count total is automatically updated.

The syntax for the function is:

=COUNT(argument)

Argument is the range of cells to be totaled.

To use the COUNT function (an example)

•   Enter the following data into cells B1 to C6:

 

•   Click on cell C7 where the results will be displayed.

•   Type =COUNT(B1:C6) in cell C7.

•   Once finish, press the Enter key on the keyboard. ?  The answer 12 appears in cell C7.

Note: Since dates, times, and formulas are stored as numbers in Excel, the COUNT function will include any cells containing these types of data in the total.

Excel AVERAGE function

The Excel AVERAGE function can be used to calculate the average, or arithmetic mean, of values in a selected range of cells.

The syntax of this function is written as follows:

= AVERAGE (argument)

The argument for this function is data contained in the selected range of cells.

To use the AVERAGE function (an example) ? Enter the following data into cells C1 to C6.

 

•   Click on cell C7 where the results will be displayed.

•   Type “=AVERAGE( ” in cell C7 (without the quote).

•   Click and drag to select cells from C1 to C6 with the mouse.

•   Type the closing bracket " ) " after the cell range in cell C7 (without the quote).

•   Once finish, press the Enter key on the keyboard.

•   The answer 470.83 should be displayed in cell C7.

•   The complete function =AVERAGE(C1:C6) appears in the formula bar.

 

Excel OR Function

The Excel OR function returns TRUE if one condition are TRUE. It returns FALSE if all of the conditions are FALSE.

To determine whether the output will be TRUE or FALSE, the function evaluates at least one mathematical expression located in another cell in the spreadsheet.

The syntax for the OR function is:

=OR (Condition1, Condition2, ) condition is something that you want to test that can either be TRUE or FALSE.

To use the OR( ) function (an example) ?  Type in the number in cells C1 and C3 as follow:

 

•   Click on cell C5 where the results will be displayed. ?  Type =OR(C1>500, C2>500, C3>500) in cell C5 ?  Once finish, press the Enter key on the keyboard. ?  The answer TRUE appears in cell C5.

Note: If any of these three cells (C1,C2, or C3) contains a value greater than 500, the output for the OR function in cell C5 will be TRUE. If all three cells have numbers less than or equal to 500, the output will be FALSE.

The Excel RAND function

The Excel RAND function will generate a random number in Excel that is greater than or equal to 0 and less than 1.

The syntax for the RAND function is:

=RAND( )

To use the RAND( ) function (examples)

•   To generate a random number between 0 and 100, use =RAND() *100

•   To generate a random number and return only whole numbers use =INT(RAND()*100)

•   To generate a random number between 100 and 200, use =RAND() *(200100)+100

Note: If you want to use RAND function to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 key to change the formula to a random number.

Discover the Excel Automatic Calculation Capability!

                                                                                     Microsoft      Excel          Automatic

Calculation feature is an extremely

dynamic capability that by default automatically recalculates any changes you make to your Microsoft Excel worksheets.

Sometimes though you may find that due to the complexity and the size of your worksheet, you may want to control when the Microsoft Excel application recalculates your workbook or you may find that you just want a single worksheet to be recalculated.

To control the Excel 2003 Automatic calculation the worksheet, you must first turn the default automatic calculation function to manual.

To turn off the automatic calculation

•   From the Tools menu and select the Options command.

•   From the Options dialog box will displayed, select the Calculation tab.

 

•   Under the Calculation section, select the Manual radio button. ?  Click the OK button.

Once you are in manual calculation mode, there are two ways you can force the Microsoft Excel to recalculate.

To use the automatic calculation feature

•   From the Tools menu and select the Options command.

•   From the Options dialog box will displayed, select the Calculation tab and then use either the Calc Now (F9) or Calc Sheet buttons.

•   To recalculate the entire workbook, click on the Calc Now (F9) button OR press F9 key.

•   Click on the Calc Sheet button will only update your current worksheet OR press Shift + F9.

Copy Excel Worksheet from one Workbook to Another

Do you need to copy Excel worksheet?

If you use Microsoft Excel, then you’ve run into the situation where the

information in a worksheet is needed in another workbook. You could go through and copy and paste the cells from one worksheet to another, but if you are working with a large spreadsheet, this is way too painful. Excel makes it easy to copy (and even easier to move) worksheets from one workbook to another.

To copy a worksheet to another workbook

•   Open the workbook that you intend to copy the worksheet. (Ex: Book1)

•   Open another workbook that you want the worksheet copied to. (Ex: Book2) ?  Go to the workbook with the desired worksheet that you wish to copy. Right-click on the tab of the target worksheet.

 

•   Select Move or Copy… from the menu.

 

•   From the Move or Copy window appears, use the To book: dropdown list to select the workbook that you want to copy the worksheet to. (Ex: Book2)

•   Then select the existing sheet that you want the copy to be placed in front of. For example, if you select the Sheet2, the copied worksheet will be place before the Sheet2.

•   Check the Create a copy check box.

•   Click OK. You will now have the same worksheet in both workbooks.

Note: To move a worksheet to another workbook, just follow the above steps and simply exclude checking the Create a copy check box.

The Excel Shared Workbook - How to Setup?

Do you know the Excel Shared Workbook function?

 Yes, Microsoft Excel 2003 allows multiple people entering information

into the same spreadsheet at the same time. This function in Microsoft Excel is called Shared Workbooks. It’s also one of the clear advantages of using Excel over other spreadsheet programs.

If a user tries to open a Workbook that is already open, then they normally get a message saying that the File is locked by a certain user and would you like to open the file in Read only mode. However, you can open a workbook in shared mode by following these steps.

Note: To utilize this feature, make sure your computer is running in the network environment.

To share an Excel file

•   Open the Excel file that you want to share.

•   From the Tools menu and choose Share Workbook.

 

•   From the Share Workbook dialog box displayed, in the Editing tab, check the Allow changes by… check box.

•   Click OK. This allows other people to use your Excel workbook whilst you are working on it.

To know the advanced share workbook features ? Open the Excel file that you want to share.

•   From the Tools menu and choose Share Workbook.

•   From the Share Workbook dialog box displayed, click the Advanced tab.

There are three areas that you can change:

1.  Track Changes.

Microsoft Excel allows you to store over 32000 days worth of changes to your Excel file, however, there is a catch. All of that information is stored in your file and consequently your file will grow exponentially. Most organisations generally work on a maximum of 30 days (the default). However the value entered simply depends on your workbooks requirements. You can choose the second option - Don't keep change history. This simply ensures no changes are maintained.

2.  Update Changes.

This function simply allows you to define how often the spreadsheet changes are updated. The most common element users use is the Update Every 15 minutes. What this does is to force a save and filter through the data to all the users. One point to consider is that if you workbook is relatively large and you are working on a slow computer you may want to increase that time to 30 minutes.

3.  Save Changes.

This defines whose changes will override who's. There are two options available:

•   Ask me which changes win

•   The changes being saved win

Which option you choose really depends on the type of workbook you have created.

Another critical issue you need to consider is if somebody disables the Excel Shared Workbook function. If this occurs, when you try to save your workbook you will be required to save it somewhere else or lose your work. This is one of the downsides to the Share Workbook function

The Excel Drop Down List - How to Create It?

 Microsoft Excel 2003 allows you to build your own Excel drop down list!

Well, this feature is really cool.

Have you ever been in a situation where you were building a template in Microsoft Excel 2003 and it would have been really useful to actually have a drop down list so that your users could choose from a preset list, rather than them simply typing in their own values?

This tutorial show you how to create the drop down list in Excel.

To create an Excel drop down list

•   Open a new Excel workbook. On the first worksheet (Sheet1) enter the following data.

Cell A1 - Title

Cell B1 - Full Name

•   Click on the second worksheet (Sheet2) in your workbook. Enter the following data.

Cell A1 - Title Cell A2 - Mr

Cell A3 - Miss

Cell A4 - Mrs

Cell A5 - Ms

•   Then select the cell range A2 to A5. We are going to give a name for this range of cell.

•   From the Insert menu choose the Name option from the menu and click Define.

 

•   From the Define Name dialog box displayed, in the Names in workbook: text box simply type in the name Title and click the OK button.

•   Now, return to the first worksheet (Sheet1). We will create a drop down list in cell A2 so click on cell A2.

•   From the Data menu, click on Validation. This will open the Data Validation dialog box.

 

•   Under the Settings tab, in the Allow: drop down list, choose the List option. ?  In the Source: text box, typing in = Title (This is to define the name Title as the range).

•   To complete the process, click the OK button.

•   Return to the cell A2 and you will now see a drop down box. If you click on the arrow you will see the results from your second sheet.

 

Note: If you do not want your range for your list to be on the same worksheet, then you must name the range.

The Excel Time Function

The Excel Time Function – What its use for? The Excel Time function is to display the time in a cell.

Excel allows you to manipulate dates and times and perform calculations on them. If you type a date or time (e.g. 13/3/08 or 9:28) directly into a cell Excel should recognise it as such. The program will normally align it to the right of the cell and display it in the formula bar in a standard format (e.g. 13/03/2008 or 9:28:00 AM).

The TIME Function Syntax:

TIME (Hour, Minute, Second)

Hour is a number from 0 (zero) to 23 representing the hour.Minute is a number from 0 to 59 representing the minute.Second is a number from 0 to 59 representing the second.

Examples:

TIME(12, 0, 0) is equivalent to 12:00:00 p.m.

TIME(16, 48, 10) is equivalent to 4:48:10 p.m.

To add and subtract times

•   To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function =A1 + TIME(1,35,10)

•   To subtract 1 hour, 35 minutes, 10 seconds from a time in A1, use the function =A1 - TIME(1,35,10)

Note: You can add times using the =SUM worksheet function. Just enter all of your times as HH:MM:SS, and then use SUM function to add them up.

The Excel Date Function

In Excel, the Date Function is to display the date in a cell.

To display the current time or date in a cell, use the NOW() function. Just type =NOW() in the cell and it will display the current data and time.

The DATE Function Syntax: DATE (Year, Month, Day)

To add and subtract dates

•   In the cell A1, type a date such as 1/2/08.

•   In cell A2, if you wish to add 8 days to cell A1, use =A1+9, which gives 9/2/08.

•   In cell A3, if you wish to subtract 5 days to cell A1, use =A1-5, which gives 4/1/08.

Excel Comment – Why and how to add and manipulate it in Excel spreadsheets?

 

Excel Comment is basically short

notes that can be inserted into any cell in Excel. You cannot see it if you do not click on the comment. It’s useful for reminders, notes for others, or for cross-referencing other workbooks.

Adding comment to worksheets cells are a great way to explain the cell contents, formulas, offer tips to users or other data in the spreadsheet.

Basically there are three ways to add a comment:

1)  Using Menu bar

2)  Using mouse right-click

3)  Using Reviewing toolbar (not explain here)

To insert a Comment using menu bar method

•   Select the cell in which you want the comment.

•   From the Insert menu, click on Comment. You can see something like this:

 

•   Type your comment where the cursor is flashing.

•   Text will wrap automatically. If you want to start a new line, press the Enter key. ? When finished, click any cell outside the comment box.

To insert a Comment using right-click method ? Point to the cell in which you want the comment.

•   Right-click on the cell and click Insert Comment.

•   Type your comment where the cursor is flashing.

•   When finished, click any cell outside the comment box.

To display a specific Comment

•   Right-click the cell which contains the comment. ?  Choose Show/Hide Comment.

To hide the Comment

•   Right-click the cell which contains the comment. ?  Choose Hide Comment.

To edit the Comment

•   Right-click the cell which contains the comment.

•   Choose Edit Comment.

•   Retype your comment.

To delete the Comment

•   Right-click the cell which contains the comment. ?  Choose Delete Comment.

To change the Comment Shape

•   Right-click the cell which contains the comment. ?  Choose Edit Comment.

•   Click on the border of the comment, to select it.

•   On the Drawing toolbar, click the Draw icon, point to Change AutoShape, and choose a category.

•   Click on a shape to select it.

•   When finished, click outside the comment.

To change the others Comment attribute ?  Right-click the cell which contains the comment. ?  Choose Edit Comment.

•   Click on the border of the comment, to select it. ?  From the Format menu, click Comment.

 

•   From the Format Comment dialog box displayed, click on the specific tab to change the necessary settings.

•   You can change the comment font and background color, font size and effects, alignment, margin, etc.

•   When finish, click OK.

Excel 2003 Data Analysis Tools: Sort Data, Subtotals, Pivot Tables and What-If Analysis This tutorial shows some of the Excel 2003 data analysis tools, including:

 

Sorting and filtering lists: With the information you have in a list, you can sort and display data that meet certain criteria, insert formulas to calculate subtotals, and create summary tables.

Subtotals: Excel can quickly goes through the whole table and inserts rows and adds totals for the column that you specify.

PivotTable: It is a quick way to analyze data such as summing up data in a rectangular table shape when you have more than one category to summarize upon.

What-If Analysis: The ability to assist in decision making with dynamic models. A dynamic model uses formulas that instantly recalculate when you change values in cells that are used by the formulas.

You can either view or download the pdf report by clicking here.

Excel Styles and Custom Formatting

 

This article let you understand the

different between Excel styles and Excel custom formatting.

When you format a cell in a worksheet, you can define cell shading, font color, font, font size, borders, and much, much more. In short, there are many definitions that can be set for a single cell.

You save all the formatting you have customized. The main limitation is that all custom formatting is only saved in the current workbook. Without defining a name for the format, it will be difficult for you to find and reuse it.

Furthermore, when using this method, you cannot save definitions such as font, patterns, borders and any of the wide variety of formatting options in the Format Cells dialog box.

Then the Excel Styles are different!

Changing the definitions of the style will modify the default format of text or numbers in the workbook.

To change the default Style in a workbook ? From the Format menu, click Style.

 

•   From the Style dialog box displayed, in the Style name box, select Normal.

•   Click Modify button.

•   From the Format Cells dialog box displayed, do the necessary adjustments.

Number tab: Set the number format that appears in the cells.

Alignment tab: Change the vertical and horizontal alignments of the text/number in the cells.

Font tab: Change the font pattern, style, color of the text/number in the cells. Border tab: Add or remove the border of the cells.

•   Click OK.

The formatting suggested by default in the workbook allows you to enter both text and numbers into cells. Formatting a cell for text only by selecting the Text format from the list of standard formats appearing in the Category box in the Number tab of the Format Cells dialog box will allow only text and not numbers to be displayed in the cell. The opposite is also true. If you format a cell for numbers only, you will not be able to display text in the cell.

Actually, in the Style dialog box, you can create a wide range of complex styles and save each one separately with a unique name. These styles can then be applied repeatedly.

The example below shows you how to format a number so that a negative number displayed in brackets and zero (0) is replaced with a dash (-). Choose the font and font size.

To create and save custom styles ?  From the Format menu, click Style. OR press Alt+’ .

 

•   From the Style dialog box displayed, in the Style name box, enter Negative Numbers with brackets, 0=-.

•   Click Modify button.

•   From the Format Cells dialog box displayed, select the Number tab, and in the Category: box, select Custom.In the Type: box, change and type in the following number format #,##0 ;[Red](#,##0);- ;

 

•   Select the Font tab.

•   In the Font: box, select Arial. In the Font style: box, select Regular, and in the Size: box, select 10.

•   Click OK.

•   In the Style dialog box, click OK.

Use the SUMIF Function Correctly!

 

In Excel 2003, the SUMIF function

adds all numbers in a range of cells, based on a given criteria.

Sometimes you may need to use only certain values inside a range in a summation, based on a defined condition. Using Microsoft Excel 2003, this can be calculated with the SUMIF function.

The SUMIF function can also use another range of cells to determine whether or not the condition is met.

The syntax for the SUMIF function is: =SUMIF(range,criteria,sum_range)

range is the range of cells you want to evaluate (apply the criteria against).

criteria is the criterion you want to meet to evaluate the number. sum_range are the numbers you want to actually sum.

To use the SUMIF function (example) ?  Create a worksheet as shown below:

 

Now, we will use this worksheet to calculate the Smith’s total sales.

•   Click on the cell B10, the cell that will displays the result.

•   From the Insert menu, click on Function.

 

•   From the Insert Function dialog box displayed, under the Search for a function: box, type in sumif and click the Go button.

•   In the Select a function: box, make sure you select the SUMIF and click OK.



•   From the Function Arguments dialog box displayed, do the followings:

Range: Select or type in A2:A8 as we want to evaluate within this ranges that contains the name Smith’s. Criteria: Type in Smith.

Sum_range: Select or type in B2: B8 as this is the ranges that we want to add up the total sales by Smith’s.

 

•   Click OK. This produces the result 85450. That is 35200+22100+28150.

Note: To find the sum of the sales that exceed 30000, use this function:

=SUMIF(B8:B8,>30000)

 

This produces the result 100700 (35200+22100+28150).

"Excel Text Concatenation: How to Join Multiple Cells Together?"

Do you know that the Excel text concatenation feature can combine various cells together into one single cell in Microsoft Excel 2003?

 

For example, you may have three columns named, Title, First name and Last name and you maybe asked to combine those three values together. To achieve this capability we use the Text

Concatenation Function. This article will look at the process of combining these cells together.

To use the Excel text concatenation feature

•     Open Microsoft Excel 2003 and create the worksheet as shown below:

 

•     Go to cell E1 and type in the word Result, then select the cell E2. We are going to combine cell A2, B2, C2, D2 using the text concatenation function which is the ampersand (&) symbol.

•     However, we cannot automatically just combine Dates and Numbers with Text cells. We need to use the TEXT function.

•     " Let us focus first on adding the Title, First name and Last Name together. First select cell E2 and type the following formula:

= A2 & " " & B2 & " " & C2

Note: There is a space between the double quotes. This is to ensure that the text in the fields do not combine together without spacing.

To add a date to the text

•     To add a date to the text we must first convert it to text using the TEXT function. The TEXT function also can be use to format the date in a specific format.

•     For example if I wanted a date in the format - 09/12/2008, I would enter the Text function as - TEXT(Cell Address, "dd/mm/yyyy")

•     To incorporate the Date into the cell, simply type the formula as follows - = A2 & " " & B2 & " "

& C2 & " was born on " & Text(D2,"dddd, dd mmmm yyyy")

•     The result will be - Mr John Harvest was born on Sunday, 12 October 1980..

The good part about Excel Text concatenation is that you can use it with Excel Database lists and combine multiple cells together and then use the data in the list with programs like Microsoft Word to do a mail merge.

"An Introduction to Visual Basic Editor in Excel 2003"

Excel 2003 is supplied with the Visual Basic Editor for modifying and creating macros and for creating user defined forms.

 

To start the editor, open an Excel file that contains macros. Then, click the Tools menu, point to Macro and click on Visual Basic Editor.

 

The Editor contains two important windows: the Project Explorer and the Code window.

1) The Project Explorer

The Project Explorer located at the left hand side of the editor screen. It displays a hierarchical list of

the projects and all of the items contained in and referenced by each of the projects. From the Project Explorer, it contains several elements:

                                                   •        VBAProject - the name of an open workbook.

Microsoft Excel Objects: The worksheets and chartsheets contained in the VBAProject. The event procedures for each sheet and the workbook are stored here.

Forms: any user defined forms.

Modules: recorded and written procedures.

2) The Code Window

Use the Code window located at the right hand side of the editor screen enables you to write, display and edit Visual Basic code.

To open a Code window from the Project Explorer window, just double-click on a form or module in the Project Explorer. You can highlight and drag the selected text to a different location in the current Code window or to another Code window.

Here are the components on the code window:

•     Procedures/Events Box

Lists all the events recognised by Visual Basic for a form or control displayed in the Object box. When an event is selected, the event procedure associated with that event name is displayed in the Code window.

If (General) is displayed in the Object box, the Procedure box lists any declarations and all of the general procedures that have been created for the form. If editing module code, the Procedure box lists all of the general procedures in the module.

•     Object Box

Displays the name of the selected object. Click the arrow to the right of the list box to display a list of all objects associated with the form.

•     Split Bar

Dragging the Split bar down, splits the Code window into two horizontal panes, each of which scrolls separately. View different parts of code at the same time. Dragging the bar to the top or the bottom of the window or double-clicking the bar closes a pane.

•     Margin Indicator Bar

A grey area on the left side of the Code window where margin indicators are displayed. Margin indicators provide visual cues during code editing.

"Goal Seek: Using Goal Seek with Real-Life Example Demonstrations"

When you know the desired result of a single formula but not the input value the formula needs to determine the result, you can use the Goal Seek feature. So, it allows you to find the correct input to produce the desired output.

 

Goal Seek (Text) - An Example

Say you wish to purchase a car, your current vehicle is worth $4,000 in part exchange. This part exchange value plus your cash down payment of $1000 makes up the 20% deposit required towards

the cost of the new car. A spreadsheet to show this is illustrated and shows that you can afford a new car costing $25,000. The only formula in the spreadsheet is in cell E4:

=(E2+E3)*5

Suppose that you have seen a car costing $ 30,000 your question would likely be. What will my cash down payment now need to be? You could experiment by entering various values into the Down Payment cell or you could use Goal Seek.

•     Make cell   E4 (i.e. the total car price that you can afford) your active cell by clicking on this  cell.

•     Start  Goal Seek  by opening the  Tools menu and click  Goal Seek   .

•     Enter  30000  in the  To value text  dialog box  and then press the  Tab  key to move to the field in the Goal Seek dialog box called By changing cell:.

 

•     Use the mouse to click on the cell   E2 . This means that when click on the  OK  button, the  contents of this cell will change to fit in with our desired result.

•     Click  OK  . The  Goal Seek Status dialog box is displayed and the value of your Down  Payment has changed to $2000. This is the new amount you should have pay to obtain this car.

 

•     If you click  OK  these changes are accepted or if you click  Cancel  these changes are not  accepted.

Goal Seek (Graphical) - An Example

If we suppose that you have seen a car costing $14,000 your question would likely be. What will my cash down payment now need to be?

•     First select the data as illustrated.

 

•     Click on the  Chart Wizard  icon on the Standard   toolbar  and when the  Chart Wizard  dialog  box is displayed, click on the Finish button to create a bar chart using default formats.

 

•     The bar chart produced should resemble that shown below.

 

•     Click  once  on the column representing the  Total car price that you can afford  . You will see  a small rectangular square in the center of each column as illustrated.

 

•     Click  once  on this column again, and the display will change as illustrated.

 

•     Move the mouse pointer to the top of this column and then click and drag the column up or  down as required. You will see a small rectangular box is displayed, which indicates the value that you have dragged to. In our example, we dragged to $ 14,000.00 only that release the mouse.

 

•     The  Goal Seek  dialog box appears ready for you to enter a cell reference into the   By changing cell: text box.

•     Click on the cell   E2 , to place this value into the  By changing cell:  text box.

 

•     Click on the  OK  button to display the  Goal Seek Status  dialog box.

 

•     Click on the  OK  button to accept these changes and exit from the  Goal Seek Status  dialog  box.

"Using Scenario Manager with Example Demonstrations"

The Scenario Manager is similar to Goal Seekin Microsoft Excel.

However it allows you to change multiple cells in order to see changes (not just one as is

the case with Goal Seek). One of the advantages of scenarios is that they are kept for later reference and can be printed in summary form.

Scenario Manager - An Example

Look at the illustration below. We will use it to illustrate the Scenario Manager.

 

It shows a sheet that is calculating yearly payments on specific items this year and details expected percentage increase in these costs. You may be interested in knowing how the figures are effected if we make a range of assumptions about how each item might increase.

The cell E3 contain the formula =C3*D3%

The cell E4 contain the formula =C4*D4%

The cell E5 contain the formula =C5*D5%

The cell E6 contain the formula =C6*D6%

The cell E7 contain the formula =SUM(E3:E6)

The cell C7 contain the formula =SUM(C3:C6)

We are going to create scenarios for the following situations:

•     Staff Low - 2.5%

•     Staff Low, Energy High - 2.5%, 35%

•     Once the above data and formulas have been created, select the changing cells,  D3:D6   .

•     From the  Tools  menu, click  Scenarios .

•     If no previous scenarios have been created you will see the following  dialog box   .

 

•     Click on the  Add  button and the  Add Scenario  dialog box is displayed.

 

•     Enter a name for the scenario you are about to create. In this case enter the name  Staff Low into the Scenario name text box and then click on the OK button. The Scenario Values dialog box will be displayed as illustrated below.


 

•     The percentage rise in staff costs are located in cell D4 and we need to change the contents  of this cell. In the dialog box enter a low value, i.e. 2.5 in the text box, next to $D$4.

•     Click on the  OK  button and you will be returned to the main  Scenario Manager  dialog box, as  illustrated below.

 

•     Next we will add a second scenario where staff cost increases are low, but energy costs  increases are high. Click on the Add button and enter the name for the next scenario, in this case Staff Low Energy High.

•     Click on the  OK  button, and change the two cells as below. In the $D$4 text box, enter  2.5  . In  the $D$5 text box, enter 35.

 

•     Click on the  OK  button.

•     You will be returned to the main  Scenario Manager  dialog box. You can go on adding  different scenarios in the way outlined above. In this case we will content ourselves with just these two scenarios.

To show a scenario

•     We have set up two scenarios. To see the effect of one of these scenarios, select the  scenarios from the main Scenario Manager dialog box.

•     In this case we have selected  Staff Low Energy High   .

•     Click on the  Show  button.

To view an alternative scenario

•     In this case select the  Staff Low scenario from the  Scenario Manager  and click on the  Show button. The data will change as illustrated below.

 

•     Click on the  Close  button to close the  Scenario Manager  dialog box.

"Excel Solver: Example Demonstration on Using Solver in Excel 2003"

Excel Solver is the most versatile ''what if'' tool. It can handle many different variables and where possible, Solver will produce the optimum answer.

Note: Solver is an add-ins tool in Microsoft Excel 2003. If you are unable to view the Solver,

just go to the Tools and click on Add-Ins. From the Add-Ins dialog box appear, tick on Solver Add-in and click OK.

Excel Solver Terminology

Target Cell

The cell that will be set to a value, maximum or minimum. Often this cell is where you specify the maximum cost of a project.

Changing Cell

The cells that Solver will change the contents of to achieve the desired objective.

Constraints

Contains the changes that Excel will make.

Excel Solver - An Example

In this example we have a budget to purchase as many new car as possible for the new company car fleet. We need a mix of small, medium and large cars.

We have a number of constraints however:

•     The total budget is limited to $ 500,000

•     We need at least 4 small sized cars

•     We need at least 3 medium sized cars

•     We need at least 2 large sized cars

 Also if we are going to let the  computer  calculate a mix of the different number of cars to meet  our requirements and budget, then we will have to specify to Excel that buying part of a car is no use, i.e. he numbers of each type of car bought must be whole numbers (integers)!

Construct your Spreadsheet and Use the Excel Solver

•     We have constructed the following data.

•     Formulas and functions used:

•     In cell E4 we have used the formula =B4*C4

•     In cell E5 we have used the formula =B5*C5

•     In cell E6 we have used the formula =B6*C6

•     In cell E8 we have used the function =SUM(D4:D7)

•     When you have entered all the data, formulas and functions as outlined above, click on the  cell D8.

•     From the Tools menu, click on Solver…

•     Make sure that the  text box  , Set Target Cell contains $D$8. If you selected this cell prior to  starting Solver this cell reference should be entered here automatically.

•     Click on the  Value of  button and in the text box to the side of it enter the number 500,000  (this is setting the maximum size of our budget). The dialog should appear as illustrated below.

 

•     Next we need to decide which values need to change in order to fit our requirements. In this  case the values to be changed are the numbers of each class of car that we can afford.

•     Click in the  By Changing Cells  part of the dialog box.

•     Either enter the information required by typing in $C$4:$C$6

OR use the mouse to drag across these cells C4 to C6 and the cell reference will be entered automatically.

 

•     Next we need to tell Excel that constrains we have, such as limited budget etc.

•     Click on the  Add  button and you will see the  Add Constraints  dialog box, as illustrated  below.

 

•     To enter the restraint that we have a budget limited to $ 500,000, in the  Cell Reference  text  box enter the cell $E$8.

•     Then click on the down arrow in the center part of the dialog box, and select the   = symbol.

•     In the  Constraint  part of the dialog box, enter the number 500000.

•     The dialog box will resemble that shown below.

 

•     To force Excel to realize that we cannot buy part of a car just whole numbers of cars we need  to add another constraint.

•     Click on the  Add  button  (NOT THE OK BUTTON)   .

•     In the  Cell Reference  text box either enter the information  $C$4:$C$6

OR use the mouse to drag across these cells C4 to C6 and the Cell Reference will be entered automatically.

•     In the center part of the dialog box, click on the down arrow and select int which will force  Excel to only use integer (i.e. whole) numbers in the selected range. The dialog box will look as below.

 

•     Next we need to tell Excel that we must have a minimum of four small cars.

•     Click on the  Add  button.

•     In the  Cell Reference  text box either enter the reference  $C$4

OR use the mouse to click on the cell C4 and the cell reference will be entered automatically.

•     Select   >= from the center part of the dialog box.

•     In the  Constraint  box, enter the value 4.The dialog box will be as illustrated below.

 

•     Next we need to tell Excel that we must have a minimum of  three  medium sized cars.

•     Click on the  Add  button.

•     In the  Cell Reference  text box either enter the reference  $C$5

OR use the mouse to click on the cell C5 and the cell reference will be entered automatically.

•     Select   >= from the center part of the dialog box.

•     In the  Constraint  box, enter the value 3. The dialog will be as illustrated below.

 

•     Next we need to tell Excel that we must have a minimum of   tw o large cars.

•     Click on the  Add  button.

•     In the  Cell Reference  text box either enter the reference  $C$6  .

OR use the mouse to click on the cell C6 and the cell reference will be entered automatically.

•     Select   >= from the center part of the dialog box.

•     In the  Constraint  box, enter the value 2. The dialog will be as illustrated below.

 

•     We are now ready to solve the purchasing problem.

•     Click on the  OK  button and you will see the dialog box below.


 

•     Click on the  Solve  button. After a short time you will see the following dialog box.

 

•     Click on the  OK  button to accept the solution.

•     The data in your workbook will now be as illustrated below.

 

I hope that you already see the true value of Excel Solver. It's a good tool if you apply it correctly.

MS Excel Topics: Formulas/Functions (Alphabetical)

 

View Formulas/Functions by Category

Alphabetical Listing of Formulas/Functions:

Abs

Exact

Or

ACos

Exp

Percentile

ACosh

Fact

PercentRank

Address

False

Permut

And

FileDateTime(VBA)

Pi

Areas

FileLen(VBA)

Pmt

Asc(VBA)

Find

Power

ASin

Fix(VBA)

PPmt

ASinh

Fixed

Product

ATan

Floor

Proper

ATan2

Forecast

PV

ATanh

Format Dates(VBA)

Quartile

Atn(VBA)

Format Numbers(VBA)

Radians

AveDev

Format Strings(VBA)

Rand

Average

FV

Rank

AverageA

GetAttr(VBA)

Rate

BetaDist

GetPivotData (Ex #1)

Replace

BetaInv

GetPivotData (Ex #2)

Rept

BinomDist

Growth

Right

Case(VBA)

HLookup

Rnd(VBA)

CBool(VBA)

Hour

Roman

CByte(VBA)

Hyperlink

Round

CCur(VBA)

If

RoundDown

CDate(VBA)

Nested Ifs(up to 7)

RoundUp

CDbl(VBA)

Nested Ifs(more than 7)

Row

CDec(VBA)

IF-THEN-ELSE(VBA)

Rows

Ceiling

Index

RTrim(VBA)

Cell

Indirect

Search

Char

Info

Second

ChDir(VBA)

InStr(VBA)

SetAttr(VBA)

ChDrive(VBA)

InStrRev(VBA)

Sgn(VBA)

Choose

Int

Sign

Chr(VBA)

Intercept

Sin

CInt(VBA)

IPmt

Sinh

Clean

Irr

SLn

CLng(VBA)

IsBlank

Small

Code

IsDate(VBA)

Space(VBA)

Column

IsErr

Sqrt

Columns

IsError

StDev

Combin

IsLogical

StDevA

Concatenate

IsNA

StDevP

Concatenate with &

IsNonText

StDevPA

Cos

IsNull(VBA)

Str(VBA)

Cosh

IsNumber

StrConv(VBA)

Count

IsNumeric(VBA)

Substitute

CountA

ISPmt

Subtotal

CountBlank

IsRef

Sum

CountIf

IsText

SumIf

Covar

Large

SumProduct

 

CSng(VBA)

LCase(VBA)

SumSq

CStr(VBA)

Left

SumX2mY2

CurDir(VBA)

Len

SumX2pY2

CVar(VBA)

Ln

SumXmY2

Date

Log

Switch(VBA)

Date(VBA)

Log10

Syd

DateAdd(VBA)

Lookup

T

DateDif

Lower

Tan

DateDiff(VBA)

LTrim(VBA)

Tanh

DatePart(VBA)

Match

Text

DateSerial(VBA)

Max

Time

DateValue

MaxA

TimeSerial(VBA)

DAverage

Mdeterm

TimeValue

Day

Median

Today

Days360

Mid

Transpose

Db

Min

Trim

DCount

MinA

True

DCountA

Minute

Trunc

DDb

Minverse

Type

Degrees

MkDir(VBA)

UCase(VBA)

DGet

Mmult

Upper

Dir(VBA)

Mirr

Val(VBA)

DMax

Mod

Value

DMin

Month

Var

Dollar

MonthName(VBA)

VarA

DProduct

N

VarP

DStDev

NA

VarPA

DStDevP

Not

Vdb

DSum

Now

VLookup

DVar

NPer

Weekday

DVarP

Npv

WeekdayName(VBA)

Odd

Year

Even

Offset

Examples:

Miscellaneous Functions:

Two-Dimensional Lookup (Example #1)

Two-Dimensional Lookup (Example #2)

Two-Dimensional Lookup (Example #3)

Two-Dimensional Lookup (Example #4)

Test a string for an alphanumeric value

Convert currency into words

How to Autonumber in Excel

Delimit values with Ascii 29 character

Creating a custom round function

Creating a custom average function (that excludes outlyers from the calculation

MS Excel Topics: Formulas/Functions (By Category)

 

View an Alphabetical Listing of Formulas/Functions

String Functions:

            Asc(VBA)                                 Find

Mid

Substitute

 

            CharFixed

Proper

T

 

            Chr(VBA)                                  Format(VBA)

Replace

Text

 

            CleanInStr(VBA)

Rept

Trim

 

            CodeInStrRev(VBA)

Right

UCase(VBA)

 

              ConcatenateLCase(VBA)

RTrim(VBA)

Upper

 

               Concatenate with &Left

Search

Value

 

            CurDir(VBA)                           Len

Space(VBA)

 

            DollarLower

Str(VBA)

 

            ExactLTrim(VBA)

Numeric / Mathematical Functions:

StrConv(VBA)

 

Abs

CountIf

Minverse

Small

 

ACos

Degrees

Mmult

Sqrt

 

ACosh

Even

Mod

Subtotal

 

ASin

Exp

Odd

Sum

 

ASinh

Fact

Pi

SumIf

 

ATan

Fix(VBA)

Power

SumProduct

 

ATan2

Floor

Product

SumSq

 

ATanh

Format(VBA)

Radians

SumX2mY2

 

Atn(VBA)

Int

Rand

SumX2pY2

 

Average

Large

Rnd(VBA)

SumXmY2

 

AverageA

Ln

Roman

Tan

 

Ceiling

Log

Round

Tanh

 

Combin

Log10

RoundDown

Trunc

 

Cos

Max

RoundUp

Val(VBA)

 

Cosh

MaxA

Sgn(VBA)

Var

 

Count

Mdeterm

Sign

VarA

 

CountA

Min

Sin

VarP

 

CountBlank

Statistical Functions:

MinA

Sinh

VarPA

 

AveDev

Growth

Percentile

StDevP

 

Average

Intercept

PercentRank

StDevPA

 

AverageA

Large

Permut

Var

 

BetaDist

Max

Quartile

VarA

 

BetaInv

MaxA

Rank

VarP

 

BinomDist

Median

Small

VarPA

 

Covar

Min

StDev

 

 

Forecast

Logical Functions:

MinA

StDevA

 

 

And

If

IF-THEN-ELSE(VBA)

True

 

Case(VBA)

Nested Ifs(up to 7)

Not

 

 

False

Information Functions:

Nested Ifs(more than 7)

Or

 

 

Cell

IsErr

IsNull(VBA)

N

 

IsError

IsNumber

NA

 

Info

IsLogical

IsNumeric(VBA)

Type

 

IsBlank

IsNAIsRef

 

IsDate(VBA) Date & Time Functions:

IsNonTextIsText

 

Date

DateSerial(VBA)                      Minute

TimeSerial(VBA)

Date(VBA)

DateValueMonth

TimeValue

DateAdd(VBA)

DayMonthName(VBA)

Today

DateDif

Days360Now

Weekday

DateDiff(VBA)

Format(VBA)                            Second

WeekdayName(VBA)

DatePart(VBA)

Database Functions:

HourTime

Year

DAverage

DGetDProduct

DSum

DCount

DMaxDStDev

DVar

DCountA

Lookup / Reference Functions:

DMinDStDevP

DVarP

Address

GetPivotData(Ex #1)Indirect

Rows

Areas

GetPivotData(Ex #2)Lookup

Switch(VBA)

Choose

HLookupMatch

Transpose

Column

Hyperlink

Offset

VLookup

Columns

More Lookup Functions:

Index

Row

 

Two-Dimensional Lookup (Example #1)

Two-Dimensional Lookup (Example #2)

Two-Dimensional Lookup (Example #3)

Two-Dimensional Lookup (Example #4)

Data Type Conversion Functions:

            CBool(VBA)                             CDate(VBA)

CInt(VBA)

CStr(VBA)

            CByte(VBA)                             CDbl(VBA)

CLng(VBA)

CVar(VBA)

            CCur(VBA)                               CDec(VBA)

Financial Functions:

CSng(VBA)

 

            DbIrr

Npv

Rate

            DDbISPmt

Pmt

SLn

           FVMirr

PPmt

Syd

            IPmtNPer

File/Directory Functions:

PV

Vdb

            ChDir(VBA)                             Dir(VBA)

FileLen(VBA)

MkDir(VBA)

             ChDrive(VBA)                          FileDateTime(VBA)

GetAttr(VBA)

SetAttr(VBA)

           

Miscellaneous Functions:

Test a string for an alphanumeric value

Convert currency into words

How to Autonumber in Excel

Delimit values with Ascii 29 character

Creating a custom round function

Creating a custom average function (that excludes outlyers from the calculation)



1