Wednesday, April 17, 2013

How to Disable Links in Excel 2007


1. Click the Excel button on the top toolbar, and then select the 'Excel Options' button at the bottom. Choose 'Proofing' from the left menu.
2. Click the 'AutoCorrect Options...' button under the 'Autocorrect options' section. Select the 'Autoformat as you type' tab, then uncheck 'Internet and network paths as you type' under the 'Replace as you type' section. Click 'Ok' to save the changes.
3. Click 'Ok' in the 'Excel Options' window to close it and return to the spreadsheet. Type or paste a URL into a blank cell then hit the space bar or enter key to verify the changes.
Read more ►

How to Turn on Sounds in Excel 2007


1. Click the 'Microsoft Office' button. A list of commands opens.
2. Click 'Excel Options.' The 'Excel Options' window opens.
3. Click the 'Advanced' tab.
4. Scroll down to the 'General' section.
5. Check the box for 'Provide Feedback With Sound.'
6. Click 'OK.'
Read more ►

How to Reset the Excel 2003 Application to Default Settings


1. Save and rename your workbook and worksheet settings templates if you want to use them again. Save them in the Templates folder, which is located here: C:\Documents and Settings\user_name\Application Data\Microsoft\Templates.
2. Click on your computer's start menu and search for the following in the search dialog box at the bottom of the menu: C:\Program Files\Microsoft Office\OFFICE11\XLSTART. Delete any template files in this folder. If you'd only like to delete workbook settings templates, delete templates with 'Book.xlt.' If you'd only like to delete worksheet settings templates, delete templates with 'Sheet.xlt.'
3. Open Microsoft Excel 2003 on your computer if there are no files located in the folder specified in Step 2. Click on 'Tools,' then 'Options' and then on the 'General' tab. Delete the Book.xlt and/or Sheet.xlt files specified beside 'At startup, open all files in.'
Read more ►

How to Make an Excel Chart With a Negative and Positive Axis


Numerical (Value) Axis
1. Open the Microsoft Excel 2010 file that contains the chart you want to alter.
2. Click anywhere on the chart. Select the 'Format' tab at the far-right end of the ribbon. Click the drop-down box that appears on the far-left end of the ribbon. Select your desired axis from the list. Then, choose the 'Format Selection' button located right below the drop-down box.
3. Click the radio button next to 'Fixed' under the 'Minimum' heading. Place your cursor into the text box next to this button and enter the lowest number you want displayed on your axis. Place a dash before you enter the number to signify that you are entering a negative number.
4. Press 'Enter' to close the Format Axis window. Your chart will automatically update to show the negative axis.
Text-Based (Category) Axis
5. Open the Excel 2010 spreadsheet that holds the graph you want to manipulate.
6. Click on the top cell in any empty column on your spreadsheet. Enter the lowest value that you want your chart to display on the axis in this cell. Be sure to place a dash in front of a negative number. Press 'Enter' to move down to the next cell and then enter the next value you want displayed on the axis. Since you will be manipulating a text-based axis, you will have to specify each value here, as Excel will not treat these values as numbers. Continue to add values until you have added enough to fill your entire desired axis. If you are counting up from negative numbers into positive numbers, skip zero.
7. Select anywhere on the chart that you want to change. Click the 'Design' tab at the top of the screen. Find the 'Select Data' button, which is located in the Data area of the ribbon, and click it. The Select Data Source window will appear on your screen.
8. Click the 'Edit' button under the axis you want to change. The window will minimize. Select on the letter at the top of the column where you entered your desired axis labels. Choose 'OK' twice to use these labels instead of the default ones.
9. Click the 'Layout' tab at the top of the screen. Select the drop-down box that appears in the ribbon and select the axis that you just altered. Click the 'Format Selection' button.
10. Click the radio button next to 'At Category Number' under the Vertical Axis Crosses heading. Enter a number into the text box equal to the absolute value of the lowest number on your axis, plus one. So if your axis goes down to negative five, you would enter six into this box. Click 'Close' and your vertical axis will now cross your horizontal axis at the right spot.
Read more ►

Tuesday, April 16, 2013

How to Import an Excel Spreadsheet into Another Sheet


Linking
1. Launch Microsoft Excel and open the source file that contains the data to be linked.
2. Open or create the destination file in which the data will be displayed.
3. Navigate to the source spreadsheet and highlight (or 'Select') the cell or range of cells to be imported by clicking the cell, or clicking the top left corner of the range of cells and holding the mouse button down while dragging the cursor to the lower right corner of the range of cells and then releasing the mouse button. Alternately, click the gray button that contains the row number or column letter to select an entire row or column, respectively, or click the gray button in the upper left corner of the spreadsheet between the row header '1' and the column header 'A' to select the entire sheet.
4. Right-click any cell in the selected range of cells and click 'Copy.'
5. Navigate to the destination spreadsheet where the data will reside and click once on a cell that you want to make the upper right corner of the copied data from the source spreadsheet. If an entire row or column was selected, click the first cell in the row or column or select the row or column by clicking the gray box that contains the number or letter of the row or column. If you selected an entire spreadsheet click the cell 'A1' or the gray button in the upper left corner of the spreadsheet between the row header '1' and the column header 'A.'
6. Right-click in the selected cell or header.
7. Select 'Paste' to copy the contents of the original cells exactly as they are to the new location, with no links back to the original data. This will allow a duplicate spreadsheet to be created including data and formulas, but subsequent changes to the original spreadsheet data will not be reflected in the new location. Formatting will be copied with this method.
8. Select 'Values' to copy the current values stored in each cell to the new location. This will not copy any formulas, but will copy the current results of any calculations performed by any formulas. Subsequent changes to the original spreadsheet data will not be reflected in the new location. Formatting will not be copied with this method.
9. Select 'Formulas' to copy all the existing formulas and values to the new location. Subsequent changes to the original spreadsheet data will not be reflected in the new location. Formatting will not be copied with this method.
10. Select 'Transpose' to copy the data and formulas along with the formatting to the new location in a transposed orientation, so that rows become columns and columns become rows. Subsequent changes to the original spreadsheet data will not be reflected in the new location.
11. Select 'Formatting' to copy the formatting of the selected cells but leave the cells empty. No values or formulas will be copied, but format attributes such as cell color and currency formatting will be reproduced.
12. Select 'Paste Link' to create a link to the source spreadsheet that will be updated when the original spreadsheet data is updated. This will allow the data from the original spreadsheet to be shown in the destination spreadsheet without allowing any changes to be made to the original spreadsheet from within the destination spreadsheet.
Read more ►

How to Remove Duplicates From Access Query


1. Open an Access 2007 database on your computer. Make sure it has a table that contains duplicate data. Select the 'Office' button and select 'Open.' Search your computer and locate the database. Click on the database and select 'Open.'
2. View the tables in your database in the Navigation Pane. Open one of the tables and visually note the duplicates and note the total number of records. Select the 'Create' tab on the ribbon. Click on the 'Query Design' button. Add the table you have just viewed by clicking on the table and selecting 'Add.' Select 'Close.' Add some fields to the query by double clicking on the fields.
3. Select the 'Properties' option by right clicking in the gray area of your query design field. This will display the Properties Sheet dialog box. Locate the 'Unique Values' field. Change the field to 'Yes.'
4. Run the query by clicking on the 'Run' button. The query will return the distinct values from the table. This is due to the Unique Values field being changed to Yes. Save the results by clicking on the 'Save' icon on the Quick Access Toolbar.
Read more ►

