January 2012 - Excel

Browse » Home » Archives for January 2012

Saturday, January 28, 2012

How to Create a Custom Sort in MS Excel

1. Open the Excel worksheet.
2. Click and drag to select the range of cells you wish to custom sort.
3. Click the 'Home' tab on the command Ribbon.
4. Click the 'Sort Filter' button in the 'Editing' group. A list of options appears.
5. Click the 'Custom Sort' option. A dialog window opens. The categories include 'Column,' 'Sort On' and 'Order'
6. Select or deselect the check box for 'My data has headers.'
7. Click the arrow next to the 'Sort by' text box in the 'Column' category. Select the 'Column.'
8. Click the arrow in the text box under 'Sort On.' Select the preferred option, such as values or cell color.
9. Click the arrow in the text box under 'Order.' For example, 'Smallest to Largest' for values, or 'A to Z' for text.
10. Click the 'Options' button. A small dialog window opens. Select the orientation for sorting, such as 'Sort left to right' or 'Sort top to bottom.' Click 'OK.' This window closes.
11. Click 'OK.' The 'Sort' window closes. The range of cell contents display the new order.

How to Calculate Fractions in Excel 2007

1. Open the Excel spreadsheet you want to use to calculate fractions.
2. Click an empty cell and enter a number in decimal format, for example, '1.875.' Press 'Enter.' The number should show up exactly as you entered it.
3. Click the cell again and then click the 'Home' tab on the menu ribbon. Click the down arrow in the text formatting window in the 'Number' group. The text formatting window might display 'General' initially or something else. Click 'Fraction' from the drop-down list. The number displays as a fraction now.
4. Add, subtract, multiply, divide or otherwise create any equation using fractions in the formula bar. For example, to add two fractions, enter '=1/2 1/4.' Excel will display '3/4' in the cell if the cell is formatted as a fraction.

How to Lock Column Headers on Microsoft Excel

1. Open a worksheet in Microsoft Excel. Add row and column header text as desired.
2. Click on the column letter on the right side of the column you want locked. This highlights the entire column. Highlight a group of columns by clicking on the first column and dragging the mouse, with the left button depressed, across the desired columns to highlight.
3. Click on the 'View' tab if using Microsoft Excel 2007. Under the Window group item in the menu, click on the arrow for 'Freeze Panes.' Select the option to 'Freeze First Column' to lock only the one column highlighted. Select the option to 'Freeze Panes' if you want to freeze a group of panes at one time.
4. Click on the 'Window' menu if using Microsoft Excel 2003 and select 'Freeze Panes' to lock one or a group of columns.
5. Repeat the process listed in steps 1 through 4 to unlock the column by selecting 'Unfreeze Panes.'

How to Make a Histogram From a List of Data in Excel

1. Open a new Microsoft Excel 2010 spreadsheet. Select cell 'A1' and type in 'Input Range.' Select cell 'B1' and type in 'Bin Range.'
2. Click on cell 'A2' and type in the first value in your data set. Enter the rest of the data set into the cells down the first column.
3. Select cell 'B2' and enter your bins down the second column. Each actual bin number will stand for the top value in that bin's range. So, if you enter bins of '20,' '40' and '60,' the ranges will be anything below 20, 21 to 40, and 41 to 60, respectively. Excel will automatically add an additional bin called 'More,' which will collect every value higher than your highest bin number. The More bin will appear even if your highest bin is equal to or greater than your highest value. In addition, you have the option of not entering any bins and allowing Excel to automatically determine your bins.
4. Click the 'File' tab at the top of the screen and click 'Options' from the menu that appears on the left side of the Excel window. Click on 'Add-Ins' on the left edge of the Options window, then click the 'Go' button. Click the check box next to 'Analysis ToolPak' and click 'OK.' This will load the add-on that is required to create a histogram. You will only need to load this add-on once, as Excel will now load it automatically.
5. Select the 'Data' tab and click the 'Data Analysis' button. Select 'Histogram' and click 'OK.' The Histogram window will appear.
6. Click the radio button next to 'Output Range' and then click on cell 'C1.' Click the check box next to 'Chart Output.' This process will begin the histogram table at cell 'C1' and create the actual histogram to the right of the table on the spreadsheet.
7. Place your cursor into the 'Input Range' field and press 'Backspace' to erase whatever was in the box. Click and hold the mouse button over cell 'A1' on your worksheet, then drag the mouse down until you reach the last value in your data set and release the mouse button. If you entered your own bins on the worksheet, repeat this process with your bins in the 'Bin Range' field.
8. Click the check box next to 'Labels' as your data has labels in the first row, then click 'OK.' Excel will display your histogram.

How to Unlock an Excel Workbook

1. Open the Excel workbook you want to unlock.
2. Select 'Tools,' 'Protection' and 'Unlock Workbook' from the toolbar. If you added a password initially (when you locked the file), you will be prompted to type it in. Click on 'OK' in the 'Unprotect Workbook' box. The workbook will be unlocked.
3. Select 'Tools,' 'Protection' and 'Unprotect Shared Workbook' from the toolbar to unlock a shared workbook. Enter the password into the 'Unprotect Sharing' box if you inserted one initially. Then click on 'OK.' Click on 'Yes' to remove the workbook's shared settings if you are prompted to.
4. Select 'File' and 'Save As' from the toolbar to remove the password from a password-protected workbook file. Click on 'Tools' and 'General Options' in the 'Save As' dialog box. Remove the password from the 'Password to open' field (to allow users to open it) and/or 'Password to modify' field (to allow users to modify it) and click 'OK.' Click on 'Save' to save the workbook without a password.

Excel 2003 Menu Bar Tutorial

1. Click 'File' to view options that are related to file operations. You can create new files, open files, save files, print files and change file properties from this menu.
2. Click 'Edit' to view common options for performing Excel edits. This menu contains options that allow you to copy and paste data, find and replace text strings and delete cells or cell contents.
3. Click 'View' to change the Excel 2003 display. You can use this menu to change the Excel toolbars, document headers and footers and change the screen view.
4. Click 'Insert' to add something new to your document. You can use this menu to add blank cells, columns or rows. You can also add formula functions and graphics to a cell via this menu.
5. Click 'Format' to change the appearance of elements in your document. You can change the format of individual cells, rows, columns and entire worksheets using the functions under this menu heading.
6. Click 'Tools' to access a menu of tools that you can use to augment the contents of your workbook. The Tools menu includes entries for spell-checking, sharing your document and protecting cells with a password. The menu also contains entries for changing Excel options and editing the Excel AutoCorrect feature.
7. Click 'Data' to perform categorical edits on your worksheet's data. You can sort data, create forms and filters, insert pivot tables and import external data to your spreadsheet.
8. Click 'Window' to change the active window, freeze panes in your spreadsheet or split your view of the spreadsheet into multiple parts.
9. Click 'Help' to view Excel 2003 help documentation.
10. Click the 'Double Arrow' icon at the bottom of any menu to view all menu items. When you first click on the menu, the menu only displays items that you have used recently.

How to Compare Two Lists in Excel 2007

