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 ►

How to Use Borders in Microsoft Excel

Instant Borders
1. Launch Microsoft Excel and create or open a workbook with some information entered into some cells.
2. Click in a cell that contains information to highlight the cell.
3. Click the 'Home' tab on the tool bar.
4. Click the small pull down arrow to the right of the 'Border' button in the 'Font' group.
5. Click on the desired border style to instantly apply the selected border to the currently highlighted cell. Alternately click on 'No Border' to remove the existing border from the cell, or click on one of the more advanced alternatives from the 'Draw Borders' section of the pull down menu.
Format Cell Borders
6. Click on a single cell to select that cell, or click on a cell and hold the left mouse button down while dragging the mouse across a range of cells and then release the mouse button to select the entire range of cells that is highlighted.
7. Click the small symbol in the lower right corner of the 'Fonts' menu on the 'Home' tab. Alternately right-click on any one of the selected cells and click 'Format Cells' or hold down 'CTRL' and 'Shift,' then press the letter 'F' once and release all keys. This will bring up the 'Cell Properties' dialog box. Click the 'Border' tab.
8. Click on the desired line style for the border. Click on the color pull down box and select a color.
9. Click on the various border buttons along the outside of the preview pane to show what the border would look like if chosen. Click the same button again to toggle the border item off. Click 'OK' to save the settings and apply the border to the work book.

Read more ►

How to Create a Simple Drop

1. Click on the cell in the workbook where you want your drop-down list to appear.
2. Select the 'Data' tab. From the 'Data Tools' menu, click 'Data Validation.' The Data Validation dialog will appear.
3. From the 'Settings' tab, click the 'Down Arrow' and select 'List.'
4. For a simple list, type the items in the Source box in the order you want them to appear in the drop-down, separating them by a comma. Click 'OK.'
5. At the insertion point, click the 'Down Arrow' to display the options, clicking on an option to select it.

Read more ►

How to Change an Active Cell to Another Cell in Excel 2003

1. Open a new or existing file in Microsoft Excel.
2. Click the 'Tools' menu. Click 'Visual Basic Editor' to open the VBA coding editor. Click the 'Insert' menu item and select 'Module.' This option opens a module that 'attaches' to the Excel spreadsheet. The module is the VBA code that runs in the spreadsheet.
3. Type the following code in the module editor:Range('A1').SelectIn this example, the 'A1' cell is activated. Replace this value with your own cell you want to activate. You can also activate a range of cells. For instance, the following code activates cells from A1 to C1:Range('A1:C1').Select
4. Click the 'Close' button to close the VBA editor. Click 'Save' in the Excel toolbar to save the changes to the spreadsheet.

Read more ►