May 2012 - Excel

Browse » Home » Archives for May 2012

Monday, May 28, 2012

How Can I Put a Last Updated in My Excel Workbook?

Create a User-Defined Function
1. Open the workbook and go to the sheet where you will be inserting the last updated date.
2. Click on 'Tools,' 'Macro' and 'Visual Basic Editor,' or just press the 'Alt' and 'F11' key at the same time.
3. Click on 'Insert' and select 'Module.' Your cursor will automatically be placed at the beginning of the open module.
4. Type (or copy and paste) the following user-defined function command into the module:Function SavedDate() As Date
Application.Volatile
SavedDate = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
End Function
5. Click on 'File' and 'Close and Return to Microsoft Excel.'
Insert Last Updated Date
6. Go to the cell in your worksheet where you want to display the last updated date.
7. Type, or copy and paste, the following function:=SavedDate()
8. Press 'Enter.'
9. Right-click on your cell and select the 'Format Cells' menu.
10. Format your date by selecting the 'Date' category and choosing how you want your date to appear from the'Type' option. Click 'OK.'

How to Cross Reference Between Excel Spreadsheets

1. Launch Microsoft Excel and open the spreadsheet that is going to display information contained in other files.
2. Open each of the other files that has information you want to reference in the display spreadsheet.
3. Switch back to the display spreadsheet and click once in the cell that is going to display information from another spreadsheet.
4. Press the 'Equal' ('=') key on the keyboard and immediately switch to the spreadsheet that contains the first piece of information that needs to be displayed.
5. Click the cell that contains the information that will be displayed in the display spreadsheet and press 'Enter.' The display spreadsheet now contains a reference to the information contained in the other spreadsheet, and this information will automatically update when changes are made to the data.

How to Import PDFs Into Excel 2007

1. Open Adobe Acrobat Reader. Click 'File' and select 'Open File.' Select the file that you want to import into Excel.
2. Press and hold the 'Shift' and 'V' keys on your computer. This opend the Table/Formatted Text tool.
3. Click and drag a box around the table in the PDF that you want to import. This highlights the table. Select 'Save As...' from the window that appears.
4. Type a name for your new file. Click the drop-down menu next to 'Save As Type.' Choose 'ANSI Text' from the list. Choose a destination folder for your new file and click the 'Save' button.
5. Open the Excel 2007 program. Click 'File' and select 'Open.' Navigate to find the ANSI file you've just saved. Click the 'OK' button. Click the 'Delimited' option in the new window and then click the 'Next' button. The table appears in an Excel spreadsheet as it appeared in your PDF document.

How to Freeze the Top Rows in an Excel 2007 Document So They Appear at the Top of Every Page

1. Open the Excel 2007 spreadsheet.
2. Identify the row or rows at the top of the spreadsheet that you wish to freeze. Often just the first row is needed for this. You may choose multiple rows if desired.
3. Click in the first row of the spreadsheet that is not to be frozen. That is, click any cell in the first row at the top of the spreadsheet that will be a part of the rows that will scroll. All other rows above this row will become frozen.
4. Click the 'View' tab at the top of the Excel 2007 ribbon bar.
5. Identify the 'Window' group on the 'View' tab. Click the menu arrow that appears under the 'Freeze Panes' button. A menu will appear.
6. Click the 'Freeze Top Row' option if you are only freezing one row. If freezing multiple rows at the top of the spreadsheet, choose the 'Freeze Panes' option. The rows are now frozen.
7. Click the same menu and choose 'Unfreeze Panes' when you wish to return the spreadsheet to its initial state.

Sunday, May 27, 2012

How to Calculate a Moving Average in Excel

1. Open a new worksheet in Microsoft Excel. Enter dates and their corresponding data points in two columns. For instance, to analyze monthly revenue figures, enter each month in column A and the corresponding revenue figure next to it in column B. A year's worth of data, then, would fill cells A1 through A12 and B1 through B12.
2. Determine the time interval of the moving average you want to calculate, such as a three-month or six-month moving average. Go to the last value of the first interval and click on the corresponding empty cell to the right. Using the example from Step 1, if you want to calculate a three-month moving average, you would click on cell C3 because B3 contains the last value of the first three months of the year.
3. Use the AVERAGE function and type a formula into the empty cell you selected, specifying the data range for the first interval. In this example, you would type '=AVERAGE(B1:B3)'.
4. Position your mouse on the lower right corner of the cell with the formula until you see a ' .' Left click and drag the formula down to the empty cell next to the last data point in the adjacent column. In the example above, you would drag the formula from cell C3 down to cell C12 to calculate the three-month moving average for the rest of the year.

How to Convert Open Office Files to Microsoft Excel

