Free Microsoft Office Tutorials | Online Office Learning Guide | Microsoft Office Tips and Tricks

Using Arithmetic Formulas

Arithmetic formulas are by far the most common type of formula. They combine numbers, cell addresses, and function results with mathematical operators to perform calculations. Following Table summarizes the mathematical operators used in arithmetic formulas.

Table ::	The ArithmeticOperators
Operator	Name	Example	Result
+	Addition	=10+5	15
–	Subtraction	=10-5	5
–	Negation	=-10	–10

* Multiplication =10*5 50

/ Division =10/5 2

% Percentage =10% 0.1

^ Exponentiation =10^5 100000

Most of these operators are straightforward, but the exponentiation operator might require further explanation. The formula =x^y means that the value x is raised to the power y. For example, the formula =3^2 produces the result 9 (that is, 3*3=9). Similarly, the formula =2^4 produces 16 (that is, 2*2*2*2=16).

Using Comparison Formulas

A comparison formula is a statement that compares two or more numbers, text strings, cell contents, or function results. If the statement is true, the result of the formula is given the logical value TRUE (which is equivalent to any nonzero value). If the statement is false, the formula returns the logical value FALSE (which is equivalent to 0). Table 3.3 summarizes the operators you can use in comparison formulas.

Table ::	Compariso Formula Operators
Operator	Name	Example	Result
=	Equal to	=10=5	FALSE
>	Greater than	=10>5	TRUE
<	Less than	=10<5	FALSE
>=	Greater than or equal to	=“a”>=”b”	FALSE
<=	Less than or equal to	=”a”<=”b”	TRUE
<>	Not equal to	=”a”<>”b”	TRUE

Comparison formulas have many uses. For example, you can determine whether to pay a salesperson a bonus by using a comparison formula to compare actual sales with a predetermined quota. If the sales are greater than the quota, the rep is awarded the bonus. You also can monitor credit collection. For example, if the amount a customer owes is more than 150 days past due, you might send the invoice to a collection agency.

Using Text Formulas

So far, I’ve discussed formulas that calculate or make comparisons and return values. A text formula is a formula that returns text. Text formulas use the ampersand (&) operator to work with text cells, text strings enclosed in quotation marks, and text function results. One way to use text formulas is to concatenate text strings. For example, if you enter the formula =“soft”&”ware” into a cell, Excel displays software. Note that the quotation marks and the ampersand are not shown in the result. You also can use & to combine cells that contain text. For example, if A1 contains the text Ben and A2 contains Jerry, entering the formula =A1&” and ” &A2 returns Ben and Jerry.

Using Reference Formulas

The reference operators combine two cell references or ranges to create a single joint reference.

Table 3.4 summarizes the operators you can use in reference formulas.

Table :: Reference Formula Operators

: (colon)	Range	Produces a range from two cell references (for example, A1:C5)
(space)	Intersection	Produces a range that is the intersection of two ranges (for example, A1:C5 B2:E8)
, (comma)	Union	Produces a range that is the union of two ranges (for example, A1:C5,B2:E8)

Popularity: 2% [?]

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.

Popularity: 11% [?]

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 column headings	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.

Popularity: 11% [?]

• · 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.

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.

Popularity: 11% [?]

• · 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 button.Note: 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.

Popularity: 11% [?]

• · 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.

Popularity: 11% [?]

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.

Popularity: 11% [?]

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.

Popularity: 11% [?]