How to Insert a Dynamic Date and Time in Excel


1. Start Microsoft Excel 2007, and open an existing spreadsheet from your files that you want to insert a dynamic date and time into. Or, start a new, blank spreadsheet.
2. Click to select the cell into which you want to insert the dynamic date and time. The cell will be outlined in a thick, black line indicating it is selected.
3. Use your keyboard to type '=now()' (without the quotation marks) into the selected cell. This is the formula that is used to instruct Excel 2007 to insert the dynamic date and time.
4. Press the 'Enter' key on your keyboard to enter the formula into the selected cell. You will now see the current date and time appear in the cell you typed the formula into. This date and time will update every time the 'Enter' key is pressed while working in Microsoft Excel.
5. Repeat Steps 2 through 4 above to insert the dynamic date and time into any other cells inside the open spreadsheet you would like to display the current date and time.
Read more ►

How to Convert a Word Document to a CSV File


1. Open the Microsoft Word document in Word and save it as a '.txt' file. To do this, select 'File' and then chose 'Save As.' Next, use the down arrow next to 'File as Type' and select '.txt.'
2. Open the Microsoft Word document in MS Excel by selecting 'File' and then 'Open.' Find the file in the 'Look in' directory from within the 'Open' dialog box.
3. Click option 'Delimited' and then click '1' beside 'Start Import at Row' to indicate the first row to import the data. Next, select the geographical location of the original file format beside 'File Origin.' When done, click 'Next.'
4. Click 'Tab' under Delimiter preference and then click 'Next.' Additional options include 'Semicolon,' 'Comma,' 'Space' and 'Other' for you to customize the separating character type.
5. Click 'General' as the format option for the 'Column Data 'and then click 'Finish.' Other column data formats include 'Date,' 'Date' and 'Do not import column (skip).'
6. Select 'File' and then choose 'Save As' from the toolbar menu.
7. Choose '.csv' as the 'Save as Type' and then click 'Save.'
Read more ►

Monday, April 15, 2013

How to Convert Word to Excel 2003


1. Open the Word document with the data you want to convert to Excel 2003.
2. Highlight the data with your mouse. Choose 'Convert Text to Table' from the 'Insert' menu and convert the text into a table if it is not already.
3. Hover your mouse over the table until you see a ' ' appear in the upper-left corner. Click on the ' ' sign to select the entire table.
4. Click 'Edit' and then 'Copy.'
5. Launch Excel 2003 and click into a blank cell.
6. Click 'Edit' and then 'Paste.' Your data will appear within Excel.
7. Click 'File' 'Save As' and choose 'Excel Workbook (.xls)' from the 'Save as Type' drop-down field. Click 'Save.'
Read more ►

How to Change the Axis on Excel 2007


Swap X and Y Axes
1. Open the Excel file that contains the chart you want to manipulate.
2. Right-click anywhere within the chart itself. Choose 'Select Data' from the pop-up menu. A small data window will appear on the screen.
3. Click the 'Switch Row/Column' button in the middle of the window. Click 'OK' to confirm your changes. Your X and Y axes will now be swapped on the graph.
Alter an Individual Axis
4. Open the Excel 2007 file that holds the chart you want to change.
5. Select one of the cells that contain the information used in the axis you want to alter. Press 'Delete' to remove any incorrect information and type in whatever you want to replace it. The Excel chart will update as you type.
6. Click anywhere within the chart and click the 'Design' tab at the top of the screen. Press the 'Select Data' button on the left side of the ribbon. Press the 'Edit' button under the 'Horizontal (Category) Axis Labels' area to select a new set of cells to make up the horizontal axis. Click and hold on the first cell then drag the mouse to the last cell and release the button. If you want to remove an individual label from the axis you will first need to press the 'Switch Row/Column' button to get the X-axis items on the left side of the screen. Select the item and choose 'Remove.' Then press the 'Switch...' button again. Click 'OK' to close the window.
7. Right-click on any piece of text in the horizontal axis. Choose 'Format Axis' from the pop-up menu. A small window will appear on the screen. Click and drag the top of the window to the side of your chart so that you can see your changes as you make them.
8. Make changes to how the chart displays the tick marks in the first two sections of this window. You can also alter the 'Axis Labels' to move the labels around the chart or remove them entirely. At the bottom of the window you can alter how the two axes intersect. Click on any of the six other options on the left side of the window like 'Number,' 'Line Fill' or 'Line Style' to alter how the text appears on the axis. Click 'Close' when you are done.
9. Right-click on any text on the vertical axis and choose 'Format' axis from the menu. You can change the way that the numbers are displayed at the top of the new screen that appears. Near the middle of the screen you can alter the tick marks and axis labels and near the bottom you can change how the two axes intersect. This menu also contains an additional six settings on the left that will affect how the axis label text appears. Click 'Close' when you are done.
Read more ►

How to Recover an Excel 2007 File


After an Excel Crash
1. Restart Microsoft Excel 2007. You may also need to restart your computer if you had a system crash or power outage. The document recovery task pane should automatically open on the left side of the screen.
2. Scroll through the documents in the 'Available Files' list. Excel will try to recover up to three versions of the document you were most recently working on.
3. Select a file from the list. Click the down-arrow icon.
4. Click 'Open' to view the recovered document. If you know you want to keep the file, click 'Save as' to rename it. Click 'Delete' if you want to delete the particular file. Save the recovered file that is most useful.
Avoid Losing Data
5. Open Microsoft Excel 2007.
6. Click the 'Microsoft Office Button.'
7. Click 'Excel Options.'
8. Click the 'Save' link.
9. Select 'Save AutoRecover Information Every x Minutes.' Enter the number of minutes in the 'Minutes' list. This will be how often Excel will automatically save your work and program state.
Read more ►

Sunday, April 14, 2013

How to Merge Data From Two Spreadsheets


1. Open Excel 2010 and locate the workbook containing the worksheets that will be merged. Click the 'File' tab and select the 'Open' icon. Find the workbook and double-click it.
2. Click in the next blank worksheet in the workbook. Select the 'Data' tab. Click 'Consolidate.' The Consolidate dialog box opens.
3. Select the function you will use to merge the data. If you are adding the merged values, select 'Sum.' Click the red box in the reference section.
4. Click the worksheet that contains the first set of data that will be merged. Highlight the range and click the 'Add' button. Select the worksheet containing the second set of data that will be merged. Highlight the range and click the 'Add' button.
5. Select the option that displays the location of the row or column headers in this merged data. Select either 'Top Row,' 'Left Column' or both. Click 'OK.' The data is merged in the new worksheet.
Read more ►

How to Define Cell Ranges in Excel


1. Enter Microsoft Excel and open a worksheet.
2. Select the range of cells (or even the nonadjacent cells) that you want to name.
3. Locate the name box at the top-left of the formula bar, just above the row and column headings.
4. Click in the box and type the name you want for the cell range.
5. Press Enter to accept the name.
6. Use the name in formulas you create.
Read more ►

