How to Use Excel Effectively

Learning Excel
1. Open the program, and from the Help menu (the blue '?' button), access office.microsoft.com. You'll be asked to identify or let Microsoft identify the installed versions of Office products on your system. A browser will open.
2. Select the 'Tutorial' option on the help screen, which now displays in the Internet browser.
3. Select the first tutorial offered, explaining the differences between Excel 2000, 2003 and Excel 2007 and 2010. Follow onscreen instructions and lessons. Learning the improvements to the newer versions is key to efficiently using Excel.
Options
4. Select 'Options' from the Office Button in Excel 2007 and the File tab in Excel 2010 at the bottom of the window and make changes to the default options to make the basic program operation as personally efficient as possible for its regular use. In both versions, the Quick Access Toolbar can be customized to place buttons for commonly used commands. In Excel 2010, the Ribbon can also be customized.
5. Select 'Backup file when saving' In the 'Save' tab. This is unchecked by default. Back-up files are critically important, especially if converting from Excel 2000 or 2003 to the new versions. When the active workbook is saved, the older copy will be renamed with an 'wbk' file extension, allowing the file to be used again if the 'xlsx' file becomes corrupted or damaged.
6. Customize the Quick Access Bar and the Ribbon with commands used frequently. The Quick Access Bar, if placed below the ribbon (default is above ribbon) and customizing options resembles the tool bars that would have been used in earlier versions of Excel.

Read more ►

How to Skip Printing Rows That Have a Cell Value of Zero in MS Excel 2003

1. Open the Excel file you want to print.
2. Select 'Data' from the menu bar, then select 'Filter' and follow the right arrow to select 'AutoFilter' from that menu. Each of your column headings will now have buttons with down arrows next to them.
3. Click on the arrow button in the column that contains zeroes you want to suppress. For example, let's say your spreadsheet lists employee names in column A and the number of hours they worked in column B. You don't want to print any rows where the employee worked zero hours. You would click on the column B arrow.
4. Click on '(Custom...)' from the drop-down box to bring up the Custom AutoFilter setting. In the drop-down box under your column B heading, select 'does not equal' and in the empty drop-down box to the right of it, select '0.'
5. Click 'OK.' Your list is now filtered and rows with zeroes are hidden.
6. Print your document.

Read more ►

How to Convert True

1. Click the 'Developer' tab at the top of an Excel worksheet. Then click the 'Insert' drop-down menu in the 'Controls' group.
2. Click the check box image in the 'Form Controls' section of the drop-down menu. This changes your mouse pointer to a cross.
3. Click anywhere in the worksheet to insert the check box with a description next to it.
4. Right-click the description next to the check box, then left-click the description until you see a blinking text cursor.
5. Press 'Backspace' or 'Delete' to erase the description that is automatically shown. Then type your own description. Click outside the description area in a blank cell when you are finished. For example, type 'Sales quota met?' as a description.
6. Right-click the check box, and click 'Format Control' on the pop-up menu to bring up a 'Format Control' window.
7. Click the 'Control' tab at the top of the window.
8. Click in the 'Cell link' text box and type the cell to which you want to link the check box. For example, click in the cell link text box and type 'A1.' Cell A1 will now show either 'TRUE' or 'FALSE' depending on whether the check box is checked or unchecked.
9. Click 'OK' to close the 'Format Control' window.

Read more ►

How to Make Bingo Cards in Excel

