Excel

Browse » Home » All posts

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.

Subscribe to: Posts (Atom)