How to Disable a Date Autofill in Excel


Disable the AutoComplete Option
1. Open the Excel file.
2. Click the 'File' tab on the command ribbon. A list of basic commands appears.
3. Click 'Options.' The 'Excel Options' window appears. The left pane includes the 'Advanced' command.
4. Click 'Advanced.' A list of editing options appears.
5. Clear the check box for 'Enable AutoComplete for cell values.'
6. Click 'OK.' This button is located at the bottom of the dialog window. The 'AutoComplete' command no longer copies the cell data to new cells. The worksheet is open for editing.
Disable the AutoFill Option
7. Open the Excel file.
8. Click the 'File' tab on the command ribbon.
9. Click 'Options.' A list of basic commands appears.
10. Click 'Advanced.' A list of editing options appears.
11. Clear the check box for 'Enable fill handle and cell drag-and-drop.'
12. Select the check box for 'Alert before overwriting cells.' This text may appear faint.
13. Click 'OK.' The worksheet opens. The fill handle does not activate in the corner of the cell.
Read more ►

How do I Create Mailing Labels in MS Word From an MS Excel Spreadsheet?


Prepare the Mailing List
1. Open a new spreadsheet and use the first row to create column headers across the top of the document. Name the top cell of each column to correspond with the address fields on your labels, for example: 'First Name,' 'Last Name,' and so on.
2. Start in the first cell on the second row and enter the data that corresponds with the cell header. For example, enter the recipient's first name in the row under the 'First Name' label. Continue until you have entered all of your mail recipients and save your document.
3. Highlight all of the data in the mailing list. Single-click on the first cell at the beginning of the first row of your address list. Scroll to the last cell, on the last row of your address list, hold down the 'shift' key and single-click on the last cell.
4. Click on the 'Formulas' tab, select the 'Define Names' group and click 'Define Name.' Type a name for your list and click 'OK.'
5. Save and close the Excel document.
Prepare the Label Document
6. Launch Microsoft Word to open a new document.
7. Click on the 'Mailings' tab and select the 'Start Mail Merge' group. Then click 'Start Mail Merge' and select 'Labels.' In Word 2010, select 'Start Mail Merge' from the 'Mailings' tab and select 'Labels.'
8. Select the type of printer, label vendor and type of labels. For example, if you are using an ink-jet printer to print Avery 15160 address labels, select 'Page Printers,' followed by 'Avery' from the 'Label Vendors' drop down, then scroll down and click on the correct product number.
9. Click 'OK' to have word create a blank label document.
Link the Labels to the Excel Document
10. Click the Microsoft Office Button in the upper left corner of the Microsoft Word screen. In Word 2010, click on 'File' and select 'Options.'
11. Select 'Advanced' and scroll to the 'General' section. Put a check in the box next to 'Confirm File Format Conversion on Open' and click 'OK.'
12. Click on the 'Mailings' tab and select the 'Start Mail Merge' group. Then click 'Select Recipients.' In Word 2010, select the 'Mailings' tab and click 'Select Recipients.'
13. Select 'Use Existing List' from the list of options. Navigate to the location of the Excel file, and double-click the file.
14. Highlight the name of the mailing list and click 'OK.' If prompted, select 'MS Excel Worksheets via DDE (*.xls)' and click 'OK.' All of the labels, except the first, should now say
>.
Format and Merge the Labels
15. Put your cursor in the first label field. Go to the 'Mailings' tab and click the down arrow next to 'Insert Merge Field.' Add each address field to the first label with proper formatting. For example:«first_name» «last_name»«address»«city», «state» «zip»
16. Click the 'Update Labels' button, to the right of the 'Insert Merge Fields' drop-down, to copy the address fields to the rest of the label template.
17. Click 'Preview Results' to view your labels.
18. Save your label template. Then click 'Finish and Merge.' Select 'Edit Individual Documents,' 'All,' and click 'OK.'
19. Save and print your new labels.
Read more ►

How to Subtract Using Excel 2007


1. Double-click the Excel 2007 icon on your desktop or in your 'Programs' list to open the program to a blank worksheet.
2. Enter the numbers you want to subtract in separate cells in the spreadsheet.You can select any cells you would like, but it's usually convenient to place the numbers next to one another either horizontally, in two separate columns, or vertically, in two separate rows.If you want to work with data from an existing spreadsheet, open that file to begin working on it.
3. Enter a subtraction formula in a blank cell. The formula is an equal sign, followed by the first cell reference minus the second cell reference.For example, if your two numbers are in cells A1 and A2, then use a blank cell to enter the formula:=A1-A2
4. Click on another blank cell to activate the formula you just entered. The text for the formula will disappear, and the subtraction amount will appear in its place.For example, if cell A1 contains 100 and A2 contains 25, then the cell with the formula will display 75, since 100 - 25 = 75.
Read more ►

How to Fill Cells With Colors in Microsoft Excel 2003


1. Select the cell that you wish to fill. You will need to left-click on the cell that you wish to fill with color in order to activate that cell.
2. Access the color-fill palette. The color-fill palette is located on the command bar and looks like an icon of a paint bucket spilling out paint. Simply left-click on this icon and a color palette will open.
3. Select a fill color. Once the color palette is open, just click on the desired color.
4. Fill the cell with the desired color. Once you click on the desired color, the cell will automatically fill with that color. You can easily change the color by following the above steps.
Read more ►

Saturday, April 13, 2013

How to Divide a Cell in Two


1. Launch Microsoft Excel. Double-click the name of the document you want to edit on your hard drive to open it in Microsoft Excel.
2. Insert a blank column next to the cell or column of cells you want to split. Highlight your cell by clicking on it (or the whole column by clicking on the column heading) and hold down the 'Alt,' 'I' and 'C' keys at the same time to insert a new column.
3. Open the 'Convert Text to Columns Wizard.' In Excel 2003 and earlier versions, drop-down the 'Data' menu and select 'Convert Text Into Columns.' In Excel 2007 and 2010, navigate to the 'Data' tab and click the 'Text to Columns' button.
4. Divide your cell or cells in two. Choose either 'Delimited' or 'Fixed Width,' depending on whether or not a comma (or other character) or a space separates the data you want to split, respectively. Click 'Next,' keeping in mind that you'll need to enter whichever character you used to separate your data on the next screen if you selected 'Delimited.' Click 'Finish.' Your cell (or column) is now divided in two.
Read more ►

How to Speed Up Excel 2007 Calculations