1.
Open a new, blank Excel worksheet. Select cells 'A1' through 'E6.' Use your mouse by clicking on the first cell and dragging down and across while holding the mouse button, or use your keyboard by clicking inside the first cell, holding down the 'Shift' key and using the arrow keys on your keyboard.
2.
Click the 'Border' dropdown, which is on the 'Formatting' toolbar in Excel 2003 or earlier and on the 'Home' tab of the ribbon in the 'Font' group in Excel 2007. Click the 'All Borders' option to apply borders to all of your selected cells.
3.
Keep the cells selected and change the height and width of the cells. In Excel 2003 or earlier, go to the 'Format' menu, point to 'Row' and choose 'Height.' In Excel 2007, go to the 'Format' dropdown from the 'Home' tab and select 'Row Height.' Enter '90' for the height and click 'OK.' Go back and select 'Column Width,' enter the number '15' and click 'OK.'
4.
Center the cell selection on your spreadsheet. In Excel 2003 or earlier, click 'File' and go to 'Page Setup,' and then click the 'Margins' tab. In Excel 2007, go to the 'Page Layout' tab of the ribbon, click on 'Margins' and choose 'Custom Margins.' Select 'Center on Page Horizontally' and 'Center on Page Vertically,' and then click 'OK.'
5.
Click on cell '4C.' Go to the 'Insert' menu in Excel 2003 or earlier, point to 'Picture' and select 'AutoShape.' In Excel 2007, go to the 'Insert' tab and select 'Shapes.' Choose a shape for the 'Free' space and insert it into cell '4C.' Right-click the shape and choose 'Add Text,' then type the word 'Free.' Right-click again and choose 'Format AutoShape.' Add color to the shape on the 'Colors and Lines' tab, and go to the 'Font' tab to change the font, if desired.
6.
Select cells 'A1' through 'E1.' Right-click and choose 'Format Cells.' Go to the 'Font' tab and choose a large, bold font size and color, such as 'Arial Rounded Bold,' size '24' in red. Go to the 'Alignment' tab and select 'Center' in both the 'Horizontal' and 'Vertical' dropdowns. Click 'OK.' Enter an upper case 'B' into cell 'A1,' 'I' into 'B1' and so on, entering the word 'BINGO.'
7.
Save the bingo card template. When you are ready to use it, enter the numbers, letters, words or symbols you want to use in random order, making each card unique. Print them out on cardstock or heavy paper.

Read more ►

How to Run a Pivot Table

1. Click and hold the mouse button over the top-left cell in the data that you want to create a table out of. Drag the mouse to the bottom-right cell of the data and release the mouse button.
2. Select the 'Insert' tab at the top of the screen. Then click the 'PivotTable' button located at the left side of the toolbar. Click 'OK' in the window that opens up. You will be taken to a new page that contains your PivotTable.
3. Place a check mark next to the fields in the field list that you want to compare. The field list is the window to the right of the Excel sheet. It contains one long space for your different fields, and four smaller ones that correspond to parts of the PivotTable. When you place the check marks, Excel will move the field into one of the smaller boxes and your table will start to take shape.
4. Drag and Drop the fields into the boxes where you want them. The 'Row Labels' box will put the data in a column on the left side of the PivotTable. The 'Column Labels' box will put the data in a row across the top. The 'Values' box will put the data into the middle of the PivotTable, sorting it based on the row and column labels. The 'Report Filter' box will allow you to filter the entire PivotTable by the fields in the box. Use the labels to set up your data, and put the data that you want to analyze in the 'Values' box.
5. Click on the field in the 'Value' list and choose 'Value Field Settings.' You can now change how the PivotTable will report the data form that field. It defaults to a count, essentially a '1' if there is data and a '0' if there isn't. You can change it to 'Sum,' where it will add the values together, or several other mathematical functions.
6. Use the 'Refresh' button in the toolbar if your original data has changed. Excel essentially takes a snapshot of the data when it creates the PivotTable. If you change the data, you have to refresh the PivotTable before you will see the changes. Likewise, if you need to change the columns in your original data field, press the 'Change Data Source' button and you can choose a new data set.
7. Click on the blue question mark in the upper-right corner to access the Microsoft Online Help for Excel 2010. From there you can learn about all the other features and functions of PivotTables. While the majority of what you will do in PivotTables will involve simply move fields around, as you get more advanced there are a ton of options to make more complicated tables.

Read more ►

How to Fix Circular Formulas in Excel