1. Download and install the Open Office suite if it isn't already on the computer.
2. Open 'Open Office Calc.'
3. Click the 'Open' icon (folder that's open) at the top of the program.
4. Choose the Open Office file you want to convert to Microsoft Excel and click 'Open.'
5. Click the 'File' menu and choose 'Save As' and a window will appear.
6. Select the drop box labeled 'Save as type,' and choose one of the listed types of Microsoft Excel formats. The format you select will be determined by the version of Microsoft Excel you plan on using to open the converted file (such as Microsoft Excel 5.0 or Microsoft Excel 95).
7. Rename the file in the 'File name' box if you wish, and then click 'Save' to convert the file.

How to Plot a Scatter Chart in Excel 2007

1. Enter labels into the first row of two columns and enter the data beneath it.
2. Click and drag the mouse over all of the data you entered into your spreadsheet to highlight it, including the labels.
3. Click the 'Insert' tab on the menu. Click 'Scatter' in the Charts section and select one of the five scatter chart types. Excel creates the scatter chart and displays it on the spreadsheet.

How to Close MS Excel

1. Navigate to the 'File' tab in the top left-hand corner of the Excel program. Click on 'File' and click on the 'Save As' tab underneath 'File.' In the pop-up window, type in the name of the workbook and navigate to the location where you want it saved. Click 'Save.' This will ensure that none of your information is lost.
2. Point your mouse cursor to the upper right-hand corner of the Excel program and hit the 'X' symbol to close out your workbooks. Do this with each open workbook.
3. Navigate your cursor to the very upper-left hand corner of the Excel program and click on the Excel symbol. Scroll down on the menu and click on 'Close' or 'Exit Excel.'

How to Change a Cell to 26Pt in Microsoft Excel

1. Open Excel 2007. Click the 'Office' button and select 'Open.' Browse your computer for the spreadsheet you wish to revise; after you locate it, click on it. Click the 'Open' button and the document will open.
2. Click a cell, then click the 'Home' tab. Select the 'Font' group and click the 'Font Size' drop-down box. Change the 'Font Size' to 26; the type in the cell is adjusted to 26 points.
3. Save your changes by clicking the 'Save' icon in the Quick Access Toolbar.

How Do I Set a Dropdown List Box in Excel to Allow Multiple Selects?

1. Open the spreadsheet you want to create the drop-down list for in Excel.
2. Locate an area on the spreadsheet to house the list of data items that will appear in the drop-down list box. This can be in any location, or on a separate worksheet tab.
3. Type the list in several cells of a single column. Place each list item in the cell below the prior item.
4. Select the cells in your spreadsheet that will use the drop-down list box. Use the mouse and drag over the range of cells that are currently empty.
5. Click the 'Data' menu and select the 'Validation' command. A pop-up box will appear.
6. Choose the 'List' option from the 'Allow' menu on the 'Settings' tab.
7. Click in the 'Source' field. Select the cells you typed earlier that contain all the list items for the drop-down list box. Drag your mouse over these cells, and the cell address will be automatically entered into the 'Source' box. Click 'OK' when finished.
8. Click in any cell you chose for the list. An arrow appears on the right edge of the cell. Choose any of the multiple items in this drop-down list box for the value of this cell.

How to Remove Blank Non

1. Open the Microsoft Excel spreadsheet that you want to filter.
2. Highlight the range of cells that you want to include in the filter. You can highlight multiple cells by clicking while holding down the 'Shift' key.
3. Open the 'Data' menu at the top of the window, expand the 'Filter' submenu and select 'AutoFilter.' This will add an arrow button to the top of each highlighted column.
4. Click on the arrow button on one of the columns and select '(Blanks)' from the drop-down menu to show all blank cells and remove all non-blank cells.
5. Click on the arrow button on one of the columns and select '(NonBlanks)' from the drop-down menu to reverse the action. Now all non-blank cells will be shown and all blank cells will be removed.
6. Go back to the 'Data' menu and uncheck the 'AutoFilter' option to remove the filters and see all of the cells again.

How to Unshare a Workbook in Excel 2007

1. Open the workbook you would like to unshare in Microsoft Excel 2007.
2. Go to the 'Review' tab and go to the 'Changes' section.
3. Click on 'Share Workbook' and go to the 'Editing' tab.
4. Uncheck the 'Allow changes by more than one user at the same time. This also allows workbook merging' box. Click OK.

How to Lock an Excel 2003 Spreadsheet

1. Launch Excel and open the spreadsheet which you want to protect.
2. Click the 'Tools' menu, expand the 'Protection' submenu and select 'Unprotect sheet.' This step will allow Excel to reinitialize the protection of the spreadsheet. If the spreadsheet is password protected, you may have to type in the current password.
3. Click the 'Tools' menu again, expand the 'Protection' submenu and select 'Protect sheet.' The 'Protection' dialog box will open.
4. Click the actions which you wish to allow users to perform on the sheet in the 'Allow all users of this worksheet to' list. For example, you can allow users to create a new column but prevent them from deleting any existing columns.
5. Type the password you wish to use to protect the spreadsheet in the 'Password to unprotect sheet' field. Confirm your changes by clicking 'OK.'

Saturday, May 26, 2012

How to Remove Locked Cells in Excel 2003

1. Open Excel 2003 and click the 'File' drop down menu. Select 'Open.' Browse the files and locate the workbook. Click the workbook and the 'Open' button. The workbook opens.
2. Click the worksheet that contains the locked cells. Click the 'Tools' drop down menu and select 'Protection.'
3. Select 'Unprotect Sheet.' If a password was applied to the protection, you will be prompted to enter the password. Click 'Ok.'
4. Click the 'Format' drop down list and select 'Cells.' Click the 'Protection' tab and de-select the 'Locked' option. Click 'Ok.' The lock is removed from the cells.

How to Use Excel to Calculate Total Time

1. Enter the starting date and time in cell A1 using the format 'month/day/year hour:minutes AM/PM.' For example, if your starting time is 7:15 a.m. on July 15, 2010, you would enter '7/15/2010 7:15 AM' in cell A1.
2. Enter the ending date and time in cell A2 using the format 'month/day/year hour:minutes AM/PM.' For example, if your ending time is 7:15 a.m. on July 17, 2010, you would enter '7/17/2010 7:15 AM' in cell A2.
3. Enter '=A2-A1' in cell A3 to find the number of days between the two times. Excel will automatically display the total time. In this example, Excel would automatically display '2.' If you want to convert to hours elapsed, multiply by 24. If you want to convert to minutes, multiply by 1,440.

How to Make All of the Columns the Same Size in Microsoft Excel

Using a Mouse
1. Open the Excel worksheet with data in columns.
2. Hover your cursor over the letter of the first column you wish to select. When the black down arrow appears, click and drag across to the last column you want to select.
3. Hover on the line between any two selected column letters--it doesn't matter which two--until you see a black symbol with two arrows pointing in opposite directions. Click and drag left to make the columns narrower or drag right to make them wider. All the selected columns should now be of equal width.
Using the Menu
4. Open the Excel worksheet with data in columns.
5. Hover your cursor over the letter of the first column you wish to select. When the black down arrow appears, click and drag across to the last column you want to select.
6. Click the 'Home' tab, then click 'Format' in the Cells section.
7. Select 'Column Width' from the menu.
8. Enter a number in the box provided and click 'OK.' You can specify any number between 0 and 255; this represents the number of characters the column will display.

Friday, May 25, 2012

How to Use the Date Function in Excel to Show Next Month

1. Open Excel 2007 and type '2010' in cell A1. Enter '10' in cell A2 and '11' in cell A3. This information will be used as reference data in the Date function. Enter the Date function '=Date(A1,A2,A3)' in cell B1. This function will display a numerical representation of the date 10/11/2010.
2. Format how the date displays by right-clicking cell B1 and selecting 'Format Cells.' Click the Number tab and pick the 'Date' category. Select a date format. Click 'OK.'
3. Change the formula in cell B1 to add 30 days to the current day to display the next month. Enter the following formula: '=DATE(A1,A2,A3 30).' The cell will reflect the date of 11/10/2010.

How to Create a Linear Chart Using Excel 2007

1. Open Microsoft Excel from the 'Start' menu and wait for the application to load.
2. Enter the data you want to convert into a chart into cells on a spreadsheet. Include labels for each column in row 1. Ensure that the X and Y values are in their own columns and each row contains the X and Y value that should be graphed together on the chart.
3. Click and drag from the top-left cell to the bottom-right cell to highlight all the data you want to include in the chart.
4. Click the 'Insert' tab on the top of the screen.
5. Click 'Scatter,' and then choose the chart type you wish to use. As soon as you click a chart type, it will automatically be inserted into the spreadsheet.
6. Select the 'Layout' tab under Chart Tools. Click 'Trendline,' and then choose linear trendline from the drop-down menu.

How to Change the Case in Excel 2007

1. Open Excel 2007 and select a workbook that contains data that needs the case changed. Click the 'Office' button and select 'Open.' Browse your computer and locate the workbook. Click the workbook and select 'Open.'
2. Insert a column near the data. Click on the first cell in the column. Click on the 'Formulas' tab in the ribbon. Select the 'Text' option. Click 'Proper.' The Function Arguments window opens. Click on the red box in this window. Highlight the first cell that needs the case changed. The cell reference appears in the Function Arguments window. Click 'OK.'
3. Review the results of the Proper function. The case of the data has been changed to proper case. The first letter of each word is now capitalized.

Thursday, May 24, 2012

How to Copy a Pivot Table to Another Worksheet

Copy the Worksheet
1. Click on the worksheet that contains the Pivot Table. You can do this easily by clicking the appropriate worksheet tab that appears at the bottom of the Excel window.
2. Right-click the worksheet tab again. A pop-up menu will appear. Choose the 'Move or Copy' option. Another pop-up menu appears.
3. Click the 'Create a copy' check box. Then click once on the existing worksheet in the menu list before which you wish to place the copy of the Pivot Table.
4. Press the 'OK' button. Excel copies the Pivot Table to another worksheet and places the copy in the specified worksheet location.
Copy the Pivot Table
5. Click on the worksheet that contains the Pivot Table you wish to copy.
6. Click once on an empty cell in the upper left of the Pivot Table, just above the Pivot Table data.
7. Drag the mouse down and to the right until you reach an empty cell on the lower right of the Pivot Table.
8. Right-click on the selected Pivot Table cells and choose the 'Copy' option. Alternately, press the 'Ctrl' and 'C' keys on your keyboard to copy the information.
9. Click in the worksheet where you wish to place the copied Pivot Table. Press the 'Ctrl' and 'V' keys, or right-click and choose the 'Paste' command.

How to Print Selected Areas of an Excel Worksheet

1. Start Microsoft Excel and open the file you want to change.
2. Drag to highlight the cells you want to print.
3. Open the File menu and select Print.
4. In the 'Print what' box, click Selection.
5. Select OK to print your selection.

Wednesday, May 23, 2012

Excel Tutorial on Statistics

1. Decide what statistical function or test you need to perform. For example, you might be trying to find an average, or mean, of several numbers, or you may want to find the slope for a graph that you've already plotted in Excel.
2. Click on the cell where you want to insert the statistical function.
3. Find the function name in Excel. Click on the 'Formulas' tab in Excel 2007 or Excel 2010, then click on 'More Functions.' Mouse over 'Statistical,' then click on the function you need. Most functions are named so that you can easily find what you are looking for. For example, AVERAGE finds the average of a range of numbers and MAX finds the maximum value in a range of numbers. If you are unsure about a particular function, mouse over the function name to find out more about the function or press the 'F1' key to bring up the help window for formulas and functions.
4. Fill out the information requested in the pop up window. The information will depend on which of the dozens of functions you have chosen. In general, you can expect to at least have to choose a range of cells to perform the function on. For example, if you want to find the average of a set of numbers in cells A1 to A10, then you'll need to enter 'A1:A10' as the range in the pop up window.
5. Press the 'OK' button. Excel will perform the requested function and return the result in the requested cell.

How to Create an Excel XLA Add

1. Launch the Microsoft Excel software. Click 'File' in the top navigational bar and select 'New' from the drop-down menu. A Workbook Options window will be displayed. Press 'Blank Workbook.' Excel will create a new workbook.
2. Select 'File' in the top menu again. Select 'Properties' from the drop-down menu. Look in the Document/Name Properties box and press the tab labeled 'Summary.' Name the new workbook. This name will be what is shown in the add-ins window after you are finished.
3. Click 'Tools' in the top menu bar and select 'Macro'; then click 'Visual Basic Editor.' The editor will appear on the screen.
4. Click 'Insert' in the top menu bar and click 'Module' from the drop-down menu. A code module box will be displayed. Enter in the function code with which you want the Excel add-in to work. For example, you may type 'Function' followed by a space, then 'Age(DoB As Date).' This add-in, if activated in an Excel worksheet, will convert any references to DoB to the numbers listed in the Date column. If you are unsure of how to structure a function code, browse the Microsoft Excel help site (see Resources).
5. Save the file. You may now add it to a current worksheet by selecting 'Tools' in the worksheet and choosing 'Add-Ins.' Navigate to the hard drive folder in which you saved the XLA file and click it; then press 'Open.'

How to Use an Access Pivot Table

1. Click 'Start,' 'All Programs,' 'Microsoft Office' and then select 'Microsoft Access 2010.'
2. Click 'File' and then 'Open' and browse to the Microsoft Access database you want to analyze.
3. Click 'View' and then select 'PivotTable View' and this displays the 'PivotTable Field List.'
4. Select a numerical field from the 'PivotTable Field List,' then drag and drop this field in the 'Drop Totals or Detail Fields Here' section of the pivot table view. For example, if you want to know how many orders you have in a store database, the order quantity field is the numerical field you select, since it represents the number of orders. A simple pivot table view is displayed showing the summarized list of the order quantities.
5. Select the field representing the store's product identifier from the 'PivotTable Field List,' then drag and drop it in the pivot table view section labeled 'Drop Row Fields Here.' Now the pivot table view is displayed listing the order quantities for each store product. The grouping of information by store product identifier makes the pivot table summary more meaningful for analysis.
6. Right-click the numerical field column name in the pivot table view and select 'AutoCalc' and then select 'Sum' and now you have a meaningful pivot table that summarizes the order quantity for every store product in your database.

How to Open QBB Files Without QuickBooks

1. Open either Quicken or Excel. Quicken is another financial program similar to QuickBooks while Excel is a spreadsheet program packaged in the Microsoft Office platform.
2. Select 'File,' 'Import,' then choose the QBB file you want to view.
3. Click 'OK' and wait for the software to convert the QuickBook document into its own format standard. Once complete, the financial information appears on your computer screen.

How to Learn Excel Spreadsheet

Free Excel Training from Microsoft
1. Download and install a free trial version of the Microsoft Office suite that includes Excel and several other useful programs in a limited time, fully functional trial version.
2. Click 'Start' at the Windows orb, 'All Programs' and 'Microsoft Office' to open the start menu group. Click 'Microsoft Excel 2010' to launch the software.
3. Click 'File' and 'Help' to launch the interactive training series to learn basic and intermediate Excel functions and to find step-by-step guides to performing the most popular functions.
Basic Excel Functions
4. Click 'Start' at the Windows orb. Click 'All Programs' and 'Microsoft Office' to open the start menu group. Click 'Microsoft Excel 2010' to launch the software.
5. Click once in the top left cell -- A1 -- and type in any number. Press 'Enter' to save the cell and move to the next cell down or press 'Tab' to save the cell and move to the next cell to the right. Alternately, type in any number and click another cell or use the arrow keys on the keyboard to save the cell contents and move to another cell.
6. Enter another number in a second cell. Press 'Enter' or otherwise save the cell contents by moving to another empty cell.
7. Press the '=' sign on the keyboard. Click the first cell with a number in it once with the mouse. Press the ' ' sign on the keyboard. Hold 'Shift' and press ' ' or press the ' ' key on the numeric keypad, if equipped, and then click once on the second cell with a number in it. Press 'Enter.' This creates a formula to add two cells together and display the results in a third cell.
8. Click in the first cell with a number and change the number. Press 'Enter' or otherwise save the cell contents by moving to another empty cell. Notice that the cell that displays the total updates automatically to reflect the new total. Formula cells update as new information is entered.

Tuesday, May 22, 2012

How to Create Invoices in Microsoft Word From Excel 2007

1. From a new blank workbook in Excel, click the 'Office' button and then the 'New' icon to display the 'New Workbook' dialog box. Under the 'Microsoft Office Online' section, choose 'Invoices' to view the invoice templates that are available for free download. Filter out customer-submitted templates, if desired, by clicking on the 'Hide customer submitted templates' icon at the top of the screen.
2. Preview the available templates by single-clicking on each one to view the template in the preview pane to the right. Click on the 'Download' button once you've selected the desired template. You will briefly see a status window until the file download is complete (downloading times are usually minimal, but will vary depending on the speed of your Internet connection). A new Excel window showing your template will pop up after the download has completed.
3. Select the cells containing data in this new worksheet, then right-click the selected data and choose 'Copy.'
4. With the Excel window still open, launch Microsoft Word. In the 'New Blank Document' screen, click the 'Paste' menu button in the 'Clipboard' group on the 'Home' tab. Choose 'Paste Special' to launch the 'Paste Special' dialog box.
5. Under the 'Paste As' section, choose the 'Microsoft Excel Binary Worksheet Object' option and then click 'OK.' Your invoice template will be pasted into Word as an Excel object. In order to enter your data into the template and temporarily alter the ribbon to show Excel commands, double-click the Excel object.

How to Sort a Row or Column in Excel

1. Start Microsoft Excel and open the file you want to change.
2. Drag over the two or more columns or rows that you want to sort.
3. Open the Data menu and select Sort. The Sort dialog box appears.
4. Select the Continue with the Current Selection option to sort a single column or row.
5. Select the Expand the Selection option to sort a series of columns or rows.
6. Choose Ascending or Descending to determine the sort order.
7. Click the Options button in the Sort dialog box.
8. Select 'Sort top to bottom' in the Sort Options dialog box to sort a column.
9. Select 'Sort left to right' in the Sort Options dialog box to sort a row.
10. Click OK in the Sort Options dialog box.
11. Click OK in the Sort dialog box.

How to Change the Pivot Table Default Settings in Excel 2007

1. Open your Microsoft Excel 2007 application on your computer. Click on any cell in the range you want to use for your pivot table.
2. Click on the 'Insert' tab and then click on the 'PivotTable' button from the Table group. Click on the 'Create PivotTable' button. The 'Create PivotTable' dialog box will then appear.
3. Change any defaults in the 'Create PivotTable' dialog box. For instance, you can click on the 'Use an external data source' option. Click on 'OK' once you've finished changing any of the default selections.
4. Continue to create your pivot table by clicking on any of the options in the 'PivotTable Field List' box. Click on the 'Update' button when you finish.
5. Change the display defaults by clicking on the 'Options' tab and then selecting the 'Options' button in the 'PivotTable' group. Click on the 'Layout and Forms' tab and then click on the 'For error values show' option.
6. Click on the box next to the 'For empty cells show' field. Click on the 'OK' button and the default display options will be changed.

Monday, May 21, 2012

How to Add a Spell

1. Click the 'View' drop-down menu and then the 'Toolbars' submenu. Make sure that the toolbar that will hold the spell-check button is visible by verifying that there is a check mark next to it or selecting it, if there is no check mark, to make it visible.
2. Click the 'Tools' drop-down menu and select 'Customize.'
3. Select the 'Commands' tab in the 'Customize' dialog box.
4. Select the 'Tools' category and scroll down until the 'Spelling' button is visible. The button will have the letters 'ABC' and a check mark on it.
5. Click and drag the 'Spelling' button from the Customize dialog box to the place on the toolbar where it will reside, and then release the mouse button. While the Customize dialog box is active, you can drag the button to a new location on the toolbar.
6. Click 'Close' to close the Customize dialog box.

How to Insert a Date in Microsoft Excel

Excel 2007
1. Right click on the cell and choose 'Format Cells.'
2. Click on the 'Number' tab.
3. Click on 'Date' in the 'Category' list box.
4. Choose the date format by clicking on one of the formats in the 'Type' box. For example, click on '3/14.'
5. Press 'OK.'
Excel 2003
6. Right click on the cell, the click on 'Format Cells.'
7. Left-click on the 'Number' tab.
8. Scroll to the 'Date' field and highlight it.
9. Scroll to the date format you'd like to use in the 'Type' box.
10. Click on 'OK.'

How to change the default font setting in Word 2003

1. Open Microsoft Word.
2. Right-click your mouse in the blank document. The Font dialog box will display.
3. Make any changes you want in this box. You can change the font, its style and size, as well as color and effects.
4. Click the 'Default' button in the lower left corner of the Font dialog box when you are done.
5. Click 'Yes' on the pop-up that appears. This will make your newly chosen settings the default for each future blank document.

Sunday, May 20, 2012

How to Graph Two Lines in Excel 2007

Line Graph
1. Open the Excel file that contains the data you need to graph. To make two lines using a line graph, you will need two columns of numerical data. If you want the data to have labels, you should include a header in the cell at the top of each column.
2. Select the data that you want to include in the Line Graph. If your desired information is the only data in the two columns, you can simply click and hold the button on the letter above the first column, then move the mouse to the second column and release the button. If your data is not the only information in the column, click and hold on the top-left cell, then drag the mouse pointer to the bottom-right cell, and release it.
3. Select the 'Insert' tab at the top of the Excel 2007 window. Click the 'Line' button in the 'Charts' area and choose either the 'Line' or 'Line with Markers' option. Your two line graph will appear on the screen.
4. Right-click in the open space of the chart if you need to add custom labels to the x-axis. Choose 'Select Data' from the list of options. Press the 'Edit' button in the 'Horizontal Axis Labels' area. Select the labels from your spreadsheet, which will normally be located on a column just to the left of the data columns, and click 'OK'. Click 'OK' again and the labels will update.
Scatter Graph
5. Open the Excel file that hold your information. When using a scatter graph to make two lines, you will need to have data arranged in two sets of two columns. Select the first two columns that you want to use.
6. Select the 'Insert' button at the top of the window. Click the 'Scatter' button under the 'Charts' area. You will see five options. Choose any option other than 'Scatter with Only Markers' to create a graph that will include lines. You will see a graph appear on the spreadsheet that has one line.
7. Right-click on the graph and choose 'Select Data'. Click the 'Add' button under 'Legend Entries'. A small 'Edit Series' window will appear.
8. Type in a name for the series under 'Series Name'. This can be whatever you want. Click the box next to 'Series X Values' and choose the data from the first column of the second set. Once you have selected the data, click the box again to return to the 'Edit Series' box. Click the box next to the 'Series Y Values' and select the data from the final column. Click the box again, then click 'OK'. The graph will now show two lines on it.

How to Lock Unlock an Excel Spreadsheet

Locking
1. Open the Excel spreadsheet you want to lock.
2. Click the green 'File' tab on the ribbon.
3. Click the large, square 'Protect Workbook' button.
4. Select a permissions level. Choose 'Mark as Final' to make the spreadsheet read-only. This option does not require a password and only discourages, but does not prevent, editing.
5. Choose 'Encrypt with Password' if you want to prevent anyone from opening the spreadsheet without a password. Type the password in the box and click 'OK.' Excel will prompt you to re-enter the password. Re-enter the password and click 'OK' again.
6. Choose 'Protect Current Sheet' if you want to set a password for opening the spreadsheet and want to restrict editing of specific cells or areas. Enter the password in the box and click on the various items you want others to be able to do. Whatever you do not check off will be disallowed. Click 'OK' when you are satisfied. Excel will prompt you to re-enter the password. Re-enter the password and click 'OK' again.
7. Choose 'Protect Workbook Structure' if you want the structure to remain intact while allowing others to make most changes. Users will not be able to delete or move sheets but otherwise, they will be able to add rows and make calculation and style changes. Enter the password and click 'OK.' Excel will prompt you to re-enter the password. Re-enter it and click 'OK' again.
Unlocking
8. Open the Excel file you want to unlock. If it is fully encrypted such that no one can open it without a password, Excel will prompt you for a password at this point. Enter the password and click 'OK.'
9. Click the 'File' tab. You should see the word 'Permissions' written in orange; this lets you know that the worksheet is protected in some way.
10. Click 'Protect Workbook' and select the protection option that is highlighted in orange. This indicates the level of protection you used to lock the spreadsheet in the first place.
11. Enter the password to unprotect the spreadsheet. Click 'OK.' You will be able to make any edits to the spreadsheet.
12. Save the document. The next time you open it, you will not need a password.

How to Convert Quattro Pro for Windows to Excel

1. Go to 'My Computer' and double-click on 'Control Panel.'
2. Double-click on 'Add/Remove Programs' to open the list of installed programs on your PC. Wait for the list to be propagated before doing anything.
3. Search for Microsoft Excel. If you installed Excel as part of an MS Office suite, look for Microsoft Office instead. Click on the program to bring up a list of options on the right side of the window.
4. Click the 'Add/remove' button among the list of options. This opens up an installation wizard.
5. Click on the 'Add or Remove Features' option in the window that appears to bring up a list of available addons to install. Click on the 'Quattro Pro 5.0 Converter under Microsoft Excel for Windows' program, then click on 'Run From My Computer' to install the converter to your PC.
6. Click 'Update Now' to finish the installation.
7. Open Microsoft Excel, then go to 'File' then 'Open' to open a window listing files on your computer.
8. Search for the Quattro file you want to convert, then double-click on the file to open it.
9. Click on 'File' then 'Save As' to open up the save dialog.
10. Select 'Microsoft Excel' or any Excel file type in the file selection at the bottom of the dialog. Name your file in the line above the file selector and click 'Save.'

Saturday, May 19, 2012

How to Use the Inverse Log Function in Excel

1. Start Excel.
2. Generate the inverse of a common -- base 10 -- logarithm in cell 'A1' by typing '=POWER(10,B1)' and entering the logarithm desired in cell 'B1.' You can replace the first 10 inside the parentheses with any base you choose.
3. Generate the inverse of a natural logarithm -- base 2.8172 -- in cell 'A2' by typing '=EXP(B2)' and entering the logarithm desired in cell 'B2.'

How to Make a Cumulative Frequency Table

1. Construct your spreadsheet to have three columns. One of these columns will be your observation interval, the second will be the number of observations you make per interval, and the third will be the cumulative frequency column.
2. Construct your table to contain as many rows as you have observation intervals (e.g., if you are going to make an observation every hour for one day, you will have 24 rows).
3. Write your observation intervals down in the first column. As in the above example, if you are making an observation each hour for one day, your 24 rows in this column will be numbered individually, 1 through 24.
4. Note the number of observations you make during each time interval in the second column. For example, if you observe 3 birds at your feeder at the 1-hour time mark, write the number '3' in that column. The next observation period, you may see only 2 birds.
5. Keep a running total of how many observations you have made during each time period in the third column. For example, if during time period one you observe 3 birds, after writing '3' down in your second column, also write '3' in the third column. At the second observation period you observe 2 birds, you will record '2' in the second column, but add this number to the number in the cumulative frequency column to give you the entire number of birds observed since your observations began (in this case, 5).
6. Continue writing observations at each observation interval, adding the total to the cumulative frequency and recording it in the third column.

How to VLookup Two Values in Excel 2007

1. Open the Excel 2007 file that contains the table you want to work with.
2. Right-click on the column letter above the leftmost column on your table. Choose 'Insert' to create a new column.
3. Select the top cell in your new column. Type '=XXYY' into the cell, where 'XX' equals the cell reference to the first cell in the first column you want to search through with VLookup, and 'YY' is the second column. Press 'Enter' and you will see the entries in those columns compressed together into the new column. For example, if 'here' was in the first column, and 'there' was in the second, your new column would read 'herethere.'
4. Move your mouse over the bottom-right corner of the cell with your formula. Click the mouse and hold down the button,and then drag the mouse down until your reach the last row in your table. Release the mouse button and Excel will copy your formula down the entire length of the table.
5. Click on the top cell in your new column. Press 'Ctrl' and 'A' to select the entire table. Click the 'Home' tab at the top of the screen. Click the 'Sort and Filter' button, which you can find in the 'Editing' area of the ribbon. Choose 'Sort A to Z.' VLookup will not return good values if the first column is not ordered from lowest to highest.
6. Select any cell outside of the data table. Enter the following formula into the cell: =VLOOKUP('XX',YY:ZZ,2) where 'XX' is equal to the two values that you want to search for, 'YY' is the top-left cell in the data table, 'ZZ' is the bottom-right cell in the data table and '2' is the column number where you want to search for your result. When entering your search values, remember to place them together without spaces, so if you were searching for the number '1' in one column and the word 'trucks' in a second column, you would enter '1trucks.' Press 'Enter' to complete the formula and get your result.

How to Remove the Blanks from a Pivot Table Add

Remove Blank Cells
1. Open Excel and open 'Pivot Table Options' in the Excel ribbon by clicking anywhere on the Pivot Table.
2. Click 'Pivot Table Options' to open the options box so you can make changes to the Pivot Table settings.
3. Click the 'Layout Format' tab and look to the 'Format' section.
4. Uncheck the box next to 'For empty cells show:' and then click 'OK.' Blank cells will no longer show up in the Pivot Table add-in.
Remove Blanks from Rows
5. Click on the Pivot Table report to open 'Pivot Table Options' in the Excel ribbon.
6. Look to the 'Active Field' section of the ribbon and click 'Field Settings.'
7. Click the 'Layout Print' tab when the 'Field Settings' box opens.
8. Uncheck the box next to 'Insert blank line after each item label' and click 'OK.' Blank lines will no longer appear after rows.
Remove Blanks from Item Labels
9. Select the item that has a blank from the Pivot Table report.
10. Click the 'Design' tab located in the Excel ribbon and then click 'Blank Rows.'
11. Select 'Remove blank line after each item label' and blank lines will no longer appear after item labels in the Pivot Table.

How to Use the Same Color for Multiple Line Charts

1. Turn on your computer and open Excel. Microsoft Excel is a spreadsheet program that helps you maintain data and compile charts, tables and graphs in a few easy steps. This program is available for PC and Mac computers and we will look at creating a graph in Excel.
2. Enter the data you want to include in your chart in the Excel spreadsheet. This is easy to do. You will place your 'Y-axis' data on the first vertical column and the 'X-axis' data across the first horizontal row. Start The vertical column on the second row so that you can place the information in each data box. For example, if you want to review 'Department Revenues' by each 'Quarter' you would place the 'Department Name' going down the first column with each 'Quarter' labeled across the top row: Q1, Q2, Q3, Q4.
3. Go to the menu to 'Insert > Chart.' Click this and follow the onscreen menus to select 'Line Chart.' Highlight the areas in the data sheet you want to use in the chart. You will be given the option to either include the chart on your existing data page or open a new page with just the chart on it.
4. Look at the chart that pops up and review it to make sure it is correct. This will be a color chart with the data represented by different colored lines. Now you need to change the colors into patterns.
5. Right-click on the first data line, which may be red, green, yellow or blue. Select 'Format Data Series.' A box will appear with several tabs over your chart. Make sure you have selected the 'Colors and Lines' tab. Confirm that you are on the correct line by the color that is in the color box. Choose the pattern under the 'Dashed' tab. Click 'OK' when you are done customizing the line. You can maintain the color of the line or change it to black.
6. Repeat Step 5 for all data lines, making sure to choose a different pattern for each information line.
7. Save the file and print.

Friday, May 18, 2012

How to Create the Input Form on Excel 2007

1. Plan your worksheet. Think about the type of information you will be keeping in the spreadsheet and set up your columns.The column headers will be used as the form labels next to the input boxes.
2. Set up your spreadsheet. Type in the column headers. Set the column lengths. Do not merge any cells in the column headers. Excel will not be able to generate an input form from merged cells. You can always make the columns bigger by changing the column width property.
3. Add the Form button to the Quick Access toolbar. Click the drop-down arrow on the Quick Access toolbar and then click 'More Commands.' A dialog box appears. Click the drop-down under 'Choose commands from:' and choose 'All Commands.' Stroll through the list and find 'Form.' Highlight 'Form' and click 'Add,' then click 'OK.' The Form button is now in your Quick Access toolbar.
4. Highlight the column headers. Click on the 'Form' button in the toolbar. Excel will generate an input form that will allow you to enter in new information into the columns. Notice your column headers on the left-hand side of the input form next to the text boxes.

Thursday, May 17, 2012

How to Find Blank Cells in a Column in MS Excel

Sort Method
1. Open the Excel file that you want to work with. Select the correct spreadsheet from the list of sheets at the bottom of the window. Click on any cell in the column where you are looking for blank cells.
2. Click the 'Data' tab at the top of the window. Locate the 'Sort Filter' area of the ribbon.
3. Select either the 'ZA' button, to sort the information from largest to smallest, or the 'AZ' button, which will do the opposite.
4. Click on any cell in the column you just sorted. Move your mouse to the very bottom of the selected cell, so that the pointer changes into a set of four arrows. Double-click the mouse button to immediately go to the last entry in that column. The blank cells are all located below this entry.
Filter Method
5. Open the Excel spreadsheet that you want to work with and choose the correct sheet from the bottom of the window. Click on any cell in the column where you are looking for blank cells.
6. Click the 'Data' tab and find the 'Sort Filter' area. Click the 'Filter' button. You will see drop-down arrows appear at the top of each column.
7. Select the drop-down arrow for your desired column to open up a small window. Click in the box next to 'Select All' to remove all of the check marks. Scroll to the bottom of the list and place a check mark next to 'Blanks.'
8. Click 'OK' and you will see the report get filtered to only show rows where your selected column has a blank cell.

How to Unlock a File in Excel 2007

1. Open the Excel file.
2. Click the 'Review' tab on the command ribbon.
3. Click the 'Unprotect Sheet,' 'Unprotect Workbook' or 'Unprotect Shared Workbook' button in the 'Changes' group.
4. Type the password if a dialog box opens. Click 'OK.'
5. Edit the unprotected file.
6. Press 'Ctrl S' to save this file.

How to Convert Military Time on an Excel Spreadsheet

1. Select the cell with military time. Place your mouse over the cell. Click your left mouse button once to select the cell. The cell is active when outlined with a black border. In addition, the status bar will indicate 'Ready' in the lower-left corner of the program window.
2. Right-click the cell. Open the short-cut menu by right-clicking the cell. Click 'Format cells...' from the short-cut menu. The 'Format Cells' dialogue box will open.
3. Choose a format. Make sure the current tab is 'Number.' Select 'Time' from the 'Category' section of the 'Number' tab. In the 'Type' section, select the time format you would like to use. Time formats marked with an asterisk adjust based on the regional settings of your operating system.
4. Accept the changes. Click 'OK' to change the military time to your selected format.

How to Make a Button for a Macro in Excel

1. Open the Excel 2010 spreadsheet where you want to add a macro button. Click the 'File' tab at the top of the screen and choose 'Options' from the list items on the far-left side of the screen.
2. Select 'Customize Ribbon' on the 'Options' window. Locate the 'Developer' entry in the rightmost column under the 'Customize the Ribbon' heading. Click the empty box next to 'Developer' to add that tab to the Excel ribbon. Click 'OK' to go back to the spreadsheet.
3. Click the 'Developer' tab at the top of the Excel spreadsheet. Locate the 'Controls' area of the ribbon and click the 'Insert' button. Under the 'Form Controls' heading, select 'Button.' The cursor will change to a ' ' sign.
4. Click and hold the mouse button anywhere on the spreadsheet. Drag the mouse to the left and you will see a box appear under the cursor. When the box is the size that you want the button to be, release the mouse button.
5. Select the macro you want the button to run when the user clicks it and click 'OK.' If you haven't created the code for the macro yet, you can type in a name for the macro and click 'New.' The VBA console will open up and you can enter in the code for the macro between the 'Sub' and 'End Sub' lines in the console.
6. Click the 'Design Mode' button on the ribbon and right-click the button. The mouse pointer will change to a ' ' sign and you can click and drag the button to another location. Click the 'Design Mode' button again to lock the button in place.
7. Right-click the button and choose 'Edit text' to change the text that appears on the button. Enter in whatever text you want and press 'Enter.'

How to Use the Multiple Rows Function in Excel

1.
In Excel, place your cursor in an empty cell located below the group of numbers that you would like to reference.
2.
Click on the 'Function' key located to the left of the Formula task bar.
3. Select 'Rows' from the Function list.
4. Click 'OK'.
5.
Highlight the section of rows containing the set of numbers you would like to add or enter the row and column reference points in the Formula bar.
6.
Click 'OK' to exit.
7.
Test the formula's accuracy by adding a couple of new rows. Highlight two rows within the middle of the range. To do this, left-click your mouse and hold down the 'Down-Arrow Key.' Release. The rows should still be highlighted.
8.
Right-click your mouse, then select 'Insert.'
9.
Verify that the cell containing the formula references all of the rows including the new row additions you just made.

Tuesday, May 15, 2012

How to Duplicate Words in Excel

1. Double-click the icon on the desktop or click 'Start,' 'All Programs,' 'Microsoft Office' and Microsoft Excel 2010' to launch Microsoft Excel.
2. Type a word into the first cell and press 'Enter' to accept the entry and move to the next cell down.
3. Type a different word that starts with a different letter into the second cell down the first column and press enter.
4. Type the first letter of either word previously entered into the column and Excel will offer to automatically complete the entry to match the previously entered cell. Press 'Enter' to accept the entry or keep typing to make a new entry.
5. Press the 'Delete' key and then press 'Enter' if typing a shorter version of the previously entered word to accept the information as typed instead of the automatic completion suggestion.
6. Click 'File,' 'Options' and click on the 'Advanced' menu choice on the left to review the settings for automatic completion. Turn automatic completion on by checking the 'Enable AutoComplete for Cell Values' box or turn it off by clearing the check box. Click 'OK' to save the setting.

How to Merge Cells in Excel

1. Start Microsoft Excel, and open the spreadsheet that contains the multiple cells you would like to merge into one cell. If you like, you can just start a new spreadsheet to perform the task of merging cells.
2. Select the cells that you would like to merge by clicking on the first cell with your mouse, holding down your left mouse button and dragging until all of the cells you want to merge are highlighted.
3. Right-click to bring up the Excel menu and click on 'Format Cells...' to open the 'Format Cells' dialog box. In the 'Format Cells' dialog box you can change all of the formatting options related to the cells and text you have within the cells, including the option of merging cells together.
4. Click the 'Alignment' tab at the top of the 'Format Cells' dialog box to show the alignment options for the currently selected cells.
5. Use your mouse to click the check box before the option 'Merge Cells' under the 'Text control' section of the 'Format Cells' dialog box.
6. Press the 'OK' button with your mouse to close the 'Format Cells' dialog box and merge the selected cells together in your Excel spreadsheet.

How to Write a Protected Excel Spreadsheet

1. Launch Excel and create or modify the data in an existing worksheet to suit your particular needs. Click on 'File,' and 'Save' to save your work.
2. Click on the 'Tools' menu. Select 'Protection' from the drop-down menu that appears, and then select 'Protect Sheet.'
3. Type in a password of your choosing when prompted, and click 'OK.' Remember this password as it will be required when making changes to the worksheet.
4. Protect the entire workbook of worksheets you have created by clicking on the 'File' menu, and then selecting 'Save As.' The Save As dialog box opens.
5. Click on the button labeled 'Tools.' Select 'General Options,' and the Save Options dialog box opens.
6. Type in the password of your choosing in the text box, and click 'OK.' This password will now be required to open the workbook file.

How to: PMT Function in Excel 2003

1. Click on an empty cell in the Excel 2003 worksheet and then type PMT( interest_rate, number_payments, PV).
2. Replace 'interest_rate' with the actual interest rate. For monthly payments, divide the interest rate by 12 and for weekly payments, divide by 52. For example if you have a 6 percent interest rate and will be making monthly payments, type '6%/12.'
3. Replace 'number_payments' with the number of payments to be made. For example, if you have four years of monthly loan payments, then type '4*12.'
4. Replace 'PV' with the loan's principal value. For example, if you have a $6,500 loan then type '$6500.' In this example, the final function should read 'PMT(6%/12, 4*52, 6500).'
5. Press the 'Enter' key to get the monthly payment.

How to Change MS Excel From English to German

1. Purchase the German language pack and download it if you haven't already done so. Install the language pack by double clicking its icon and following the on-screen prompts.
2. Open Microsoft Excel and click the 'File' tab at the top of the page.
3. Click the 'Options' link in the left pane and select 'Language.'
4. Click the drop-down menu under Editing Languages and select the German version you want to use for spell checking, according to your country preference. Click the 'Add' button next to the selection. Select the entry in the list and click the 'Set as Default' button to the right of the list.
5. Move down the window to the Choose Display and Help Languages section. Click the German entry in the Display Language list and click the 'Set as Default' button below the list. Repeat the process for the Help Language list.
6. Click 'OK' to close the window and apply the changes.

How to Tell Which Version of Excel Created a Spreadsheet

1. Locate the Excel file on your computer. For example, run Windows Explorer to find the file.
2. Look at the file extension for the file. The file extension is the set of characters that come after the dot. For example, you might see .xls.
3. Decide which version of Excel created the spreadsheet. If the file extension is .xlsx, Excel 2007 created the file. If the file extension is .xls, Excel 2003 (or an earlier version of the software) most likely created the file. There is an exception: Excel 2007 has the capability of being able to make backwards compatible (.xls) files.

How to Put a Password in My File in Excel

1. Launch Excel.
2. Click the 'File' menu option, then click 'Info.'
3. Click the 'Protect Document' icon and select 'Encrypt With Password.' A window pops up and prompts you to enter a password.
4. Type a password into the box. Mix upper- and lowercase letters with numerals and special characters -- such as question marks and dollar signs -- to improve the strength of the password. Excel allows up to 255 characters in a password.
5. Click 'OK' to input the new password. Save the file normally to lock in the encryption.

How to Create a Macro in Excel 2007

1. Open Excel 2007 and click the 'Developer' tab at the top-right end of the window. Click the 'Record Macro' button at the top of the 'Code' heading.
2. Type a name for the new macro in the 'Name' text box, such as 'Adding Columns' or 'My New Macro.' Type a key in the 'Shortcut Key' text box if you want to map a shortcut to the new macro. Pressing the 'Ctrl' key and the key you specify will automatically run the new macro.
3. Type a brief description of what the macro is intended to accomplish in the 'Description' box if you think other users may potentially need your macro. Click 'OK' to begin recording the new macro.
4. Perform the specific operations you want recorded in the macro, such as adding together the values in columns, creating new rows or calculating the average of two cells.
5. Click 'Stop Recording' when you are done performing operations. Click the 'Macros' button when you need to run the macro. Click the macro's name and then choose 'Run.'

Monday, May 14, 2012

How to Delete Multiple Rows in Excel 2007

1. Open 'Microsoft Office Excel 2007.'
2. Click the 'Office' button, and select 'Open.' Locate the file you want to edit, and click the 'Open' button.
3. Click the row heading on the left side of the sheet for the first row you want to select.
4. Press and hold the 'Ctrl' key, and click the row headings for the remaining multiple rows you want to select.
5. Click the arrow beside the 'Delete' icon in the 'Cells' group under the 'Home' tab in the Office ribbon menu at the top of the program.
6. Click 'Delete Sheet Rows.'
7. Click the 'Save' icon located next to the Office button to save the changes you've made to the file.

Sunday, May 13, 2012

How to Get a List of the Unique Values in an Excel Spreadsheet

Filtering
1. Highlight the cells you wish to filter for unique values.
2. Click the 'Data' tab and select 'Sort Filter,' then 'Advanced.' Inside the box that pops up, choose 'Filter the list, in place' if you wish to filter the data within the spreadsheet. You can also copy the filtered list into a new table by clicking 'Copy to another location,' then selecting the cell range you wish to paste by entering the range in the 'Copy to' field.
3. Choose the check box for 'Unique records only,' then click 'OK' to generate the list of values.
Removing Duplicate Values
4. Highlight the cells from which you wish to remove duplicate values.
5. Navigate to the 'Data' tab and select 'Remove Duplicates,' which is located in the data tools group.
6. Select one or more columns under the 'Columns' section in the pop up box, based upon those columns from which you wish to remove duplicates. Click 'OK.'
7. A message will display showing how many values were removed from the data table and how many unique values are left. Document the information you need, then click 'OK' to view the table.
Applying Conditional Formatting
8. Highlight the cells to which you wish to apply conditional formatting for unique values.
9. Select 'Conditional Formatting' under the 'Home' tab and within the 'Style' group,
10. Choose 'Manage Rules,' then select 'New Formatting Rule.'
11. Ensure the cell range and worksheet displayed in the 'Show formatting rules for' box are appropriate. Then select 'Edit rule.'
12. Select 'Format only unique or duplicate values.' in the Edit Formatting Rule box that pops up. In the 'edit the rule description' field, choose 'Format all' as the list and then choose 'unique' or 'duplicate,' depending upon which items you want to highlight. Choose the type of formatting you want (i.e., color, font, fill) then click 'OK.'

How to Export Excel 2007 to a Works Database

Install the Microsoft Office Compatibility Pack
1. Go to the Microsoft Office Compatibility Pack for Word, Excel and PowerPoint File Formats page (see Resources).
2. Scroll down to the 'Quick Details' section and choose the language for the file you would like to download by clicking the down arrow next to the 'Change Language:' label and making your selection. Click the blue 'Download' button and save the 'FileFormatConverters.exe' file to your computer.
3. Click the down arrow next to the 'Estimated Download Time' label to choose the speed of the Internet connection you are using.
4. Read through and (if necessary) follow the instructions in the 'Overview' and 'System Requirements' sections to be sure your program will function properly once installed.
5. Click 'Start,' type 'Windows Update' and press 'Enter.' Click 'Check for updates' in the Windows Update window that opens and install all recommended software to bring your system up to date. If Windows Update does not recommend software, it is because your computer is already up to date.
6. Open the 'FileFormatConverters.exe' file you saved and follow the instructions given to install the program on your computer.Use the following instructions to save the file in CSV format if you have Excel on your computer and you do not want to install the Microsoft Office Compatibility Pack.
7. Open the file in Excel that you want to transform and click the 'Office' button, select 'Save As' and then 'Other Formats.'
8. Select the downward arrow next to the 'Save as type' label in the 'Save As' dialog box and scroll to find 'CSV (Comma delimited) (*.csv)' and select it. Click the 'Save' button. Save one sheet at a time if saving data from multiple sheets in a workbook. If you are using this method, use the CSV file instead of the Excel file for the conversion.
Convert the Excel Spreadsheet to a Works Spreadsheet
9. Click 'Start' and navigate to the file you want to convert. Right-click on the file, select 'Open with,' then select 'Choose Program' to open the 'Open With' window.
10. Scroll through the list to find 'Microsoft Works.' If it is there, select it and click the 'OK' button to close the 'Open With' dialog box and open the file in the selected program.If you did not find Microsoft Works listed in the 'Open With' dialog box, find the Microsoft Works program and click the 'wksss.exe' file, then click 'OK' to close the dialog box and open the file in the selected program.
11. Click on 'File' on the top menu in Works and select 'Save As.' Be sure the 'Save as type' option is set to Works format. The extension will be '.wps.'
12. Name the document and click the 'Save' button.
Create the Works Database
13. Click cell A1 (the top-left cell in the spreadsheet) and hold down the 'Ctrl' and 'Shift' keys and press the 'End' key to select the active cell range in the workbook.
14. Click 'Edit' on the top menu and select 'Copy' to copy the cells you want to include in your database.
15. Click 'File' on the top menu and select 'New' and then click 'Add to a Field' to create a new database.
16. Click 'Edit' on the top menu and click 'Paste' and click 'Yes' to let Works add extra fields.

How to Convert Columns to Percent Using VBA

1. Open the Excel 2010 spreadsheet where you want to format a column as a percentage. Once the spreadsheet is open, press 'Alt' and 'F11' to open up the VBA console.
2. Right-click on the first worksheet listed on the left side of the VBA console. Choose 'Module' from the popup menu under 'Insert.' Click on the new module that appears a few lines underneath the worksheet.
3. Place your cursor in the white space on the right side of the VBA console. Type 'Sub ColConv' and press 'Enter.' VBA will automatically add the 'End Sub' command to the code.
4. Place your cursor on the line between the 'Sub' and 'End Sub' commands. Enter the following code into the console:Columns('A:A').SelectSelection.NumberFormat = '0.000%'Change both 'As' to whatever column you want to convert. Change the number of '0s' after the decimal to determine how many decimal places the percentage will show. For example, '0.00%' will display a percentage as '15.55%,' while '0%' will display a percentage as '16%.'
5. Click the 'X' at the top of the VBA console to close it. You can run your macro by clicking the 'Macro' button in the 'Developer' tab. Just choose 'ColConv' from the list and click 'Run' to convert your specified column to a percentage.

How to Use Macro in Excel With Examples

1. Start Excel. A new workbook is automatically opened for you.
2. Open the macro recording window. In Excel 2003, click on 'Tools > Macro > Record New Macro' and in Excel 2007 click 'View > Macros > Record Macro.'
3. Type in a meaningful name in the macro box according to what you want to record: for example 'OpenTaxFile,' or 'MakeExpenseSheet.'
4. Enter a shortcut key for your macro in the 'Shortcut key' box. For example, enter a 'O' to assign the keys 'Ctrl' and 'O' to your macro.
5. Click on 'OK.' The macro recorder is now running.
6. Perform the task that you want the macro to automate. For example, open a particular file by click on 'File > Open' in Excel 2003, or 'Office > Open' in Excel 2007 and then locate the file. You can also format a spreadsheet: for example, make an expense sheet by typing in any information that you want contained on the sheet, like mileage and business expenses.
7. Stop macro recording by clicking on 'Macro > Stop Recording.' Your macro is now ready for testing and use.

Saturday, May 12, 2012

How to Calculate R Squared for Measurements in Excel

1. Open a new Excel 2010 spreadsheet. Click on cell 'A1' and type in a header for your measurements. This text will appear at the top of the chart where your R-squared value is located.
2. Click on cell 'A2' and enter your first measurement. Then enter the rest of your measurements down column 'A.'
3. Select cell 'A1' and click the 'Insert' tab at the top of the Excel window. Click the 'Line' button, located in the 'Charts' area of the ribbon. Select the 'Line' option, which will be the icon in the upper-left corner, from the menu that appears. A line chart will appear on your spreadsheet.
4. Click anywhere on the chart to select it, and then click the 'Layout' tab at the top of the screen. Click the 'Trendline' button in the Analysis area of the ribbon, and choose 'More Trendline Options' from the pop-up menu.
5. Click the radio button next to 'Linear' in the Format Trendline window. Then place a check mark next to 'Display R-squared value on chart,' near the bottom of the window. Click 'Close' to close the window. Your R-squared value will appear just above the trendline on the chart.

How to Use Squared in Excel

1. Launch Microsoft Excel, then enter the number to be squared into cell A1. Press 'Enter' to move the focus to cell A2.
2. Press the Equal sign '=' and type the word 'Power' followed by a left parenthesis '(' and then click once on cell A1 to select it as the source. Type a comma ',' and then the number '2' and a right parenthesis ')' and press 'Enter.' The cell A2 now shows the squared value of whatever is entered into cell A1, or in other words the contents of cell A1 raised to the second power. The final formula will look like this: =POWER(A1,2)
3. Change the number in cell A1 to any other number and the formula in cell A2 will automatically update to reflect the value of cell A1 raised to the second power.

How to Print Microsoft Excel Spreadsheets With Gridlines

1. Open the Microsoft Excel spreadsheet that you would like to print with gridlines. Make sure that all of the information on the spreadsheet is correct and ready to print.
2. Click 'File' and 'Page Setup.' This will bring up a box where you will be able to change different features of your spreadsheet.
3. Click the tab at the top of this box that says 'Sheet.' From this box you will be able to change different ways that you can print your spreadsheet. Find the box that says 'Print.' You will see a box inside this area that says 'Gridlines.' Click this so that there is a check in the box. Click 'OK.' When you print your spreadsheet, you will be able to see the gridlines behind your content.

How to Convert Decimal Time Into Seconds in Microsoft Excel

1. Open a new Excel 2010 spreadsheet.
2. Click on cell 'A1,' and type in the time in decimal form. Enter the minutes before the decimal point if you want the result to display like it would on a clock.
3. Click on cell 'A2.' Type the following formula into the cell: '=((A1-INT(A1))*60)' (without quotes). Press 'Enter' to complete the formula, and you will see the number of seconds appear in the box. This formula will remove the minutes from the equation entirely, so if you entered '4.5' into cell A1, your result here would be '30,' as '.5' is exactly half of a minute.
4. Click on cell 'A3.' Type the following formula into the cell: '=(A1/60)/24' (without quotes). Press 'Enter' to complete the formula. This will give you the numerical version of the time, which Excel can then convert into hours, minutes and seconds.
5. Right-click on cell 'A3,' and choose 'Format Cells' from the pop-up menu. Select 'Time' from the list of options on the left side of the window. Choose one of the time options that includes hours, minutes and seconds, but does not include a.m. or p.m.
6. Click 'OK' to close the window. You will see cell A3 change to show you your entered time in hours, minutes and seconds.

How to Use Goal Seek in Visual Basic

1. Open the Excel document in which you'd like to run your Goal Seek macro.
2. Find the two cells you would like to use for Goal Seek. The first should be the cell for which you have a fixed goal. The second should be a cell you allow Excel to change in order to fulfill that goal. The first cell's value must be a formula that is based on the second cell--otherwise, no matter what changes Excel makes to the second cell, no changes will appear in the first.
3. Open the Macro window. In Excel 2007 or later, do this by clicking on the 'View' tab of the ribbon, then clicking 'Macros.' In earlier versions of Excel, click the 'Tools' menu, choose 'Macro,' then choose 'Macros.'
4. Type a name for your Goal Seek macro in Macro window, and click 'Create.' This will launch the Visual Basic for Applications editor and show you the text of the new Macro, which will start out as an empty subroutine like this:Sub MyMacroName()
End Sub
5. Create a Range object representing the cell for which you have a known goal (the first cell from Step 2). For example, if you'd like to Goal Seek cell A1, write:Dim goalCell As Range
goalCell = ThisWorkbook.Worksheet(1).Range('A1')
6. Create a Range object representing the cell you'd like Excel to change to cause the first cell to reach the known goal. For example, if you'd like Excel to manipulate cell A2, write:Dim variableCell As Range
variableCell = ThisWorkbook.Worksheet(1).Range('A2')
7. Call the GoalSeek method on the goal cell's Range. As the first argument, provide the value you'd like it to Goal Seek to; as the second argument, provide the variable cell's Range. For example, if you'd like to Goal Seek to 0, write:goalCell.GoalSeek(0, variableCell)

How to Make a Chart With Standard Errors in Microsoft Graph

1. Type your data into Excel using a column format. For example, if you are creating a chart for children's test scores, type the names of the children in the first column, then type the test scores in the second column.
2. Highlight the data in the document. Click at the top left, then drag the cursor to the bottom right.
3. Click 'Insert,' then click 'Chart.' Follow the instructions on the Chart Wizard to create a graph of your choice.
4. Double click a series on the chart, then press 'Ctrl' and '1' to open the Format Series dialog box.
5. Click either the 'X Error Bars,' or the 'Y Error Bars' tab, depending on what type of error bar you want to add.
6. Click an icon that represents the specific error bar you want to add. The choices are 'Both,' 'Plus,' 'Minus,' or 'None.'
7. Click a radio button for the error amount, and type an amount into the error text box. For example, click the radio button for 'Fixed value,' then type '2' into the text box.
8. Click 'Ok.'

How to Delete a Custom Toolbar in Excel 2003

1. Open Excel 2003 and locate your customize toolbar. Click 'Tools' on the menu bar and select 'Customize.' The Customize dialog box appears.
2. Click the 'Toolbars' tab in the Customize dialog box. Search the menu for your custom toolbar's name. Use the scroll bar to drag up and down to find your custom toolbar's name. Remove the check from custom toolbar's name.
3. Click the toolbar name. The toolbar name is highlighted in blue. Click the 'Delete' button. The custom toolbar is deleted.

Friday, May 11, 2012

How to Import an MS Outlook Calendar Into MS Excel

1. Launch Microsoft Outlook.
2. Click the 'File' tab at the top of the window.
3. Click 'Open,' and then click 'Import.'
4. Click 'Export to a file,' and then click 'Next.'
5. Click 'Microsoft Excel 97-2003,' and then click 'Next.' This is the .XLS file type, which is compatible with all versions of Microsoft Excel from 97 to 2010.
6. Click 'Calendar,' and then click 'Next.'
7. Click 'Browse,' and then select a location on your computer for your Excel calendar file. Click 'Next' to continue.
8. Click 'Finish.'
9. Browse to the file location you selected earlier, and then double-click the file to open it in Excel.

How to Remove Excess Tab Symbols From an Excel Spreadsheet

1. Make a copy of the file you want to remove tab symbols from. Work on the copy of the file.
2. Go to the 'Home' tab.
3. Click on the 'Find Replace' icon (it looks like a pair of binoculars). This will pull up the Find/Replace dialog box. Alternatively, hit 'Ctrl'-'H' to pull up the Find/Replace dialog box.
4. For the item to be found, enter '^t'. Leave the 'Replace' field blank.
5. Click on the 'Options' button and select 'Workbook' from the first drop-down menu.
6. Click on the 'Find Next' button; make sure it's highlighted what you want replaced, and click 'Replace.'
7. Repeat the previous step until all the tab symbols you want replaced are removed. Alternatively, if you want to eliminate all tab symbols, select 'Replace All.'

How to Remove Add

Delete Toolbars
1. In Excel 2007, point to the toolbar you want to remove with your mouse.
2. Right-click with your mouse.
3. Select 'Delete.'
Hide Toolbars
4. Click on the 'View' tab in Excel 2007.
5. Find the name of the toolbar you want to hide in the 'Show/Hide' section.
6. Uncheck the box next to the toolbar's name.

How to Restore Office File Associations

1. Click on the Start button and choose Run. Type the following in the run command box and press the Enter key: 'winword /regserver'. Doing so will start the Microsoft Word application and automatically re-associate all word processing file types with the Word program.
2. Click on the Start button and choose Run. Type the following in the run command box and press the Enter key: 'excel /regserver'. This will start the Microsoft Excel application and will associate all spreadsheet data file types with the Excel program.
3. Click on the Start button and choose Run. Type the following in the run command box and press the Enter key: 'powerpnt /regserver'. This will start the Microsoft Powerpoint application and will associate all Powerpoint presentation files with the Powerpoint program.
4. Click on the Start button and choose Run. Type the following in the run command box and press the Enter key: 'msaccess /regserver'. The Microsoft Access application will start and it will associate all appropriate database file types with the Access program.

How to Create a Sum Formula in Microsoft Excel 2003

1. Create a new file and name it for easy saving by left-clicking on the “File” tab on the command bar and selecting “Save As.”
2. Create a group of numbers in cells. Select a desired column and then create at least two or more groups of numbers in a row, from top to bottom. For example, if you were to use column “A”, then you would create a group of two or more numbers in cells “A1” and “A2”.
3. Create a sum formula for the cells. To create a sum formula, select a vacant cell and activate it by left-clicking on the desired cell. Then enter your sum formula. To create a SUM formula in Excel you use this format: SUM=(A1 A2). Before each sum formula, type in the word “SUM=” followed by the cells that will be summed in parenthesis.
4. Save your spreadsheet. Make sure to save your new spreadsheet by clicking on the “File” tab and selecting “Save.”

Subscribe to: Posts (Atom)