1. Open Excel. Click on the 'Formulas' tab at the top of the spreadsheet, which is between 'Page Layout' and 'Data.' Click on the 'Calculation Options' button that appears to the right; a drop-down menu appears. You will see that the 'Automatic' option is checked.
2. Select the 'Manual' option from the drop-down menu in order to disable automatic recalculations of all cells whenever a change is made to one. Now Excel will only recalculate when you explicitly tell it to do so. Alternatively, you may choose the option 'Automatic Except for Data Tables,' which will automatically make changes to all cells that are directly affected by a cell change except for those in data tables. This will prove faster than the 'Automatic' option, but slower than the 'Manual' option.
3. Click on the 'Options' button at the very top of Excel. From the drop-down menu, select the 'Excel Options' button at the very bottom. A new menu appears. Hit the button called 'Formulas.' More options appear to the right. Under the heading 'Calculation Options,' you will see that 'Automatic' is checked again. Click the 'Manual' bubble in order to enable this feature for Excel more generally. Another box that says 'Recalculate workbook before saving' is also checked. Uncheck this box so that Excel does not automatically calculate every time you save.
4. Go back to the 'Excel Options' menu and select 'Advanced.' From here, go to 'Formulas' and then look for an option called 'Enable multi-threaded calculations.' Check this box and then adjust the number of processors that you want your computer to dedicate to performing Excel calculations. More processors will increase the speed at which Excel calculates. Note that increasing this number will slow down other applications that your computer is running.
Read more ►

Friday, April 12, 2013

How to Turn Off AutoCorrect in Excel


1. Open the Excel file in which you want to disable AutoCorrect. Access the AutoCorrect Options dialog. In Excel 2007 or later, click the 'Office' button in the top left, then 'Excel Options,' then 'Proofing' and then 'AutoCorrect Options.' In earlier versions of Excel, click the 'Tools' menu, then 'AutoCorrect Options.'
2. Uncheck the box next to whatever AutoCorrect features you want to disable. To disable AutoCorrect entirely, uncheck every box.
3. Click 'OK' to finalize the changes and disable AutoCorrect.
Read more ►

Microsoft Excel: How to Remove a Drop


1. Left-click on the Office ribbon button and click on the 'Excel Options' button.
2. Select “Popular” in the Excel options box and then check the “Show the developer tab in the Ribbon” box
3. Click the “Review” tab and make sure the spreadsheet is an “Unprotected Sheet.”
4. Select “Design Mode” in the “Developer” tab.
5. Right-click on the drop-down box and select “Delete.”
Read more ►

How to Make an Excel Spreadsheet Expire


1. Open Microsoft Excel, go to the “File” menu and choose “Open.” Browse for the Excel file that you want to make expire and double-click the file name to open it.
2. Press “Alt F11” to launch the Visual Basic Editor from Excel. Use either “Ctrl R” to switch to the “Project Explorer” window or go to the “View” menu, point to “Other Windows” and choose “Project Explorer” from the list of options.
3. Right-click “ThisWorkbook” in the “Project Explorer” window. Select “View Code” from the list of available options.
4. Type the code that makes the spreadsheet expire into the code box provided. A proper code format that provides users with a message regarding the expiration date and the number of days left until the spreadsheet expires looks like this:
Sub Auto_Open()\\ Dim exdate As Date \\ exdate = \'12/01/10\' \\ If Date > exdate Then \\ MsgBox (\'Spreadsheet has Expired\') \\ ActiveWorkbook.Close \\ End If \\ MsgBox (\'Workbook Valid Until\' exdate - Date \'Days left\') \\ End Sub
Enter the date that the spreadsheet should expire by “exdate=” and any message you prefer regarding the expired worksheet in the place of “Spreadsheet has Expired.”
5. Return to the open Excel spreadsheet, go to the “File” menu and select “Save” to save the workbook with the new macro attached. Close the workbook and reopen it in Excel. The message you entered into the macro should appear, stating the expiration date of the file and how many days are left until it expires.
Read more ►

Thursday, April 11, 2013

How to Calculate Cells in Excel 2007


1. Open Microsoft Excel and create a worksheet with the data you want to calculate. Place your cursor in an empty cell below or beside the column you want to calculate.
2. Select the 'Formula' tab from the Ribbon and click on 'AutoSum' under the 'Function Library' group. To add all the numbers in a column, click on the 'AutoSum' button. Excel 2007 will select the calculated cells on the worksheet for you.
3. Press the 'Enter' key to accept the calculation or manually select the cells you want to calculate, then press the 'Enter' key.
4. Repeat the calculation for additional columns or rows, or copy the formula to your other cells. To copy your calculation to another cell, select the cell with the formula. Then place your mouse just outside the cell (on the bottom-right portion of the cell) until your mouse becomes a plus sign.
5. Drag the cell in the direction of the cells where you want the formula added. The formula will be repeated on those cells.
6. Calculate the average of numbers by placing your cursor in an empty cell and clicking on the arrow for the 'AutoSum' button in the 'Formula' tab. Then click on 'Average.'
7. Use a different formula by clicking on 'More Functions' within 'AutoSum.' Find the formula you need by searching for the function or changing the category and selecting it. The 'Function Arguments' dialog box will open up
8. Click on the button to the right of the dialog box and select the range of values you need. Click on the button again to return to the 'Functions Argument' box. Then press 'OK' when you are finished.
Read more ►

How to Convert Quicken to Excel 2003


1. Open the 'Report' menu from the top of the Quicken window.
2. Select 'Reports and Graph Center.' Choose the information that you want to export to Microsoft Excel. Specify the start date and end date of the information you want to export. Quicken will turn that information into a report that will open on screen.
3. Click the 'Copy' button in the Quicken toolbar. This will copy all of the data contained in your Quicken report to the Windows clipboard.
4. Open Microsoft Excel 2003 on your computer. Select 'New' and 'Blank Spreadsheet' to create a new blank spreadsheet on screen.
5. Use your computer mouse to highlight all of the cells in your Excel 2003 spreadsheet where you want your Quicken information to go. Click the 'Paste' option in the Excel toolbar at the top of the screen to paste all of your information from Quicken into the Excel spreadsheet.
Read more ►

Thursday, March 28, 2013

How to Calculate Upper Lower Limits With Excel 2007


1. Select a blank cell where you would like to display the minimum or maximum value. If the range of values is all in one contiguous row or column, select a cell below or to the right of it.
2. Click the arrow next to the 'AutoSum' button in the 'Editing' group on the 'Home' tab of the Excel Ribbon.
3. Select 'Max' if you would like to calculate the maximum value or 'Min' if you would like to calculate the minimum value.
4. Select the data you for which you would like to find the minimum and maximum value. If the numbers are in a contiguous column or row, Excel should select them automatically. If they numbers are not in contiguous columns or rows, click on them one by one while holding down the 'Ctrl' key.
5. Press 'Enter.' Excel will calculate the largest or smallest value in a set of numbers.
Read more ►

How to Buy Microsoft Excel Software


1. Determine the primary functions that you will be using Excel to accomplish. These may include academic, business or other spreadsheet-related matters, such as tracking projects or spending.
2. Choose a version of Excel to purchase based on the functions you will use the software to accomplish. If you intend to conduct extensive spreadsheet analysis or business accounting, then consider purchasing Microsoft Excel 2007 in order to ensure full compatibility with other software products. If you are going to do basic, at-home accounting, then Excel 1997 will fit your needs. Also, if you intend on sharing Excel files with others, Excel 2007 is the only version that supports the Open Office spreadsheet format that can be read by all current spreadsheet programs.
3. Go the Microsoft Office online store listed in the resources section of this article. Note the cost of Microsoft Excel that is listed on the website and compare to the cost listed by other resellers for the same version of Excel. Choose the website that offers you the best price and return policy for the version of Excel you have decided to purchase and and buy the software.
Read more ►