How to Fix Circular Formulas in Excel--Directly Referenced Cell
1.
To demonstrate this type of circular formula and the resolution of the problem a simple bank reconciliation spreadsheet is used. Set up an Excel spreadsheet as follows: in row 1 label Column A: Balance, Column B: Debit, Column C: Credit, Column D: To, Column E: Reference, Column F: Date and Column G: Cleared Bank.
2.
In row 2 enter: a $500 credit in the 'Credit' column, 'Deposit to open account' in the 'To' column, and a date in the 'Date' column. In the 'Balance' column on row 3 enter the following formula: =A3 C2-B2, with the intent that this formula would take the prior balance, add any deposits and subtract any debits. When you enter this formula you will get an error message stating that a circular reference has been created. You can either select 'OK' or 'Help' to fix the reference yourself or 'Cancel' to ignore the circular reference. If you select 'OK' or 'Help' a pop-up window will open that will give you more information about circular references.
3.
In addition to the pop-up window that provides more information about circular references a toolbar opens when you select 'OK' or 'Help.' This is the Circular Reference toolbar. If you select the drop-down arrow on the toolbar it will show all circular references in the current workbook. You can go directly to the circular reference by selecting it from the drop-down list.
4.
Fixing the circular reference can be done by first selecting the cell and then placing your cursor in the formula bar. This color codes the formula to the actual cells on the worksheet that are referenced in the formula. In this example, you can see that the cell A3 references itself in the formula.
5.
The algebraic equivalent to this would be the formula: x = x y - z, which would only hold true in one instance, where y and z = zero. To correct this formula, change the formula in A3 to reference cell A2 instead of cell A3. This can be done by typing directly into the formula bar, dragging the colored border that is around cell A3 so that it is around cell A2 or retyping the formula.
How to Fix Circular Formulas in Excel--Indirectly Referenced Cell
6. To demonstrate indirectly referenced circular formulas, we have simply added a column to the practice worksheet and labeled it 'Interest Paid.' In row 2 of that column enter the following formula: =A3*0.03, with .03 representing interest paid on the balance. In row 3 in the Balance column enter the following formula: =A2 C2-B2 D2. The algebraic equivalent to this would be the formula: x = w y z (x *.03).
7.
When you enter this formula you will get an error message stating that a circular reference has been created. You can either select 'OK' or 'Help' to fix the reference yourself or 'Cancel' to ignore the circular reference. If you select 'OK' or 'Help' a pop-up window will open that will give you more information about circular references.
8.
In addition to the pop-up window that provides more information about circular references a toolbar opens when you select 'OK' or 'Help.' This is the Circular Reference toolbar. If you select the drop-down arrow on the toolbar it will show all circular references in the current workbook. You can go directly to the circular reference by selecting it from the drop-down list. Since this is an indirectly referenced circular formula an arrow will point from the cell that has the circular reference back to the cell that refers to that cell. In this example, the arrow points from A3 back to D2, indicating that A3 refers to D2 for its calculation and that D2 refers to A3 for its calculation.
9. To correct this formula, change the formula in D2 to reference cell A2 instead of cell A3. This can be done by typing directly into the formula bar, dragging the colored border that is around cell A3 so that it is around cell A2 or retyping the formula.

Read more ►

How to Change Hyperlink Font in Excel 2003

1. Run Excel 2003 and open the spreadsheet that contains the hyperlink you wish to edit.
2. Click the cell above the cell that contains your hyperlink, then press 'Enter' to move down to your hyperlink cell. Doing so allows you to select the hyperlink cell without clicking on it and opening the link in a Web or file browser.
3. Click 'View' in the File menu, then click 'Toolbars' and 'Formatting' to activate the text formatting toolbar. Skip this step if the text formatting toolbar is already activated.
4. Click the 'Font' drop-down menu located in the Formatting toolbar and select the font that you wish to use for the hyperlink.
5. Click the 'Font Size' drop-down menu to choose the hyperlink's font size.
6. Click the 'Bold,' 'Italic' or 'Underline' icons to apply font formatting to the hyperlink.

Read more ►

How to Import Data From Multiple Spreadsheets

1. Open a new blank workbook. Begin the installation process by clicking the 'file' tab and selecting 'Options.' Select 'Customize Ribbon' in the 'Excel Options' dialog.
2. Select 'All Commands' in the 'Choose commands from:' drop-down menu. In the left-hand pane, select 'Compare and Merge Workbooks' and click 'Add.' You will be given the option of which ribbon you want to place the new function. Click 'OK.'
3. Put all of the worksheets and workbooks you want to merge into one folder in your hard drive. Locate the 'Compare and Merge Workbooks' function, which will have been saved in the ribbon of your choosing. Click it, and then select which workbooks you want to merge.

Read more ►

How to Delete Links in Excel 2003

1. Launch Microsoft Excel 2003. Click on 'File' and select 'Open.' Locate the Excel file on your computer that contains links you want to remove. Double-click the file to open it in Excel.
2. Press the 'Alt' key and 'F11' key on your keyboard simultaneously. The visual basic editor will appear.
3. Click on 'Insert' and select 'Module.' A new window will appear, allowing you to input visual basic coding.
4. Copy and paste the following code into the editor:Sub RemoveHyperlinks()'Remove all hyperlinks from the active sheetActiveSheet.Hyperlinks.DeleteEnd Sub
5. Click on 'File' and select 'Close and Return to Microsoft Excel.' The visual basic editor will close.
6. Click on 'Tools' in Microsoft Excel and hover over 'Macro.' Scroll over and click on 'Macros.' A dialog box will appear listing all saved Macros for Excel 2003.
7. Select 'RemoveHyperlinks' from the list of macros and click 'Run.' Excel will run the macro and automatically delete all hyperlinks from the spreadsheet.