1. Open a new workbook (click the 'Office' button, 'New' and 'Blank Workbook') as well as the two lists you intend to compare. In the new spreadsheet, make headers across the top: 'Original List,' 'Comparison List' and 'Missing?'
2. Copy and paste the original list into the new spreadsheet. Move over a column and paste the comparison list into the spreadsheet.
3. Move your cursor to cell C2, the first empty cell in the 'Missing?' column. Type the function into this cell. Type in '=isna(match(' then either click the first cell in the 'Comparison' list or type B2 (if that's the cell number). Type a comma, then either click and drag to select the whole 'Original List' or type the range in--this might be something like 'A2:A18.' Type in another comma, then the word 'FALSE.' Finally, type two closed parentheses. The whole function should look like =isna(match(B2,A2:A18,false)).
4. Press 'Enter.' The function should return a 'TRUE' if the item in cell B2 does not appear in the list under column A, and 'FALSE' if it does.
5. Click to select the cell with the function in it. Change the cell range of the 'Original List' so there are two dollar signs in it. It should now read A$2:A:$18. These dollar signs will prevent Excel from changing that range in the next step.
6. Press 'Enter' again, then click and select the function again. Click the small black fill handle on the bottom-right corner of the cell and drag that cell down to the last row in the 'Comparison List.''
7. Look at the list you just created. If the lists are similar, most of the items under 'Missing?' should say FALSE.' Any items marked 'TRUE' don't appear in the 'Original' list.

How to Copy Paste Filtered Data

1. Drag your mouse over the filtered data so it is highlighted. This identifies to the program which data you wish to copy.
2. Press the 'F5' button on your computer keyboard, if you are using Excel 2007 or later. A pop-up window will appear. Alternately, you can click the 'Go To' button in the 'Find Select' section of the Excel toolbar. This step is not necessary for versions of Excel prior to 2007.
3. Click the 'Special' button on the pop-up window.
4. Click the 'Visible cells only' option, and press 'OK'. This instructs Excel to only select the data included in the filtered results. Hidden data from the original unfiltered data set is ignored. You are now ready to copy.
5. Press the Control-C keyboard combination to copy the data. Alternately, select the 'Copy' button on the Excel toolbar.
6. Click on the spreadsheet, file, or other document where you wish to paste the filtered data.
7. Press the Control-V keyboard combination to paste. The filtered data is pasted into the new file.

Friday, January 27, 2012

How to Copy a Microsoft Excel Sheet

Excel 2007
1. Click on the tab of the worksheet you want to copy.
2. Right-click with your mouse on the tab, then choose 'Move or Copy.'
3. Select where you want the worksheet to go. Use the drop-down menu to select a workbook.
4. Click on the check box next to 'Create a Copy.'
5. Click on 'OK.'
Excel 2003
6. Open the worksheet you want to copy.
7. Click on 'Edit,' then click on 'Move or Copy worksheet.'
8. Click on 'Create a Copy' in the dialog option box.

How to Format Number Currency in Excel 2003

1. Click 'Start' in Windows, select 'Microsoft Office' and then click 'Microsoft Office cell 2003.'
2. Click the cell that contains the number you want to display in the currency format.
3. Click 'Cells' on the 'Format menu.'
4. Click 'Currency' in the 'Category' box on the 'Number' tab.
5. Click the currency symbol you choose in the 'Symbol' list.
6. Enter the number of decimal places that you want to display in the 'Decimal Places.'
7. Select the display style for negative numbers in the 'Negative Numbers'.
8. Click 'File' and then click 'Save' to apply the change to your Excel spreadsheet.

How to Draw a Histogram in Excel 2007

1. Type the word 'Bins' into cell A1.
2. Type the word 'Frequencies' into cell B1.
3. Enter the minimum values for each histogram bucket in column A, under the word 'Bins.' For example, in cell A2 begin with '0.2,' and continue down the column with increasingly large numbers.
4. Enter the number of data items that belong in each given bin in column B, under the word 'Frequencies.' For example, if the first two bins are 0.2 and 0.3, and there are 12 data items that fall between the values 0.2 and 0.3, then type '12' to the right of 0.2 in column B.
5. Select all the data (including the header title) in column B. To select it, left-click on the title, and drag the mouse to the bottom of the list.
6. Click the 'Insert' ribbon.
7. Click the 'Column' button in the Charts section of the Insert ribbon.
8. Click the first icon on the top left of the Column drop-down. It looks like a flat bar graph.

How to Design a Crossword Puzzle With Excel 2003

Designing the Crossword Puzzle
1. Open a new document in Microsoft Excel 2003.
2. Select the area of your spreadsheet where the crossword puzzle will go. (Make sure to keep this same area highlighted throughout all of the steps in this first section.) Then right-click over the selected area and choose 'Format Cells' from the pop-up list.
3. Select the 'Alignment' tab. Then choose 'Right Indent' for the horizontal text alignment and 'Top' for the vertical text alignment.
4. Select the 'Font' tab. Then pick 'Arial' for the font, '10' for the font size and 'Superscript' for the effects and click 'OK.'
5. Select 'Format,' 'Row' and 'Height' from the toolbar. Type '20' into the 'Row Height' dialog box and click 'OK.'
6. Select 'Format,' 'Column' and 'Width' from the toolbar. Type '4' into the 'Column Width' dialog box and click 'OK.' The selected cells should now be square.
7. Select the 'Border' button from the 'Formatting' toolbar. Choose 'All Borders' to create a border around each selected cell.
Adding Content
8. Place your cursor in the cell where you want to add a number. Type in the number you want. Then press 'Enter' or use your arrow keys to move to the another cell in the crossword.
9. Insert additional numbers into the desired cells until they are all in.
10. Select any cells within the crossword puzzle that you want to shade (areas where letters/words will not be entered when printed). To select multiple cells at the same time, hold down the 'ctrl' key on your keyboard as you select each cell.
11. Click on the arrow beside the 'Fill (Color)' button in the 'Formatting' toolbar and choose black. The selected areas will be shaded.
Inserting the Clues
12. Place your cursor outside the crossword puzzle (below it) to insert the clues for the crossword puzzle. Type in 'ACROSS' and bold the text by using the 'Bold' button in the 'Formatting' toolbar. Then press 'Enter.'
13. Type in the number '1' and your first clue for the 'ACROSS' category and press 'Enter.' Continue typing in all of the other clues until you are finished.
14. Next to the Across clues, enter the Down clues in a different column. Type in the word 'DOWN' and bold it. Then press 'Enter.'
15. Type in each number and clue for the 'DOWN' category, pressing 'Enter' after each one. Continue typing in other numbers and clues until they are complete.
16. Save your work by selecting 'File' and 'Save' from the toolbar. Name your crossword puzzle file and click 'Save.'

How to Use a Reference in a Different Worksheet in Excel

1. Open the Excel workbook with multiple worksheets that you want to use.
2. Click the tab at the bottom of the Excel window to select the worksheet in which you want to enter a formula.
3. Click an empty cell to insert a formula. Type an equal sign, an Excel function and an open parenthesis to begin building your formula. For example, if you want to add numbers from different worksheets, enter:=SUM(
4. Enter a cell number or another function from the current worksheet you want to work with. For example, if you are adding a number from cell A2 in the first worksheet to a number in cell A2 in the second worksheet, click or type cell 'A2.' Follow that with an operator. For example:=SUM(A2
5. Click a different worksheet tab at the bottom of the Excel application window. Click the cell you want to add to the formula next or type the cell address. Excel will insert the name of the worksheet you are referencing along with the cell number. For example:=SUM(A2 Sheet2!A2
6. Type a closing parenthesis when you are finished building the formula and press 'Enter.' Excel will return you to the original worksheet. Your complete formula might look like this:=SUM(A2 Sheet2!A2)

How to Work With Pivot Tables in Excel 2003

1. Open the Excel 2003 file that contains the data you want to work with.
2. Ensure that each column of data has a header at the top. If you do not have a header row, you can right-click the number on the left side of your data's top row and choose 'Insert' from the pop-up menu. Type headers for each column into the new row. You also need to remove any subtotal cells from the middle of the data field, as the PivotTable can't handle these types of cells.
3. Click the 'Data' menu at the top of the screen and choose 'PivotTable and PivotChart Report.' This launches the PivotTable wizard. Select the 'Next' button on the first screen of wizard.
4. Click the small box next to the 'Range' field and the wizard will minimize. Select the data that you want to include in the PivotTable. Click the top-left cell of your desired data range, then hold down 'Shift' and click the bottom-right cell. Don't include any subtotal cells on the bottom or the right side of the data area. Click the small box again to bring the wizard back up, and choose 'Next.'
5. Choose between placing the PivotTable on a new worksheet, or an existing worksheet. If you choose to use an existing worksheet, you will have to select the cell where you want the PivotTable to start. The table will expand down and to the right of your selected cell. Click 'Finish' to complete the wizard and create your PivotTable. You will see an empty PivotTable along with a field list that contains the names of all your column headers.
6. Click an item in the field list, then drag and drop it to one of the four areas of the PivotTable. The first area sits just above the actual PivotTable and acts as a global filter for the data in the table. Then, there are the two areas that make up the rows and columns of the PivotTable. Finally, there is a large area in the middle of the PivotTable that comprises the main data for the table. Each area can have multiple fields, as they will just stack onto one another automatically.
7. Rearrange the PivotTable by clicking the name of the field in the PivotTable and dragging that name to a different area. You can also drag and drop the field name off the PivotTable to remove it.
8. Click the arrows next to any of the field names on the PivotTable to filter the report. Remove check marks from any item you want to remove from the table, or add check marks to bring any item back onto the table. By using this feature on the page field area above the PivotTable, you can filter the entire table based on the elements in this list.
9. Double-click any data or subtotal cell in the PivotTable to see a list of all the source data that Excel used to create that number. The list will appear on a new worksheet that Excel adds to the workbook.

Thursday, January 26, 2012

How to Export the Outlook Calendar to Excel

1. Open Microsoft Outlook and allow the main screen to fully load. Click on the 'File' menu at the top of the page and select 'Import and Export' from the subsequent menu. Select 'Export to a file' from the next page and click the 'Next' tab at the bottom of the window.
2. Select 'Microsoft Excel' from the list of programs and click the 'Next' button. Select the 'Calendar' folder from the list of options on the next page and click the 'Next' button at the bottom of the screen.
3. Select a name for the calendar file and type it into the text box. This will be the name of the file used for the calendar in Excel. Click the 'Finish' tab at the bottom of the screen to share the calendar with Excel.

How to Use the Pivot Table Wizard in Excel

1. Start Excel and open the workbook file where you want to create the pivot table.
2. Format your data as straight lists of values. For example, you might have month, type of product, salesperson, or income.
3. Select the data you want in the pivot table.
4. Open the Data menu and select Pivot Table and Pivot Chart Report.
5. Click the radio button in the first section for 'Microsoft Excel list.'
6. Decide if you want just a table for your data, or if you also want charts. Then Click Next.
7. Verify that the range of cells you selected is listed in the Range field.
8. Click Next.
9. Decide whether you want the pivot table in a new or existing worksheet, then click Finish.

How to Turn on Worksheet Gridlines Row Headings Using VBA in Excel 2003

1. Launch Office Excel 2003, click the “Tools” menu, click “Macro” and click “Visual Basic Editor” window. Click the “Tools” menu and click “Project Explorer.”
2. Click “ThisWorkbook module” and copy and paste the following code to hide the gridlines and headings:Private Sub hideGridLines()
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
End Sub
3. Press “F5” to run the procedure.
4. Copy and paste the following code to your module to show the gridlines and headings:Private Sub showGridLines()
ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayHeadings = True
End Sub
5. Press “F5” to run the procedure.

How to Open an MS Excel File Without Excel

1. Locate the Excel file you would like to open on your computer. It may be on your desktop, in a folder or in a portable disk drive. The file extension (portion of the file name after the period) will be '.xls' for spreadsheets created in Excel 2003 and earlier versions. For Excel 2007 and later spreadsheets, the extension will be '.xlsx.'
2. Right-click on the file name if you use a PC or press 'Ctrl' and click on the file name if you use a Mac. This will cause a menu, known as a context menu, to pop up with options.
3. Select 'Open with...' from the context menu. This will open a list of programs you can use to view the spreadsheet. In some versions of Windows, you may need to click 'Choose default program' to get the list of programs.
4. Find the recommended programs at the top of the list. If you have any other spreadsheet programs on your computer, they should be listed here. Microsoft Works, Apple's Numbers, Lotus 1-2-3 and OpenOffice Calc can all open Excel spreadsheets, although you may lose some formatting. If you have any of these programs, just click the program name and the computer will open the spreadsheet in that application.
5. Click 'Cancel' if you do not have any spreadsheet programs on your computer, or if you try the recommended programs and they do not work.
6. Open an Internet browser, such as Internet Explorer, Safari or Chrome, and visit Microsoft.com to download the free Excel Viewer software. (A direct link is listed under this article's Resources section.) This software will allow you to open and view an Excel spreadsheet, but you will not be able to edit the data.

Wednesday, January 25, 2012

How to Rename a Worksheet in Excel 2003

1. On your computer, locate the folder that contains the worksheet you want to rename.
2. Right-click on the worksheet file name. This will summon a small pop-up menu with options.
3. Click the 'Rename' option. A small box will appear around the text.
4. Type in the new name for your Excel 2003 worksheet.
5. Click outside of the text box. This will save the new name.

Tuesday, January 24, 2012

How to Delete Comments in Excel 2007

Deleting Comments
1. Open the Excel workbook and worksheet containing the comment to be deleted.
2. Highlight the cells that contain comments you wish to delete. You may need to hold down the 'Ctrl' key while clicking if you are selecting several cells that are not adjacent to each other.
3. Left-click once on the 'edit' menu at the top of the screen. Then select 'Clear,' followed by 'Comments.'
Deleting All Comments
4. Open the workbook and worksheet containing the comments to be deleted.
5. Left-click on the 'Find Select' button at the top of the screen in the editing tool bar and choose 'Comments.'
6. Left-click 'Clear' and then 'Comments' in the 'Edit' tool bar.

How to Protect Cells From Modification in Excel 2003

1. Open Windows and pull up your Excel spreadsheet. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Highlight the cells to be protected. Choose 'Cells' from the 'Format' menu. Select the 'Protection' tab. Check the box to the left of 'Locked'.
3. Go to 'Tools'. Hold the mouse on 'Protection'. Then click 'Protect Sheet'.
4. Enter a password if you want to use one. If you do not want a password, just click 'OK'.

Monday, January 23, 2012

How to Add a Background Color or Pattern to Cells in Excel 2003

1. Open Microsoft Excel on your computer. You should be able to locate the program by using the 'Start' button. If you can not find it there, use the 'Search' or 'Find' function, or just look for the icon on your desktop.
2. Highlight the cells that you want to emphasize. You can click and drag or click on each cell individually while holding down the 'Ctrl' button. Using the 'Ctrl' button and clicking will allow you to select non-adjacent cells.
3. Look for the tiny paint bucket icon on your tool bar. Use this icon to fill in a cell with a single color. Choose the color by hitting the down arrow on the right of the paint bucket. To select the last color you used or the default color, just click on the paint bucket icon. If you only need one color, you'll be done after clicking 'OK.'
4. Left-click on your cell selection to reveal a drop-down Formatting menu if you want to add a pattern.
5. Choose a color and then click on 'Add Pattern' to customize the color and pattern for the chosen cell(s). Then click 'OK.'
6. Repeat the above steps for all of the different color and pattern combinations that you need. When you want to remove a color or fill pattern, just highlight the cells, click the arrow next to the paint bucket icon and select 'No Fill.'

How to Have Excel Solver Give Only Whole Numbers

1. Go to the 'Data' tab in Excel, and click the 'Solver' button to launch the Solver dialogue box.
2. Click the 'Add' button under Constraints to launch the 'Add Constraints' dialogue box.
3. Enter the name of the cell in the 'Cell Reference' box, or highlight a range of cells with your cursor to fill this box automatically.
4. Click the drop-down menu in the middle of the 'Add Constraints' dialogue box, and select 'int' from the menu. The word 'integer' will appear in the Constraint box.
5. Click 'OK'

Sunday, January 22, 2012

How to Make Columns Wider in Excel

Click Drag
1. Place your mouse pointer on the top row where the column letters are displayed.
2. Move the cursor over the lines separating the columns and notice that the cursor becomes a cross.
3. Place the cursor over the line to the right of the column that you want to make wider. Be sure that it turns into a cross before going to the next step.
4. Click and drag the column over until it is the width that you want.
Use the Toolbar
5. Go to 'Format.'
6. Select 'Column' and then 'Width.'
7. Enter the width that you want the column to be in the 'Column Width' box.
8. Click 'OK.' The column's width will change.

How to Get the Row Number of a Cell in VBA

1. Open the Visual Basic Editor (VBE) by clicking on the 'Developer' tab and then clicking on 'Visual Basic.'
2. Click on the 'Insert' tab and then click 'Module.' This opens a blank window.
3. Copy and paste the following code into the blank window:Sub myActiveRow()myRow=ActiveCell.RowMsgBox myRowEnd Sub
4. Press 'F5' to run the routine.

How to Create a Button in Excel 2007

1. Click the 'Microsoft Office' button located in the top left corner of Excel.
2. Click 'Excel Options' (a rectangular button on the bottom right of the panel).
3. Click the 'Customize' category from the selection list on the left. Select 'All Commands' from the 'Choose commands from' drop-down list. Scroll down the list to select the type of button you want to add to your Excel 2007 spreadsheet. For example, if you want to add a macro to a button, choose 'Option Button (Form Control)' and click 'OK.' This adds a button to the 'Quick Launch' toolbar (located above the ribbon).
4. Click the button on the Quick Launch toolbar. Left-click anywhere on the spreadsheet and drag the mouse to create a box.
5. Right click-on the button to format the button or assign a macro.

How to Print Continuing Row and Column Headings for an Excel Worksheet

1. Open Microsoft Excel and the file you want to change.
2. Open the File menu and select Page Setup.
3. Select the Sheet tab.
4. Enter the cell numbers you want to use as your row headings in the Row to Repeat box. Enter the first cell number for the heading, then a colon, then the last cell number. For example: B4:F4.
5. Enter the cell numbers you want to use for your column headings in the Columns to Repeat box. Enter the first cell number for the heading, then a colon, then the last cell number. For example: A1:A2.
6. Click Print Preview to see how your document will look.
7. Select Close to exit from the Print Preview function.
8. Click OK to accept your changes.

How to Convert XLSM to XLS

1. Open the 2007 version of Excel or later. Click “Open” in the “File” drop-down menu. Open the .XLSM file from the folder tree that opens up.
2. Click “Save As…” in the “File” drop-down menu. Select “Excel Workbook” as the format. Check the portability notes of the format at the bottom of the Save panel to confirm the format has adequate backward portability for your needs.
3. Convert the XLSM file using an older version of Excel the same way by first downloading the converter titled “Microsoft Office Compatibility Pack for 2007 Office Word, Excel and PowerPoint File Formats.” Before downloading the converter, it is important that you update your version of Microsoft Office to the latest service pack (see Resources).

Saturday, January 21, 2012

How to Insert Charts in Microsoft Excel 2003

1. To create a chart, you first need to select the cells you wish to be contained within that chart. To do this, left-click and hold the mouse button, then drag the cursor over the groups of cells you wish to include. Then let go of the mouse button, and the cells will remain highlighted.
2. Access the chart tool. Scroll to the “Insert” tab on the command bar and select “Charts.”
3. Select the chart for your spreadsheet. A Charts Properties menu will open, in which you can select the style of the chart you want from a drop-down menu. Select the desired chart and click “Next” to continue.
4. In the next menu, you can specify the specific columns and rows to include in the chart. If you have already highlighted the cells and columns from Step 1, you can click “Next” to continue; otherwise, type in the series of charts and rows by entering their corresponding column letters and row numbers.
5. You can label the chart, name the fields and adjust the categories, gridlines and table information simply by selecting the corresponding tab and entering the required information into the fields. Click the “Next” button to continue after making the desired entries.
6. Select where to place the chart. Choose by selecting the corresponding radial button to place the chart in a new sheet or inside of the current sheet; after making your selection, click on the “Finish” button to implement your completed chart.

How to Change Security Settings in Excel 2010

1. Open Microsoft Excel 2010.
2. Click the 'File' tab menu.
3. Click 'Options' to open the 'Excel Options' window.
4. Click 'Trust Center' on the left side bar.
5. Click the 'Trust Center Settings' button under 'Microsoft Excel Trust Center.'
6. Click 'Add-ins' on the left side bar to disable add-ins or require them to be signed by trusted publisher.
7. Click 'ActiveX Settings' to enable, disable or prompt before enabling ActiveX.
8. Click 'Macro Settings' to enable or disable macros with or without notifications.
9. Click 'Protected View' to select options to enable when opening potentially dangerous files. With protected view, you can open unknown files in restricted mode, which disables unknown macros, ActiveX controls or add-ins.
10. Click 'File Block Settings' to choose which file types you want to block or file types you want to open in 'Protected View.'
11. Click 'OK' to close and accept all settings.

How to Paste Special With Word Documents in Microsoft Excel 2003

1. Open your Word document and copy the desired portions of text you wish to paste special into Excel.
2. Open your spreadsheet and scroll to the “Edit” tab on the command bar. Select “Paste Special.”
3. Select the options to paste special. Under the “As” menu, left-click on 'Microsoft Office Word Document Object,' which will paste the copied contents from the Word document into the spreadsheet as a movable object.
4. Left-click on the 'OK' button to paste the Word document into Excel as an object.

How to Disable Automatic Refresh on Office Excel 2003

1. Log onto your computer and open Microsoft Excel 2003. Open the spreadsheet you want to recalculate.
2. Click the 'Options' menu. Choose 'Tools' from the list.
3. Go to the 'Calculation' tab. Choose the 'Manual' calculation option. Click 'OK' when you are done.

Friday, January 20, 2012

How to Merge Workbooks in Excel 2003

1. Create a shared workbook by enabling the Track Changes option. Then send it out to others to make changes.
2. Find the copy of the workbook on which you will merge the changes. In Excel, go to 'File > Open' and find the file.
3. Click 'Tools > Compare' and 'Merge Workbooks.'
4. Look for the files you want to merge and add them into the current file.
5. Click 'OK' to merge the files. This is your master copy of this workbook.
6. Save the file.

How to Remove Duplicates in Excel 2003

1. Open Excel 2003 and copy your two (or more) columns of data into the spreadsheet.
2. Highlight the titles of the columns from which you want to remove the duplicates then click 'Data' and then click 'Filter.'
3. Click 'Advanced Filter' and then click 'OK' if necessary.
4. Click on the 'Copy to another location' option and then click 'Unique records only.'
5. Highlight the records that you want to remove the duplicates from, repeat this process when requested and then select an empty column in the 'Copy to' section.
6. Click 'OK' and your data set is copied into the column you indicated as the 'Copy to' column, excluding any duplicate values found in the data.

How to Stop Excel From Rounding Up or Down the Numbers to the Nearest Whole Number

1. Launch Microsoft Excel. If you have an existing file you wish to work with, click 'File' at the top of the window, and then click 'Open' to browse to that file.
2. Locate the cell(s) containing the values you want to prevent Excel from rounding. If there are multiple cells not in the same column or row, hold down the 'Ctrl' key as you click each cell with your mouse. If you want to adjust all of the files in a row or column, click the number to the left of the row or the letter at the top of the column to select the entire row or column.
3. Click the 'Home' tab at the top of the window.
4. Click 'Format' in the 'Cells' section of the ribbon at the top of the window, and then click 'Format Cells.'
5. Click the 'Number' tab at the top of the window.
6. Click 'General' under the list of options in the column at the left side of the window, and then click 'OK.'

How to Import Macros for Excel

1. Open the workbook with the macro you want to import in Excel. Press 'Alt' and 'F11' at the same time to open the built-in visual basic editor.
2. Navigate to the macro you want to move using the left navigation pane.
3. Click to highlight all the VBA code for the macro. Press 'Ctrl' and 'C' at the same time to copy the macro.
4. Open the workbook you want to import the macro to. Press 'Alt' and 'F11' at the same time to open the built in visual basic editor.
5. Double click 'ThisWorkbook' in the left navigation pane. Press 'Ctrl' and 'V' at the same time to paste the macro you copied earlier into the VBA code window of the new workbook. Your macro is now imported.

How to Use the Scroll Bar in Excel

1. Open the Excel workbook that you want to navigate by double-clicking on it. If you have a different program set your default application to open Excel files, right-click on the excel file select 'Open with' and then choose 'Microsoft Office Excel.'
2. Left-click on the arrow buttons at either end of the main scroll bars to scroll down the spreadsheet one row or column at a time. The vertical scroll bar is located at the far right of the Excel window and the horizontal scroll bar is located in the bottom right-hand portion of the Excel window.
3. Left click on the position bar (the lighter colored bar segment located within either scroll bar), hold down the mouse button, and drag the bar to quickly scroll through the area of the spreadsheet that is being used.
4. Right click on any spot along a scroll bar and select 'Scroll Here' to automatically move the position bar to that spot.
5. Left click on the position bar, hold down the mouse button and the shift key and then drag the bar to quickly scroll through large numbers cells. Holding the shift key and moving the bar will scroll through thousands of cells quickly.

Thursday, January 19, 2012

How to Update Conditional Formatting in Excel 2007

1. Highlight the cells with the conditional format you want to change. To make changes throughout the worksheet, highlight the entire spreadsheet by pressing 'Ctrl' and 'A' at the same time.
2. Click the 'Home' tab in the Office Ribbon and select 'Conditional formatting' from the 'Styles' box.
3. Select 'Manage rules' from the menu that appears. This opens the conditional formatting rules manager dialog box.
4. Click on the rule you want to change.
5. Click 'Edit rule' and select from the dialog box options to update your conditional formatting.
6. Click 'Ok' to save your change, then click 'Apply' to apply the rule to the spreadsheet.
7. Click the next rule and repeat the 'Edit rule' and saving processes.
8. Click 'Ok' to save all changes and close out of the conditional formatting rules manager when you are finished.

How to Convert Lotus 123 to Excel

1. Click the “File” drop-down menu in Excel. Select “Open.” Double-click the Lotus file in the tree that pops up. Spot check the file to see if formula and formats look the way they should. If the formatting is wrong, keep in mind that WK1 and WK3 files save formatting to a separate file. You’ll have to have that file in the same directory as your WK1 or WK3 file.
2. Click the “File” drop-down menu in Excel. Select “Save.” The Save panel will pop up.
3. Select the .XLS file type in the Save panel and click OK. Excel will then convert the workbook to Excel format.
4. Open the new .XLS file. Search on the phrase “formula failed to convert.” If Excel couldn’t convert a formula, it displays the value that the formula produced and leave the “formula failed” comment. Since these are comments, you’ll have to find them by clicking the following sequence: Edit (drop-down menu), Find, Look in: Comments. Return these cells to formulas if possible.
5. Print out the newly converted spreadsheets and compare the numbers to a printout of the original spreadsheet to make sure every formula converted correctly.

How to Update a PivotTable Using a Cell Reference

1. Open the PivotTable you want to change.
2. Click on a cell within the PivotTable you want to update.
3. Run the PivotTable and PivotChart Wizard by clicking on the 'Tools' menu and then selecting 'PivotTable and PivotChart Wizard.' It automatically takes you to the wizard for your PivotTable.
4. Skip Step 1 and go directly to Step 2. This step allows you to redefine the cell reference for the source data. Input the new cell reference in the box for source data and click 'Next' and then select 'Finish.' The PivotTable automatically updates with the new data in the new cell reference.

Wednesday, January 18, 2012

How to Use Excel Templates

1. Turn on your computer and open Excel.
2. Click on 'File' in the tool bar and select 'New.' This will open a sidebar to the right of your Excel document.
3. Under 'Templates' click on the link that says 'On my computer.' This will open the 'Templates' window.
4. Click on the tab labeled 'Spreadsheet Solutions.' This will display the list of templates available on your computer. Select the template of your choice and then click 'OK.'
5. A new window should now be open and the template you selected should be displayed. Now you can fill in the template, and then save and name it accordingly.

How to Put Radio Buttons in an Excel Spreadsheet

1. Open Microsoft Excel.
2. Click the Microsoft Office button located in the top left corner of the window. Click 'Microsoft Excel Options.'
3. Click the 'Popular' category and the select check box for 'Show Developer Tab in the Ribbon.' This option is located under the 'Top Options for Working with Excel' heading. Click 'OK' when you are finished.
4. Click the Developer's tab on the menu bar and select 'Insert' from the Controls group. Select the 'Option' button, which is located under 'Form Controls.'
5. Click the spreadsheet cell where you want to insert the radio button.

Tuesday, January 17, 2012

How to Create a Password/Username for Excel 2003

Create an Excel 2003 Username
1. Open Excel. Go to 'Tools' and select 'Options.'
2. Click 'General' and enter your new username in the username field.
3. Click 'OK.'
Create an Excel 2003 Password
4. Open Excel, and open the spreadsheet you wish to password protect. Click 'Tools,' then select 'Options.'
5. Click the 'Security' tab. Enter a password in the 'Password to Open' field and click 'OK.'
6. Retype the password when Excel prompts you to confirm, and click 'OK.'

How to Use Excel to Create a Bell Curve

1. Type the word 'Mean' into cell E1 and 'Standard Deviation' into cell G1.
2. Type the desired mean and standard deviation for your bell curve into cells F1 and H1. The mean represents the average number from the entire data set. In a bell curve, this is often similar to the median, or the number which occurs most often. The standard deviation is a statistical property based on likelihood of occurrence. A deviation of 1 will include 68 percent of all the data in a collection. By the third deviation, almost all the data is included. For example, a mean of 5 with a deviation of 2 means that 68 percent of all the data will fall between the numbers 3 and 7, which are 2 removed from the mean of 5.
3. Type the number '-4' into cell A2. Select the cell after entering the data by clicking on it once. The desired numbers are arbitrary so long as the subsequent formulas are entered accurately for Excel to generate normally distributed data appropriate for the desired bell curve.
4. Click the 'Edit' menu and select the 'Fill' sub-menu.
5. Choose the 'Series' command from the 'Fill' sub-menu. A pop-up window will appear.
6. Select the 'Columns' option in the 'Series in' section of the 'Series' pop-up window. Select the 'Linear' option in the 'Type' section, and type '0.25' into the 'Step value' field. Type '4' into the 'Stop value' field and press the 'OK' button. The 'Step value' is customizable. Enter a smaller number to generate a curve with greater detail and more points, such as '0.1'. A higher number will show fewer data points.
7. Type the formula=A2*$H$1 $F$1into cell B2. Type the formula=NORMDIST(B2,$F$1,$H$1,FALSE)into cell C2. These functions generate the complex distribution of data necessary to form a true statistical bell curve.
8. Select cells B2 and C2 by clicking once on B2 and dragging the mouse to cell C2. Release the mouse.
9. Copy the formulas down through the entire data range. Hover the mouse over the lower-right corner of cell C2. The cursor will change to a black plus sign. Click and drag the plus sign down to the last row which contains data in column A.
10. Select columns B and C by clicking on cell B2 and dragging down to the last row that contains data, and over one column to include C.
11. Click the 'Chart' button at the top of the Excel program window. A pop-up window will appear.
12. Select the 'XY (Scatter)' chat type and press the 'Finish' button. The bell curve is created.

How to Bring an Image to the Front in Excel

1. Start Microsoft Excel 2007, and open a workbook from your files that contains images in an order you would like to change, or start a blank workbook and insert at least two images.
2. Click to select the image that you would like to bring to the front of the Excel workbook. This means if you put this image over another image it will remain on top, or in the front. White sizing handles will form around the image once you select it.
3. Select the 'Format' tab at the top of the Excel screen. This will display the Format ribbon which contains all the options for formatting an image in Excel.
4. Click the small arrow to the right of the 'Bring to Front' button in the 'Arrange' section of the Format ribbon. A drop-down list will be displayed.
5. Choose the 'Bring to Front' option to bring the picture to the front, so it overlaps all other images on the worksheet. Select the 'Bring Forward' option to bring the image to the front of any other image except those that are set to be brought to the front. Once clicked, the option will immediately take effect to the selected image.

How to Fix Column Width in a Pivot Table

1. Launch the 'Microsoft Excel' program and open a spreadsheet in which a pivot table is already created.
2. Right-click the pivot table and choose 'Table Options' from the resulting menu.
3. Uncheck the 'Auto Format Table' box and click 'OK.'
4. Adjust the column widths and refresh the pivot table. The columns will stay fixed at the width you specified instead of reformatting to fit the data.

How to Create Control Charts in Excel

1. Create a two-columned table; enter the dates or other time measuring criteria in the right column, and the data to be graphed in the left column. For example, to begin creating a control chart to measure the number of products produced on an assembly line per day, list the respective dates on the left and the corresponding production numbers on the right.
2. Calculate the average of your entire data series by entering '=AVERAGE(first value,last value)' into any cell, and establish your desired upper and lower control limits. In our production example, you may find that the average number of products made per day is 100, and you may wish to produce no more than 150 per day and no less than 50. In this case 150 would be your upper control limit and 50 would be your lower control limit.
3. Create a second two-columned chart to list your average data value and control limits. Since these values will be the same for all dates, list only the first and last date in your data series in the left column, once for each value, and the corresponding values in the right column. In our example, assuming your first date is 1/1/09 and your end date is 2/2/10, you would enter '1/1/09' in any cell, '2/2/10' in the cell directly beneath it, and '100' directly to the right of both dates. You would then repeat this procedure twice, directly under the first entries; once for the upper control limit, and once for the lower.
4. Create an XY chart by highlighting the data in your first table and clicking 'Insert -> Charts -> XY Chart'. You will now have a control chart showing the data values at each time interval. In the example, you would have a horizontal XY chart showing the number of products produced each day.
5. Add your data average and control limits by highlighting each of the four-cell blocks in your second table, one at a time, and dragging them over to the chart. Click 'OK' in the Paste Special dialog box that pops up each time, and you will have a complete statistical process control chart. Following the example, you would highlight the start date, end date, and both instances of '100' for the average, then drag the group over the chart. You would then repeat the process for the upper and lower control limits.

How to Link Data to Different Worksheets in Microsoft Excel

1. Launch Microsoft Excel and open each of the source spreadsheets that contain information to link into the target spreadsheet. Open the target spreadsheet last.
2. Click on the cell in the target spreadsheet that is to contain linked information from a source spreadsheet and press the 'Equal' key ('='). Switch to the source spreadsheet and click on the cell that contains the information to be linked and then press 'Enter.' The target spreadsheet now contains a link to the data in the source spreadsheet as indicated in the formula bar. Changes made to the source spreadsheet will be reflected in the target spreadsheet automatically.
3. Click on a cell in the target spreadsheet that is to contain calculated information from cells in multiple source spreadsheets and press the 'Equal' key ('='). Switch to the first source spreadsheet and click on the first cell that contains the information to be linked. Type an operator such as the 'Plus' key (' ') and click on the next cell in the formula, from the same source spreadsheet or a different one, or even from the target spreadsheet if desired. Continue to add operators and cell references until the formula is complete, then press 'Enter' to store the formula and display the results of the calculation.

Monday, January 16, 2012

How to Create a Shortcut for Symbol Use in Excel

1. Make sure your Microsoft Excel security level is set to 'Low' or 'Medium.' On the 'Tools' menu, click 'Options.' In the 'Options' window, choose 'Macro Security' and click the 'Security Level' tab and set it to 'Medium' or 'Low.' If Excel is on 'High' security, then it won't allow your macro to run.
2. On the 'Tools' menu, hover over the 'Macro' option, and select 'Create New Macro.' (If you're using Excel 2007, go to the 'View' tab, click 'Macro,' and select 'Record Macro.'
3. Enter a name for your macro in the space provided. Make it something memorable and ensure that it begins with a letter. Do not make the macro name a cell reference--otherwise, it will create errors when run.
4. Enter a letter for your macro under 'Shortcut Key.' When you run your macro, you'll hold 'Ctrl' and press the key you provide in this box. You cannot use a number for the macro.
5. Choose where you want to store the macro. If you want to be able to use it with any workbook, choose 'Personal Macro Workbook.'
6. Enter a description for the macro. Be as concise as possible.
7. Click 'OK.' Now Excel is ready to record the macro. Carry out the steps needed to create your symbol or functions. Remember, the macro tool can record a number of functions in one round, so you can create complex routines to run with every step of the shortcut key.
8. Now that you're finished recording your Macro, click 'Stop Recording' in the Stop Recording toolbar. If you're using Excel 2007, click on the 'Macro' button in the 'View' ribbon, and select 'Stop Recording' from the drop-down menu.
9. Practice using the shortcut and make sure it works correctly. If it doesn't, go to 'Macros,' click on 'View Macros,' and click 'Edit' to make any necessary changes.

How to Reset Excel 2003 to Print DPI

1. Open the Microsoft Excel 2003 spreadsheet file that you want to reset dpi settings for.
2. Select the 'File' option from the top toolbar menu and then click the 'Page Setup' option. The Page Setup dialog box will then appear.
3. Select the '600 dpi' option in the 'Print quality' section to return to the standard dpi setting.
4. Click the 'OK' button to close out of the Page Setup dialog box and return to your document.
5. Click the 'File' button and then select the 'Save' option to save you changes made to the Excel 2003 spreadsheet.

How to Specify the Templates Folder in Excel 2007

1. Open Microsoft Excel 2007.
2. Click the 'Microsoft Office' button located in the upper left corner of the screen.
3. Select 'Excel Options' from the menu.
4. Click 'Advanced' from the 'Options' menu.
5. Select 'File Locations' from the general section.
6. Select 'User Templates.'
7. Press the 'Modify' button.
8. Browse to the new templates folder location.
9. Press the 'OK' button.
10. Click the 'OK' button again.

How to Use Bullets in Excel 2007

1. Open an Excel 2007 spreadsheet. Insert the cursor in a cell where you want to use bullets.
2. Press 'ALT 0149' to insert a bullet point into the cell. Press the space bar to put a space and then type in the bullet item.
3. Press 'ALT Enter' to put a line break in the same cell and then press 'ALT 0149' to insert another bullet point, or press 'Enter' to submit the text in the current cell and move to the one below it.

Sunday, January 15, 2012

How to Graph a Histogram Bin in Excel

1. Type the data you want on your histogram in column A. Use a separate cell for each piece of data.
2. Type your corresponding bin numbers into column B.
3. Click 'Data,' then 'Data Analysis.'
4. Click 'Histogram,' then 'OK.'
5. Click the 'Collapse Dialog' button next to 'Input Range,' and then highlight your data range, which is column A.
6. Click the 'Collapse Dialog' button next to 'Bin Range,' and then highlight your data range, which is column B.
7. Click 'OK.'

How to Remove Blanks From Pivot Table

1.
Select any box within the pivot table report to activate the PivotTable toolbar. Click the 'Refresh Data' icon.
2.
Click on the data field drop-down list in the first column of the pivot table. Uncheck the box next to the '(blank)' option. Click 'OK.'
3. If there is another data field drop-down list in the same column, select it and uncheck the box next to the '(blank)' option. Click 'OK'. Repeat this step until all data fields in the column have all of their '(blank)' filtering options unchecked.
4.
Select the first data field drop-down list in the first row of the pivot table and uncheck the '(blank)' filtering sort options.
5.
Repeat Step 4 for each and every data field that has drop-down filtering list.

How to Protect a Worksheet in Excel 2007

1. Open the Excel workbook that has the worksheet you want to protect.
2. Click the worksheet you want to protect and then click the 'Review' tab located in the Excel ribbon at the top of the page. You can check spelling, add comments, and protect a worksheet here.
3. Look to the 'Changes' section of the ribbon and click the 'Protect Sheet' button.
4. Enter a password to protect the worksheet. If someone else wants to make changes to the worksheet, they will have to enter the password you selected.
5. Select any elements you want others who have access to the worksheet to be able to change without the password. For example, you can choose to allow other users to edit objects or use the Auto Filter. After you make your selections, click 'OK.' The worksheet is now protected.

Saturday, January 14, 2012

How to Convert a Webpage Table to an Excel Spreadsheet

How to Convert a Webpage Table to an Excel 2003 Spreadsheet
1. Open the Excel worksheet into which you want to import the webpage table. Go to the 'Data' menu, point to 'Import External Data' and select 'New Web Query.' The 'New Web Query' window will open.
2. Type or paste the webpage address into the 'Address' bar and click 'Go.' The webpage will open in the dialog box with arrows to the left of each available table.
3. Click the arrow beside to table that you want to convert from the webpage to your Excel spreadsheet.
4. Click the 'Import' button. The 'Import Data' dialog will open. Under 'Where Do You Want to Put the Data,' select 'Existing Worksheet' and click 'OK.'
5. Wait while Excel converts the table to your worksheet. Modify the table and save your changes as desired.
How to Convert a Webpage Table to an Excel 2007 or 2010 Spreadsheet
6. Open the Excel worksheet into which you want to import the webpage table. Go to the 'Data' tab.
7. Click 'From Web' in the 'Get External Data' group. The 'New Web Query' window will open.
8. Enter or paste the webpage address into the 'Address' bar. The address may be up to 255 characters in length. Click 'Go' to navigate to the webpage.
9. Click the arrow icon next to the table you want to convert to your spreadsheet or click the arrow icon at the top left of the window to import the entire webpage. If you do not see arrows on the webpage, click the 'Show Icons' button on the 'New Web Query' toolbar.
10. Click the 'Import' button and Excel will convert the webpage table to your worksheet.

How to Embed Objects in MS Word

Microsoft Word 2007 or 2010
1. Open a Microsoft Word document.
2. Place your cursor where you want to insert an embedded object.
3. Click the 'Insert' tab. Click 'Object' in the 'Text' group.
4. Click the 'Create from file' tab to embed an existing object. Click 'Browse' to select the file from your computer. Uncheck the 'Link to file' box. Click 'OK.'
5. Click the 'Microsoft Office Button' or 'File' tab. Click 'Save' to save the Word document.
Microsoft Word 2003
6. Open a Microsoft Word document.
7. Place your cursor where you want to insert an embedded object.
8. Click the 'Insert' menu. Click 'Object.'
9. Click 'Create from file.' Click 'Browse' to select the file from your computer that you want to embed. Uncheck the 'Link to file' box. Click 'OK.'
10. Click 'File,' then 'Save' to save the Word document.

How to Cite MS Excel in Literature

Creating an APA in-text citation for Excel
1. Open your word processing software to write your paper. Write out the thought that needs to have a citation referring to Excel.
2. Type “Microsoft Excel” and, in parentheses, “Excel version” as your in-text citation. It should look something like “In using Microsoft Excel (2007), I found it easy to…”
3. Do not add an entry for this in-text citation later in your paper in the list of references or bibliography section.
Creating an APA software citation
4. Open your word processing software to write your paper. Write out the paraphrased thought that needs to have a citation referring to an uncommon piece of software.
5. Type in your citation in your text. It should be the author’s last name and the month of creation for the software. Example: Jack Smith’s software CaffeineDog (2010) allows you to feed the dog and start your coffee pot at the same time. Next you need to provide the reference in your list of references in the back of your paper.
6. Type out the name of the author, last name, first name. In parentheses, type in the year the software was published and a period.
7. Type in the name of the software and, in parentheses, type the version, if applicable. Do not italicize the title. In brackets, write out Computer Software. Add a period. Follow this with the name of the institution where the software was created followed by a period. Example: Smith, Jack (2010). CaffeineDog (Version 3.0) [Computer Software]. CaffeineDog Software, Omaha, NE.

How to Use an Excel List for a Random Drawing

1. Construct a list of contest entrants. If you already have a list with consecutively numbered entrants, skip to step 6. If not, open a new spreadsheet in Excel.
2. Click on cell B1. Enter, or copy and paste, the first person's name. Click on cell B2, and enter the next name. Continue down the column until you have entered all the names.
3. Assign a consecutive number to each entrant starting with 1 and ending with N, where N is the total number of entrants that you have. Click on cell A1, and enter '1.' Click on cell A2, and enter '2.'
4. Click on A1, and drag the cursor to cell A2. These two cells should now be highlighted.
5. Move your cursor to the bottom right corner of cell A2 until a solid black cross (one without arrows) appears. Click and drag this black cross down column A until you reach the last name. Each number should now correspond to a name to the right of it.
6. Click on any empty cell in the spreadsheet. Enter the following formula to generate a random number: =RANDBETWEEN(1,N)N is the total number of entries that you have. For example, if you have 100 contestants, you would enter: =RANDBETWEEN(1,100)Don't put spaces between any of the terms in the function.
7. Hit 'Enter' to receive a random number between 1 and N. That number corresponds to the winning entry in your list. To generate another random number, move your mouse to the random formula cell again, and hit 'Enter' again.

How to Remove a Page Break in Microsoft Excel 2003

1. Open the Microsoft Excel 2003 worksheet. Click the 'View' tab on the top menu.
2. Select 'Page Break Preview' to change the worksheet view. This exposes the page breaks in the worksheet.
3. Click on the page break you want to remove. Hold your left mouse button and drag the page break out of the preview screen. Release your mouse button. This removes the page break from the worksheet.

How to Generate 20 Random Numbers in Excel 2007

1. Open the Excel 2007 file where you want to create 20 random numbers.
2. Click an empty cell that has another 19 empty cells directly beneath it.
3. Type '=rand()*(y-x) x' into the formula bar. Replace 'x' and 'y' with the range of numbers that the random number will fall between. For example, '=rand() (10-1) 1' will return a random number between 1 and 10. Press 'Enter.'
4. Click the cell with your formula in it, which now displays a random number. Move your mouse over the bottom-right corner of the cell, where the pointer turns into a plus sign. Click the mouse button and hold it down. Move the mouse down 19 cells beneath the first and release the mouse button. You now have 20 random numbers, although each number is written out to nine decimal places.
5. Click and hold the mouse button on the first random number cell, and then drag the mouse to the last cell and release the button. Right-click anywhere in the selected range and click 'Format Cells' from the menu.
6. Click 'Number' from the list on the left side of the window. On the right side, change the field after 'Decimal places' to be equal to the number of decimal places you want to display on the Excel sheet. Click 'OK' to continue.

How to Use Excel 2007 to Make a Climograph

1. Open a new worksheet in Excel 2007. Create three column headings: Month, Temperature and Precipitation.
2. Enter 12 rows of data consisting of the 12 months of the year in the first column and the mean monthly temperature and precipitation amounts for each month in the second and third columns, respectively.
3. Click and drag the mouse across your entire range of data in all three columns.
4. Click on the chart button in the toolbar to start the Chart Wizard. Select a clustered column type chart and click 'OK.' You will see the data grouped in two rows of vertical columns.
5. Right-click on the data series marker for temperature and select 'Format Data Series' from the submenu. Select the option for 'Secondary axis' in the pop up window and click 'OK.'
6. Right-click on the data series marker for temperature and select 'Change Series Chart Type' from the submenu. Select a 'Line With Markers' data series type from the pop up window and click 'OK.' You now have a completed climograph.

How to Convert a Column to All Caps in Excel

1. Open the Microsoft Excel document that contains the column of data that you want to convert to all caps. Open a second blank Microsoft Excel document as well.
2. Highlight the entire column in the Excel document that you want to convert to all caps. Note that if you want to convert several columns to all caps, you must repeat this process for each column; you can only change one column at a time using this method.
3. Right-click the highlighted data and select 'Copy.' Minimize the Excel document and maximize the blank Excel spreadsheet.
4. Right-click in cell 'A1' in the top left corner of the spreadsheet and select 'Paste.' The data will transfer to the blank document.
5. Input the following formula in cell 'B1' in the spreadsheet you just copied the data to:=UPPER(A1)Cell 'B1' will now contain an all-caps version of cell 'A1.'
6. Hold your cursor at the bottom right corner of cell 'B1' until it changes from a white cross to a black cross. Click and drag downward, highlighting the same number of cells as the 'A' column. The 'B' column will populate with the all-caps version of the 'A' column.
7. Highlight all of the cells that contain values in column 'B,' right-click and select 'Copy.'
8. Maximize the original document, and highlight the column that you highlighted in Step 2.
9. Right-click the highlighted selection and choose 'Paste Special.' A dialog box will appear. Select 'Values' and click 'OK.' Excel will replace the original values in the column with the all-caps version of the values.

How to Protect Cells in Microsoft Excel

Microsoft Excel 2007
1. Open your spreadsheet in Microsoft Excel.
2. Select any cells you do not want protected. Right click on them and click 'Format Cells.' You can skip this step if you will be protecting the entire sheet or workbook.
3. Select the 'Protection' tab. The 'Locked' option will automatically be checked, but the cell is not protected yet. Click on the box to remove the check mark so that group of cells will not be locked when you protect the sheet.
4. Click on the 'Review' menu. Look for the 'Changes' section on the right side and click 'Protect Sheet' to protect only that sheet or tab, or click 'Protect Workbook' to protect all of the sheets. The 'Protect' dialog box will open.
5. Select the attributes you want to restrict. If you would like to specify a password to unlock the sheet or workbook, you may do it here, but that is optional.
6. Click 'OK.' Your worksheet or entire workbook will be locked as you requested.
7. Click the 'Unprotect Sheet' or 'Unprotect Workbook' button to unlock the cells.
Microsoft Excel 1997-2003
8. Open your spreadsheet in Microsoft Excel.
9. Select any cells you do not want protected. Right click on them and click 'Format Cells.' You can skip this step if you will be protecting the entire sheet or workbook.
10. Select the 'Protection' tab. The 'Locked' option will automatically be checked, but the cell is not protected yet. Click on the box to remove the check mark so that group of cells will not be locked when you protect the sheet.
11. Click on the 'Tools' menu, and then 'Protection.' Click 'Protect Sheet' to protect only that sheet/tab or click 'Protect Workbook' to protect all of the sheets. The 'Protect' dialog box will open.
12. Select the attributes you want to restrict. If you would like to specify a password to unlock the sheet or workbook, you may do it here, but that is optional.
13. Click 'OK.' Your worksheet or entire workbook will be locked as you requested.
14. Click on the 'Tools' menu, 'Protection' and 'Unprotect Sheet' or 'Unprotect Workbook' button to unlock the cells.

Friday, January 13, 2012

How to Succeed in Business With Microsoft Excel 2003

1. Visit Microsoft Office's website and click 'Excel' under the 'Support' menu item, and then choose the 2003 version.
2. Learn as much as you can about how to use Excel through Microsoft Office's online tutorials (see Resource section). These courses are free on the company's website, and they will help you learn how to use the software in detail.
3. Click the 'Templates' link on the Microsoft Office home page and then click 'My Programs.'
4. Select 'Excel 2003' from the programs list and click 'Save.' This limits your online search to compatible templates. Search through the links provided for all useful templates for your business including budgets, forms, expense reports, invoices, receipts, project calculators and calendars. Downloading premade templates will save you time, and you can modify them to suit your business needs.
5. Visit the main Microsoft website and download the Office 2003 Compatibility Pack from the 'Downloads' area of the website (see Resource section). Follow the instructions on the site to download and install this software, which will make your Excel 2003 software compatible with newer versions so you can read and edit the .xlsx file format from newer versions of Excel. This enables you to exchange invoices and other business documents electronically without upgrading to newer versions of Excel.
6. Set aside time to do your Excel data entry when you won't be disturbed. You can use Excel throughout the workday to track your work flow, but many businesses experience a backlog in data entry. Excel can only help you track data if all data is entered, so make that part of your basic business operation.
7. Double-check all data you enter into Excel for accuracy. Excel cannot compensate for human error.
8. Back up all of your Excel documents regularly to an external hard drive, CD, USB drive or other media that is not part of your computer's hard drive. Regularly may mean daily for some businesses and weekly for others. Consider how much data you are willing to lose and base your backup schedule on the amount of data you would lose in a day versus time spent backing up.
9. Password-protect all documents containing private information, including your customers' contact information and any financial records you don't want others to access. Click 'Tools,' then 'Options,' and then click the 'Security' tab. Follow the dialog box instructions to secure your workbook and click 'OK.'

How to Block Access to an Excel 2007 Worksheet

1. Open your worksheet in Excel, just as you would normally.
2. Click the top-right 'Windows' orb, hover your mouse pointer over 'Save As' and select 'Excel Workbook.'
3. Click 'Tools' at the bottom of the 'Save As' window and select 'General Options.'
4. Enter the password you would like to use next to 'Password to Open' and click 'OK.'
5. Re-enter your password and click 'OK.'
6. Click 'Save' and choose 'Yes' when prompted to save it over the existing file.

How to Create an Input Box in Excel VBA

1. Click the Windows 'Start' button, 'All Programs,' 'Microsoft Office,' and the program you want to use to create an input box. After the software loads, open your document.
2. Click the 'Developer' tab or menu item.
3. Click 'VBA' to open the coding editor and locate the function or section of your file you want to use to display the input box.
4. Type the following code to create the input box: 'Dim input As String input= InputBox(Prompt:='Enter your name', Title:='Name Input')' This code displays an input box for the user to enter a name. The name stores in the 'input' variable.

How to Create a List Box to Select Multiple Entries in Excel 2003

1. Enter the data you wish to format as a drop-down list within a single row or column. Make sure it is a single block of cells such as A2:A6.
2. Highlight the cells in your list. Click the 'Name' box next to the 'Formula' bar and enter a name for your list. Click 'Enter.'
3. Click 'Validation' from the 'Data' tab. Highlight the cells for which you created the list. Choose 'List' from the 'Validation' box. Type a '=' sign before the name of your list within the 'Source' box and click 'OK.'

How to Create a Multiple Baseline Graph in Excel 2010

1. Type the label for your first baseline into cell A1. Type the label for your first intervention into cell B1, the next baseline in C1 and the next intervention in D1. Continue for as many baselines and interventions as you want to show in your graph.
2. Enter the data for the first baseline in column A. Enter the data for the first intervention in column B starting on the first row after the last entry in column A. Accordingly, you will have blank cells in column B where there is data in column A and blank cells in column A where there is data in column B. Continue for all of the columns so that each new set of data starts in the row below the last entry in the previous column.
3. Click and drag from cell A1 to the lower-right corner of your data. Click on the 'Insert' tab and choose 'Line' from the 'Charts' section. The multiple-baseline chart will be created with the baselines and interventions showing as separate lines on the chart.

Thursday, January 12, 2012

How to Name a Field in Excel 2003

1. Open the Excel Workbook that contains the field you want to name.
2. Select the cell, column or row you want to name. Select a cell by clicking it. Select a column by clicking the column letter at the top of the spreadsheet. Select a row by clicking the row number on the left side of the spreadsheet. Select disconnected cells, columns or rows by holding down the 'CTRL' key while you make your selections.
3. Select a range by clicking and dragging to include cells. Select disconnected ranges by holding down the 'CTRL' key while you make your selections.
4. Find the name box in the spreadsheet. It is to the left of the formula bar directly above the column letters. The address of the first cell you selected is visible in the name box--unless you selected multiple ranges, then the first cell in the last range of cells selected is visible in the name box.
5. Click in the name box and type in the name you are giving the cell or range. Press the 'Enter' key.
6. Click the drop-down arrow in the name box to see the names of all the cells and ranges you have named.
7. Open the 'Define Name' dialog box by pressing 'CTRL F3' to review the names you have created and the cells to which they refer. Click the 'Close' button when done.
8. Use a named cell or range in a formula by replacing the cell or range address with the name you defined. For example,give the range of cells from A1 to A5 (A1:A5) the name 'blue.' Assuming those cells contain numbers, you can use the formula '=sum(blue)' instead of '=sum(A1:A5).'
9. Highlight a named range when creating a formula to insert the range name automatically. For example, using the named range 'blue' in the sum function will insert blue ('=sum(blue)') in place of the range from A1 to A5 ('=sum(A1:A5)').

How to Add Multiple Cells in Microsoft Excel 2003

1. Before you can add multiple cells, you need to define the area. You can easily do this by left-clicking on the desired cell and dragging the mouse cursor over the area where you wish to add cells and then releasing it. The selected area will stay highlighted.
2. Right-click on the highlighted area to access the Cell Properties submenu.
3. Add desired multiple cells. Scroll to “Insert” on the Cell Properties submenu and left-click to insert or add multiple cells.
4. Set cell rotation for the addition of cells. A cell insert properties box will open, in which you can select the rotation and placement of the cells to be added. You can shift the existing cells either to the left or down to make room, all by selecting the corresponding radial button.
5. Implement your changes. To add the multiple cells, click on the 'OK' button.

Wednesday, January 11, 2012

How do I Use VLookup in MS Excel?

1. Type '=VLOOKUP(' into the cell in that you want the result of this function to be displayed. Don't include the quotation marks.
2. Type in the value that you want the function to look for, followed by a comma. You can either type it in as a literal value. If it is text, make sure to enclose it in quotation marks. You can enter a cell reference.The function will search for whatever value is contained in the cell you refer to.
3. Enter the cell reference for the range of cells comprising the table, followed by another comma. For example, if your table consists of cells A1 through E10, enter in A1:E10. Alternatively, you can just click and drag a box around the table, and Excel will automatically put the cell reference into the formula. The function will search for the value you entered in step two in the first column of this range, so make sure the range you enter begins with the correct column.
4. Type in a number representing the column you want to get your result from, followed by another comma. For example, if you want the telephone number from the third column of the range you entered in Step three, type in 3. The number refers to columns in the range, not the column numbers in the spreadsheet. The first column, the column used to find the key value, is column 1.
5. Type TRUE or FALSE, followed by a closing parenthesis ')', then push Enter. You should use FALSE if you want the function to only find an exact match to your key value, or TRUE if want to let it find the nearest approximation.

How to Use Less Than or Equal to Function in Excel

1. Select the cell to display the calculated result and insert the function for the comparative formula (by selecting 'Function' under 'Insert' in the top menu bar). Make sure to review the format of the formula in the dialog box to identify the information to be entered. For example, the formula format for IF is '=if(logical_test, value_if_TRUE, value_if_FALSE)' where the logical test is the comparative rule or conditional criterion, value-if-TRUE generates a response when both conditions match and value-if-FALSE generates a different response if both conditions do not match.
2. Type '(' and then select the cell containing the value for the criterion or type the desired value. For example, after the function '=if' is generated, type '(' and then select the A3 cell to display '=if(A3' in the formula bar.
3. Type '
4. Type ')', other information as specified by the formula format (in Step 1) and then press the 'Enter' key to complete the function. For example, the desired calculation will generate a display of 4 if the value in A3 is less than or equal to 100 or 500 if the value in A3 is greater than 100. The complete formula would read '=if(A3
5. Explore other functions to create comparative formulas such as COUNTIF and SUMIF where '

How to Insert a Microsoft Graph Chart in a Publisher Document

How to Insert a Microsoft Graph Chart in a Publisher Document
1. Open Publisher by double clicking on the Publisher Icon.
2. Go to the File pull-down menu and click once on new.
3. Go to the Insert pull-down menu. Click on it.
4. Click once on Object. An Insert object menu will pop up.
5. Choose on the Create from file option. You will see two options: Create new or Create from file. You would have chosen Create new if you wanted to create a new Microsoft graph chart. Since we are working on the assumption that you have a graph chart that you have already created and want to import it from somewhere else on your computer, you will need to choose the Create from file option. This is the option we will be looking at since we are focusing on inserting rather than creating a Microsoft graph chart in a Publisher document.
6. Click inside the small circle in front of Create from file.
7. Click on Browse to retrieve the file you want to get the Microsoft graph chart from.
8. Click once on the File you want to open.
9. Click on open. The Microsoft graph chart will automatically appear in you document.
10. Click on the chart and hold down the cursor to move the chart to the position you want it to be in your document.

How to Insert Excel Data Into a Word 2007 Table

1. Open the Microsoft Word and Excel 2007 files. Locate the files from both software programs on the computer hard drive and double click on the file icon to open each.
2. Create the table in the Word document. Choose the 'Insert' tab and from that menu ribbon select 'Table' option. Next, choose the appropriate size of the table to insert in the Word document.
3. Highlight the data in Microsoft Excel to insert in the Word 2007 table. Left click the mouse and while holding the mouse button down, highlight the data in Microsoft Excel.
4. Copy the data from the Excel document. With the data highlighted, right click and choose 'Copy' from the right click menu.
5. Paste the data from the Excel document into the Word table. Select the word document and position the cursor inside the Microsoft Word 2007 table. Then, choose 'Paste Special' from the main menu in the paste dropdown box. From the paste special dialogue box choose the 'Microsoft Excel Worksheet Option' and insert the data into the table.
6. Save and close both documents when done. From the main file menu, choose 'Save' in both Microsoft Excel and Word to make certain the work is not lost.

Subscribe to: Posts (Atom)