How to Enable VBA in Excel 2003


1. Click the 'Start' button from your desktop and then move your mouse over the 'All Programs' option.
2. Select the 'Microsoft Excel 2003' option from the list of programs. Excel 2003 will then open on your screen.
3. Click the 'Tools' option from the top toolbar menu and then move your mouse over the 'Macro' option.
4. Click the 'Security' option and then select the 'Trusted Publishers' tab from the Security dialog box.
5. Select the 'Trust Access to Visual Basic Project' option and then click the 'OK' button. VBA will then be enabled in Excel 2003.
Read more ►

Wednesday, March 27, 2013

How to Align Double


1. Go to the 'Insert' tab of the ribbon and click on 'Text Box' in the 'Text' group. Draw a text box onto the worksheet and click on the text box to select it. The 'Drawing Tools' tab appears when the text box is selected. Click on the 'Format' tab under 'Drawing Tools.'
2. Click the dialog launcher button in the bottom right corner of the 'Size' group to open the 'Format Shape' dialog box with the 'Size' tab selected. Clear the 'Lock Aspect Ratio' check box, if it is selected. Change the number in the 'Height' box to '2.' Change the number in the 'Width' box to '3.5' and click the 'Close' button. Drag the text box to the upper left corner of the worksheet.
3. Select the text box. Right-click it and select 'Copy.' Press 'Ctrl V' to paste the copy of the text box. Drag the copied text box to the right of the first one, using the grid lines to align them side by side. Copy and paste six more text boxes, placing three beneath the first box and three beneath the second. Click on cell 'K47' and keep the mouse button depressed. Drag the mouse up to cell 'A1' to select all of the cells containing business cards. Click on cell 'L1.' Press 'Ctrl V' to paste the business cards onto the second page.
4. Press and hold the 'Ctrl' button and click on the four business cards in the first column. Go to the 'Format' tab under 'Drawing Tools.' Click on 'Align' in the 'Arrange' group and choose 'Align Left.' Select the boxes in the second column and repeat the steps, choosing 'Align Right' instead. Align the boxes in the first column of the second page to the left and the final column to the right.
5. Press and hold the 'Ctrl' button and click on the four business cards across the top of the two pages, which should appear side by side. Go to the 'Format' tab under 'Drawing Tools.' Click on 'Align' in the 'Arrange' group and choose 'Align Top.' Repeat for the remaining rows to align all of the cards on both sides.
6. Type the information that you want on the front of the business cards into the text boxes on the first page. Type the data for the backs of the business cards into the text boxes on the second page. Add images by clicking on the 'Insert' tab and selecting 'Picture' to add an image from your computer. Select 'Clip Art' to add a Microsoft clip art image.
7. Press 'Ctrl P' to open the print dialog box. Click the 'Properties' button to open the 'Printer Properties' dialog box. Select the duplex printing option, which will vary depending on your printer. It may be called 'Print on Both Sides,' 'Flip on Long or Short Edge' or something similar. Close the dialog box and print your double-sided business cards.
Read more ►

How to Protect XLS Cells From Changes


1. Click 'Start,' followed by 'All Programs,' 'Microsoft Office' and 'Microsoft Excel 2010' to launch Excel. Alternatively, launch Excel by double-clicking on a desktop shortcut or an Excel file in Windows Explorer.
2. Navigate to the worksheet that contains the cells you wish to protect.
3. Click the 'Select All' button to select the entire worksheet. It has a small triangular shape and is located in the upper left corner of the spreadsheet between the column 'A' label cell and the row '1' label cell.
4. Click the 'Home' tab. Click 'Format' in the 'Cells' group and click 'Format Cells' from the context menu.
5. Click the 'Protection' tab and clear the 'Locked' check box and click 'OK' to unlock the entire worksheet. Excel locks all cells on a worksheet by default and to lock specific cells it is necessary to unlock the worksheet first and then lock specific cells.
6. Click once on the first cell that is to be protected to select it.
7. Hold down the 'CTRL' key on the keyboard. Click additional cells individually to select them or drag the mouse over a range of cells to select the entire range. Release the 'CTRL' key when all cells on the sheet that are to be protected have been selected.
8. Click the 'Home' tab. Click 'Format' in the 'Cells' group and click 'Format Cells' from the context menu.
9. Click the 'Protection' tab and click the 'Locked' check box and click 'OK' to lock the selected cells.
10. Click 'Format' in the 'Cells' group on the 'Home' tab. Click 'Protect Sheet' and ensure the check box is checked for 'Protect Worksheet and Contents of Locked Cells.' Supply a password if desired and select additional options from the list as necessary to enable users to work with the spreadsheet. Click 'OK' to protect the worksheet and lock the cells.
Read more ►

How to Fill a Word Receipt Form With Excel Data


1. Open your receipt document. Click the 'Mailings' tab.
2. Click the 'Select Recipients' button. Select 'Use Existing List.' Navigate to the location of your Excel spreadsheet. Click the Excel file's name. Click the 'Open' button.
3. Select the worksheet that contains your customer data from the 'Select Table' dialog box. Leave the 'First row of data contains column headers' option checked if it applies to your spreadsheet. For example, it would apply if the first row and cell of your spreadsheet is 'Name' instead of the actual name of a customer. Click the 'OK' button.
4. Click on the place on your receipt where you want to insert the name of your customer. Click on the 'Insert Merge Field' button. Select the field that corresponds to your customer's name. These fields are from your Excel spreadsheet.
5. Repeat Step 4 for all of the customer information you want on your receipt.
6. Click the 'Preview Results' button. You can see the rest of your customer's receipts by clicking on the forward arrow next to the '1.'
7. Click the 'Finish Merge' button. Selecting 'Edit Individual Documents' will create new documents for each receipt. Selecting 'Print Documents' will print each receipt without creating new documents. Select the option that works best for you.
Read more ►

How to Count Cells That Are Not Blank in Excel 2007


1. Open your Excel workbook to the worksheet where you want the data counted.
2. Click in any open cell where you would like the result to appear.
3. Type '=COUNTA(' (without the quotation marks), but do not press 'Enter.' Make sure you include both the equal sign and the left paren.
4. Highlight the range of cells where you want the non-blank entries counted, using your mouse.
5. Release the mouse button and press 'Enter.' The results of the count will appear in the cell where you entered the formula.
Read more ►

How to Format Lines in an Excel Chart


1. Start Microsoft Excel 2007 and open a spreadsheet from your files that contains a chart to which you want to format the lines of.
2. Right-click on top of the line in the Excel chart that you want to format to display the shortcut menu. Choose 'Format (name of line)' from the shortcut list. The name of the line you are formatting will appear after the word 'Format' in the shortcut list. The 'Format' dialog box will open.
3. Select 'Line Color' from the list on the left side of the 'Format' dialog box if necessary. The line color options will be displayed on the right side of the dialog box. Choose 'Solid line,' 'Gradient line' or 'Automatic' to set the line color. If you choose one of the first two, you will see more formatting options appear. You can then specify the color, transparency and gradient settings for the line.
4. Click 'Line Style' from the list on the left side of the 'Format' dialog box. Set the width, compound type, dashed type, cap type and join type using the drop-down lists and boxes. Specify the type of arrow you want to use if applicable in the last part of this section.
5. Choose the 'Shadow' option on the left side of the dialog box to display the shadow settings for the line. Choose a shadow from the preset shadows and set the color. Use the sliders to set the transparency, size, blue, angle and distance of the shadow.
6. Click the 'Close' button to close the 'Format' dialog box and save your settings. You will return to your Excel chart with your newly-formatted lines visible.
Read more ►