Read more ►

How to Rotate to Landscape in Excel Charts

1. Click on the vertical axis.
2. Click on the 'Format' tab under Chart Tools.
3. Click 'Format Selection.' Make sure that the vertical axis is displayed in the selection box. If it isn't, click on the axis.
4. Click on the 'Categories in reverse order' box. This reverses the order of categories and places the vertical values in the horizontal axis and vice versa.

Read more ►

How to Apply Significant Figures in Excel

1. Double-click the Microsoft Excel 2010 file icon to which you want to apply significant figures to open it. Alternatively, to open a new Excel document, skip to the next step, else go to step 3.
2. Click the 'Start' button on the taskbar, point to 'All Programs,' and then click 'Microsoft Excel 2010.'
3. Press and hold the 'Ctrl' key on your keyboard and then one-by-one click in the cells to which you want to apply significant figures. This will select the cells you click. To select all the cells in the document, press 'Ctrl A'.
4. Right-click in one of the selected cells and then click 'Format Cells.'
5. Click the 'Number' tab on the Format Cells dialog box that opens.
6. Click 'Number' below Category, type the number of significant figures you want in the 'Decimal places' box (for example 6), and then click 'OK.'

Read more ►

How to Import Excel Tables Into MS Word

1. Highlight and copy the content in Excel that you wish to embed in the Word document.
2. Switch to the Word document.
3. Place the cursor where you want the imported data.
4. Click the arrow under 'Paste' in the 'Clipboard' segment of the 'Home' menu. Choose 'Paste Special' from this menu.
5. Click the circle next to 'Paste Link.' Highlight 'Microsoft Office Excel Worksheet Object' in the box that says 'As' and click 'OK.'
6. Press 'Alt' and 'F9' at the same time. This turns on the field code display. The embedded link should appear something like this: {LINK Application 'File Path' [Range] [Switches]}
7. Modify switches if desired. Switches control options for automatic or manual updating and formatting. For example, choosing \A tells Word to automatically update the linked field. The full list of switches is available in Reference 1.

Read more ►

How Do I Create a Polygon Frequency Graph Using Excel?

1. Highlight the frequency data in the worksheet. For example, if your data is in cells A2 to A10, highlight these cells by left-clicking in A2 and dragging the cursor down over the cells.
2. Click on the 'Chart Wizard' and choose 'Line > Line With Markers.' Click 'Next.'
3. Click on the 'Series' tab.
4. Click on 'Category (X) axis labels' and then click the red mark.
5. Highlight the cells containing your midpoints. For example, if your midpoints are in cells B2 to B10, highlight cells B2 to B10.
6. Click the red marker again to return to the Chart Wizard, and then click Next. This will create the polygon frequency graph.

Read more ►

How to Create a Map in Excel

1. Download or scan a starter map image. Many free maps are available for download at www.enchantedlearning.com/USA/states. Right-click any map and choose the 'Save As' command to place the map on your computer.
2. Launch Excel. Select the entire worksheet by clicking in the selection box located between the column A and row 1 labels in the upper left of the spreadsheet.
3. Click the 'Format' menu and choose the 'Column' submenu. Select the 'Width' command. A pop-up window will appear.
4. Type the number '2.0' into the column width field and press the 'OK' button.
5. Select the 'Format' menu again. Choose the 'Sheet' submenu. Click on the 'Background' command. A pop-up window will appear.
6. Browse to the location of the saved map image you downloaded or scanned. Click on the name of the map image file and press the 'Insert' button.
7. Click on the 'Tools' menu. Select the 'Options' command. A pop-up window will appear.
8. Click the 'View' tab of the pop-up window.
9. Uncheck the following boxes: 'Row column headers,' 'Vertical scroll bar,' 'Horizontal scroll bar' 'and 'Gridlines.' This will ensure that the map image appears clean without interference from the spreadsheet grid.

Read more ►

How to Select Multiple Sheets in Excel

1. Launch Excel and open the Excel workbook with which you want to work.
2. Click the tab for the first sheet with which you want to work. Sheet tabs are located at the bottom of the Excel workspace. If you have not renamed your sheets, they will have names like 'Sheet1' and 'Sheet2.' Sheet tabs turn from blue to white when they are selected.
3. Press the “Ctrl” key on your keyboard and click additional sheet tabs to select those sheets.

Read more ►