Tuesday, March 26, 2013

How to Use VLookup With Different Sheets on Excel


1. Type the following data into 'Sheet1' of an Excel workbook, pressing 'Tab' in place of the commas. This table data associates a job code with a description for that code. This lookup table lets tables on any other sheet in the workbook use a code in place of typing the full description.D, PainterE, SculptorF, Designer
2. Click the top left cell of the lookup table just entered, then drag to the bottom right cell. This action selects the table. Type the name 'jobs' in the text box to the left of Excel's 'Formula bar,' which sits directly above the worksheet grid. The text box into which you typed is the 'Range name' box.
3. Click cell 'C4' of any sheet in your workbook besides 'Sheet,' then type the following sample table. This table uses a job code, for which the 'vlookup' function will display descriptive text.Name, Job codeClark Kent, dLois Lane, e
4. Type the text 'Job description' in the cell to the right of the 'Job code' header. Click the cell below the 'Job description' header and type this function: 'vlookup (D5, jobs, 2)'. Notice that Excel displays the job description where you typed the 'vlookup' function. That function's first argument is a cell reference for the cell containing the first job code of your sample table from step 3. The second argument refers to the lookup table. The third argument is the index of the job description column in the lookup table.
5. Click the lower right corner of the cell you just typed, then drag down to the last row in your sample table. This fills the 'Job description' column with the job descriptions that the 'vlookup' function provides. Note that these descriptions are on a different sheet from that of the sample table.
Read more ►

How to Widen Bars in Excel 2007


1. Open the chart that contains the bars that you want to widen. Click once directly on the data series (bar) that you want to widen. Note that all of the bars in the data series will widen equally, regardless of which you select. This action will launch a separate pop-up window.
2. Click the 'Format' tab and locate the 'Current Selection' section. Click the 'Format Selection' option.
3. Locate the 'Gap Width Field in the 'Series Options' section. Enter a value in the 'Gap Width' field that is lower than the current value. For example if the current gap width is listed as 150, try 125 or 100. The lower the number, the wider the bars become. You may have to try a few different gap width settings until you find an appropriate width. Click 'Close.'
Read more ►

Monday, March 25, 2013

How to Use Conditional Formatting in Microsoft Excel


1. Apply conditional formats to monitor cell values, such as formula results. Stay abreast of dramatic changes affecting your organization, business or personal financial situation like donations increase, sales decline or a bank account overdraft.
2. Know when conditions change. Utilize the conditional formatting color coding system to signal those changes (e.g., red text color can indicate a turn down in stock value). Use conditional formatting in Microsoft Excel to recognize changing conditions to better control the direction and outcome of activities.
3. Open Microsoft Excel and the file you want to affect. Select cells for applying conditional formatting. Use conditional formatting in Microsoft Excel for automatic cell change when a specific condition is met.
4. Choose 'Conditional Formatting' from the 'Format' menu within Excel. See a 'Conditional Formatting' box pop up with options to choose from. Select the 'Cell Value Is' option from the first drop-down menu to utilize existing values in the cells you've selected in the formatting condition. Pick 'Formula Is' instead to enter a formula for the formatting standard if you want to assess information or a condition besides that associated with the values within the cells selected.
5. Type in a constant (number or text) after you have selected the comparison phrase (e.g., 'less than' or 'equal to') for 'Cell Value Is.' Realize a constant is a value that doesn't change since it's not calculated. Start with an equal sign if you choose to enter in a formula.
6. Enter the formula which will evaluate to a logical value, TRUE or FALSE for the formatting criteria for 'Formula Is.' Apply formatting when another condition is met (not based on the evaluation of a condition or data of the selected cell value).
7. Hit the 'Format' button next so that you can access formatting options. Choose one of the tabs, 'Font,' 'Border' or 'Patterns,' once the 'Format Cells' option box pops up. Make your preferred formatting selections to be applied to a condition change or when the formula calculates to TRUE within your selected cells.
8. Click 'Add>>' to include a second or third condition to selected cells. Repeat the same procedure you used for the first condition. Keep in mind that the first condition takes precedence and not all conditions, if met all at once, will be applied by Excel.
Read more ►

Sunday, March 24, 2013

How to Make a Formula in Excel


Simple Math Formulas
1. Open Microsoft Excel by clicking on your Desktop shortcut or by selecting it from the Windows Start menu. You will automatically get an Excel Workbook. The workbook has three blank worksheets that Excel has named Sheet1, Sheet 2 and Sheet 3.
2. Remember that each column has a title that Excel uses in formulas, and each row has a number. The place in a worksheet where a row and column meet is a cell. Each cell's name is made with a column letter and the row number. The first cell at the top of the worksheet is A1. The next cell to the right is B1.
3. Start typing your numbers that you want to calculate in Sheet 1. You can type the numbers in a single column or you can type them across the rows. You also have the option of typing your data in more than one column or row.
4. Put the cursor in the first empty cell below the data in your column or at the end of your first row. Notice that Excel displays the cursor location and cell name below the toolbars at the top of the screen.
5. Begin every formula by typing the equal sign. Excel immediately displays it in the Formula Bar that is above the worksheet's first row.
6. Click the first cell that you want to include in your formula. Excel adds the cell name to your formula. Type a mathematical function. If you are adding numbers, use the plus sign on your keyboard. For subtraction, use the minus sign. To divide, use the slash on the question mark key. To multiply, Excel uses the asterisk, above the number 8 on the keyboard.
7. Click each cell that you want in your formula, then type the arithmetic function that you need. Click the green check mark at the beginning of the formula bar when your formula is finished.
AutoSum
8. Use Excel's AutoSum button for faster addition of one column or row of numbers. It will be much easier than clicking on each cell and typing the plus sign.
9. Place your cursor in the first empty cell after your numbers. Click the AutoSum button. It resembles a fancy capital letter E and is located on Excel's editing toolbar. Excel highlights all the cells in the column above until it reaches an empty cell. For data in rows, Excel selects data to the left of the cursor.
10. Click the green check mark in the formula bar to complete the formula.
Use Excel Functions in Formulas
11. Make a formula with Excel's built-in mathematical functions by placing your cursor in an empty cell first. Then type the equal sign to start the formula. Choose Formulas, and then choose Insert Function in Excel 2007. In Excel 2003, click to open the Insert menu, then select Function from the drop-down list.
12. Select the function that you want. Your choices include financial, date, time, statistical, engineering, trigonometry, logical and many others, arranged in categories. To see all of the functions, select All in the category drop-down box.
13. Select the function that you need by highlighting it in the function list. Click on it to insert it into your formula. Excel places it in the formula bar. Select the function COUNT or AVERAGE to try this out.
14. Complete your formula by clicking each cell that you want to include in the formula. After you finish adding cells, click the green check mark. Excel displays the results of your function's calculation.
Read more ►

How to Create a Conditional Formula in Excel 2007


1. Open an existing or new Microsoft Excel 2007 document. Click 'Start' from the main operating system menu. Then choose 'Programs' from the start menu. Select 'Microsoft Office' from the programs menu. Next, choose 'Microsoft Excel' from the Microsoft Office menu. Now either choose 'New' from the main menu or locate the existing Excel document to use for a conditional formula.
2. Establish the parameters for the conditional formula. For example maybe a person can create a conditional formula for an invoice discount. That way if the invoice is paid in under a certain number of days then a client would receive a percentage discount.
3. Structure the conditional formula. In this example say that if the invoice is paid in less than thirty days, the customer receives a five percent discount. The conditional formula would look like this: IF((C5-B5)
4. Type the conditional formula in the spreadsheet. A person can either type the formula in or use the conditional function. To use the conditional function click on the 'Formulas' tab and choose the 'Logical' option from the format library.
5. Enter the conditional formula in the 'If' formula function dialogue box. Once the 'If' function dialogue box appears enter the previous formula parameter in the provided input boxes. In the 'Logical_test' box enter 'C5-B5
6. Test and save the Microsoft Excel 2007 spreadsheet. Be sure that the conditional formula is working correctly by observing the results and check them against the known data. When the formula is satisfactory, save and close the spreadsheet.
Read more ►

How to Set Up an Employee Schedule in Excel


1.
Open a blank Excel worksheet. Go to the 'Insert' tab in Excel 2007 or the 'View' menu in Excel 2003 or earlier and select 'Header Footer.' Select 'Custom Header' and enter 'Employee Schedule.'
2.
Go to the 'Page Layout' tab in Excel 2007 and click on 'Orientation.' Select 'Landscape.' In Excel 2003 or earlier, go to the 'File' menu and select 'Page Setup.' Select 'Landscape' on the 'Page' tab. Enter 'For the week of:' in cell 'A1.' Select cell 'A4.'
3.
Set up the cell by changing the font size to '12' and select 'Bold' in the 'Font' group of the 'Home' tab in Excel 2007 or on the 'Formatting' toolbar in Excel 2003 or earlier. Type in the first day of the scheduled workweek for employees.
4.
Select cell 'B4.' Type in the first scheduled time for employees to start work. Enter increments of time in the remaining cells of 'Row 4.' These may be hour or half hour increments, or you might want to only enter shift change times.
5.
Select cell 'A5.' Enter the name of the first employee by alphabetical order. In cell 'A6,' enter the next employee alphabetically, and so on until all employees are set up in the schedule.
6.
Skip a row and repeat steps 3 through 5 with the second day of the workweek. Repeat with the remaining days of the workweek. Add color to the cells containing days, times and employee names by using the 'Fill Color' button in the 'Font' group of the 'Home' tab in Excel 2007, or the 'Formatting' toolbar in Excel 2003 or earlier.
7.
Save the employee schedule as an Excel template. Click the 'Office Button' in Excel 2007 or the 'File' menu in Excel 2003 or earlier and select 'Save As.' Enter 'Employee Schedule' under 'File Name.' Select 'Excel Template (*.xltx)' under 'Save as Type' in Excel 2007 or 'Template' in Excel 2003 or earlier and click 'Save.' This will allow you to reuse the schedule without having to set it up again.
Read more ►

Saturday, March 23, 2013

How to Remove 'Getting Started' From Microsoft Excel


1. Launch the Excel application.
2. Click 'Tools' and then 'Options.'
3. Click the 'View' tab. Remove the check mark next to 'Startup Task Pane' and click 'OK' in the dialog box.
Read more ►

Friday, March 22, 2013

How to Make an Excel Sheet Into a Form


1. Open Excel 2010 and select a workbook containing data. Click the 'File' tab and select 'Open.' Browse the files and locate the workbook. Click the workbook and select the 'Open' button. The workbook will open.
2. Add the form to the Quick Access Toolbar by clicking the right drop-down arrow on the Quick Access Toolbar. Select 'More Commands.' Click 'All Commands' in the 'Choose Commands From' section. Drag the scroll bar down and click 'Form.' Click the 'Add' button to add the button to the Quick Access Toolbar. Click 'OK.'
3. Highlight the data in your spreadsheet. Make sure you highlight the column headers too. Click the 'Form' button. A form will appear containing your data.
Read more ►

How to Create a Flow Chart in Excel 2007


1.
Open an Excel worksheet. Go to the 'Insert' tab of the ribbon and click the 'Shapes' drop-down arrow. Select the first shape you want to use from the 'Flow Chart' shape gallery.
2.
Click on the worksheet where you want to place the first shape. Go back to the 'Shapes' gallery, click on the next shape you want to use and place it on the worksheet. You can click on a shape, hold down the mouse button and drag it into position as well. Repeat until all the shapes you need are on the worksheet.
3.
Go back to the 'Shapes' gallery and select the first connector you want to use in the 'Lines' gallery. Click on the first shape in the flowchart from where you want the connector line to begin. Click on the second shape where you want the connector line to end. Repeat with the remaining shapes.
4.
Right-click the first shape and select 'Add Text.' Type a brief description for the first step in the process. Repeat with the remaining steps.
5.
Go to the 'Format' tab and select a color scheme from the 'Smart Art' gallery, or apply effects such as '3-D' or beveled lines. Save the worksheet when you are finished.
Read more ►

How to Reduce File Size in Excel Without Using Software


1. Click on any cell in the Excel file you want to reduce. Open the 'Go To' dialog box in Excel 2007 by clicking the 'Find' drop-down arrow on the Home tab of the ribbon and selecting 'Go To.' In Excel 2003 or earlier, go to the 'Edit' menu and select 'Go To.' You can also use the keyboard shortcut 'Ctrl G' in any version of Excel.
2. Click the 'Special' button on the 'Go To' dialog box. Select 'Blanks' and click 'OK.' Click the 'Clear' drop-down in the 'Editing' group in Excel 2007 and select 'Clear All.' In Excel 2003 or earlier, go to the 'Edit' menu, point to 'Clear' and select 'All.' This will clear any blank cells to help reduce the file size.
3. Go to the very last cell containing data in the Excel file. Select the entire row beneath this cell by clicking the number to the left of it. Hold 'Ctrl Shift' and then press the down arrow key to select all of the cells beneath this point.
4. Click the 'Clear' drop-down in the 'Editing' group in Excel 2007 and select 'Clear All.' In Excel 2003 or earlier, go to the 'Edit' menu, point to 'Clear' and select 'All.' Select the column to the right of the last cell. Hold 'Ctrl Shift' and then press the right arrow key. Again, select 'Clear All.'
5. Repeat Steps 1 through 5 for any other Excel files that are linked to the workbook you are trying to reduce. Save the files and then check the new file size by clicking the 'Office Button' in Excel 2007. Point to 'Prepare' and then click on 'Properties.' In Excel 2003 or earlier, go to the 'File' menu and select 'Properties.' The file size will be listed on the 'General' tab.
Read more ►

Thursday, March 21, 2013

Microsoft Excel 2003 Is Slow in the Page Break Preview


1. Open the Microsoft Excel 2003 file on your computer that contains the page breaks that you are having problems with.
2. Click the 'Tool' menu from the top of the page and then click the 'Options' button. The Options dialog box will then appear on your screen.
3. Click the 'View' tab. Click the box next to the 'Page breaks' field so it is no longer checked.
4. Click the 'OK' button and you will close the dialog box. The page breaks view will be disabled.
5. Click the 'File' option and then click the 'Page Setup' option. Select any changes to meet your preferences and then return to your document.
Read more ►

How to Use Excel to Calculate Coefficient of Variation


1. Enter the data to be analyzed in one column of an Excel spreadsheet.
2. Use the Excel AVERAGE() function to calculate the mean of the data. In a blank cell on the spreadsheet, type '=AVERAGE(' (without quotes) and highlight all of the cells containing the data. Press 'Enter' to see the mean of the data.
3. Use the Excel STDEV() function to calculate the standard deviation of the data. In a second empty cell, type '=STDEV(' (without quotes) and highlight the data. Press 'Enter' to view the standard deviation of the data.
4. Divide the standard deviation by the mean: in a third empty cell, type '=' (without quotes) and click on the cell containing the standard deviation. Type '/' (without quotes) and click on the cell containing the mean. Press 'Enter' to view the coefficient of variation.
Read more ►

How to Convert Multiple Columns in Excel to a Single List in Word


1. Open the Excel 2010 file that you want to work with. Right-click on the 'A' above the first column and choose 'Insert.' This creates a blank first column that you will use to construct your list.
2. Press 'Alt' and 'F11' to launch the Excel VBA console. Right-click on any worksheet in your current workbook -- these are listed on the left side of the console -- move your mouse over 'Insert' and choose 'Module.' Double-click on the module which appears in the list.
3. Copy the following code and paste it into the white space on the right side of the VBA console:Sub Combine()Range('B1').SelectDo While ActiveCell > ''Range(ActiveCell, ActiveCell.End(xlDown)).Copy Destination:=Range('A10000').End(xlUp).Offset(1, 0)ActiveCell.Offset(0, 1).SelectLoopEnd SubThis code creates a macro, called 'Combine,' which combines all adjacent columns, starting with column 'B,' into one long list in column 'A.' If you think the total number of cells will be larger than 10,000, increase the number '10000' in the code so that it will be larger than the number of all your cells combined. The macro runs until it encounters a blank cell in the top row of a column.
4. Click the 'Play' button in the middle of the bar at the top of the VBA console. This creates your list in column 'A.' Click the 'X' in the top-right corner of the VBA console to close it.
5. Select the 'A' above the first column to select the entire column. Press 'Ctrl' and 'C' to copy the information to your clipboard.
6. Open the Microsoft Word 2010 file where you want to paste the list. Click the document to place your cursor wherever you want to insert the list.
7. Click the bottom of the 'Paste' button to open up a pop-up window. Choose the icon labeled with a large 'A' to insert the information as text.
8. Click the last item in the inserted items and hold the mouse button down. Drag the mouse up to the first item and release the mouse button, selecting the entire range of items. Click the 'Home' tab at the top of the screen and find the 'Paragraph' section. Click the 'Bullets' or 'Numbering' buttons to turn the information into a list.
Read more ►

How to Change Pivot Table Source Data


Microsoft Excel 2007
1. Open the Microsoft Excel 2007 application on your computer. Click on the “Microsoft Office” button from the top-right corner of the application.
2. Click on the “Open” option and then locate the Excel 2007 file that contains the pivot table for which you want to change the data source. Click on the “Open” button.
3. Click on the “Options” tab from the top toolbar menu and then click on the “Change Data Source” button from the “Data” group.
4. Click on the radio button next to the “Select a table or range” field. Click on the button in the “Table/Range” field.
5. Select the new range for your data within the pivot table and then click the “OK” button in the Change Pivot Table Data Source dialog box.
Microsoft Excel 2003
6. Open the Microsoft Excel 2003 application on your computer. Click on the “File” option from the top toolbar menu.
7. Click on the “Open” option and then find the Excel 2003 file that contain the pivot table for which you want to change the data source. Select the file and then click on the “Open” button.
8. Right-click on any cell in the pivot table and then click on the “Wizard” option. The PivotTable and PivotChart Wizard will appear on the screen.
9. Click on the “Back” button. Select the radio button next to the “Existing worksheet” field and then click inside of the text box.
10. Select the new range for your pivot table within the spreadsheet and then click on the “Finish” button from the dialog box.
Read more ►

Wednesday, March 20, 2013

How to Find Delete Multiple Instances in Excel


1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office' followed by 'Microsoft Excel' to open the spreadsheet software.
2. Click the 'File' ribbon tab, then click 'Open.' Double-click the Excel spreadsheet file that contains the duplicate values.
3. Highlight all the cells you want to check for duplicates. Click the 'Data' ribbon tab at the top of the window. Click 'Delete Duplicates' to start the wizard.
4. Check the box for each column you want to check from the selection. If you want to check all columns, click 'Select All.'
5. Click 'OK' to run the duplicate checker. The window displays the number of duplicates found. Click 'OK' to delete the duplicate instances.
Read more ►

How to Unlock an Excel Workbook Without Knowing the Password


1. Download and install Excel Password Remover 2010 from Straxx.com (see Resources). This is an Excel add-in file that will place two new options, 'Unlock sheet' and 'Unlock Workbook,' under the 'Tools' menu. This is an effective, free program.
2. Try Passware Kit Basic 10.1 (see Resources). This software can recover passwords for Microsoft Word, Excel, and Powerpoint files, as well as passwords for email accounts, network connections and local Administrators.
3. Download Excel Password Recovery Master (see Resources). This software offers near instant recovery of password. Length and complexity of passwords do not affect the speed at which they are recovered. Excel Password Recovery Master also supports Multilingual passwords.
Read more ►

How to Convert MS Excel Files to PDF


1. Open the MS Excel file that contains your document.
2. Ensure that the 'Print Area' has been properly defined. Go to 'Page Layout,' click 'Print Area' and finally, select 'Set Print Area' from the main menu. You will see a dotted line that represents the selected print area.
3. Select 'File' then 'Print' from the main menu. The 'Print and Printer' selection menu appears.
4. Select 'Adobe PDF' from the Printer drop-down selection.
5. Click 'Print'. The 'Save PDF File As' dialog window appears.
6. Navigate to the directory where you wish to save your PDF files and click the 'Save' button. The Adobe PDF progress bar appears and the PDF file is now created.
Read more ►

How to Change Ribbons in Excel 2007


1. Open Excel 2007.
2. Click on one of the following tabs: 'Insert,' 'Page Layout,' 'Formulas,' 'Data,' 'Review' or 'View.' If you previously placed a check in the Excel Options box labeled 'Show Developer tab in the Ribbon,' the rightmost tab you will see is the 'Developer' tab.
3. Click on 'Home' to return to the 'Home' Ribbon.
Read more ►

Blogger news