Excel

Browse » Home » All posts

Wednesday, March 21, 2012

How to Insert a File Location in Excel 2007

File Path in a Header or Footer
1. Open the saved worksheet.
2. Click on the 'Insert' tab on the command ribbon.
3. Click on the 'Header and Footer' button in the 'Text' group. The 'Design' tab will appear.
4. Click on the header or footer text box.
5. Click on the 'File Path' button in the 'Header Footer Elements' group. A code will appear in the text box.
6. Click away from this text box. The workbook's file path will display in the text box.
File Path in a Hyperlink
7. Open the saved worksheet.
8. Click on one cell to insert this file location. You can opt to select the cell's text for an embedded link.
9. Click on the 'Insert' tab on the command ribbon.
10. Click on the 'Hyperlink' button in the 'Links' group. A dialog box for 'Edit Hyperlink' will appear.
11. Click on the down arrow of the 'Look In' box for the location of the file. For example, the file may be saved on the desktop.
12. Click on the file in the scrollable window.
13. Click the 'OK' button. The hyperlink will appear in the cell.

How to Convert a PDF File Into an Excel File

1. Download and install PDF-to-text conversion software, or convert your file online. There are several programs that will do this; some are free and some cost money but have free trial periods. See the Resources section for information.
2. Process the PDF-to-text conversion, and save the text file.
3. Open a blank Excel spreadsheet and import the text file. In Excel 2003, go to Data>Import External Data>Import Data. In Excel 2007, go to Data>Get External Data>From Text. The 'Text Import Wizard' will pop up.
4. Choose the type of file that best describes your data. You will see a preview of the text below, to help you choose. If commas or tabs separate the data you wish to import, choose 'Delineated.' If the fields are already in columns, with spaces in between, choose 'Fixed Width.'
5. Follow the wizard through the importing steps. In each screen you will be able to see a preview of how the data will be imported, so you can always go back and change a step if it does not look correct. For example, if you chose 'Delineated' and the preview screen does not show separate columns for your data, go back and change your selection to 'Fixed Width.'
6. Select 'Finished' and then 'Existing Worksheet' as the location to open your data. Your PDF document is now in an Excel file. It will likely not be perfect and you will need to do a little tweaking to have all the data where you want it, but most of the work has been done.

How to Create Headers in Microsoft Excel 2003

1. Scroll to the “View” tab on the command bar to access the View menu.
2. Under the “View” submenu, select “Header and Footer,” which will open a header-and-footer properties box. Click on the “Custom Header” button to access the Header Properties box.
3. Enter the desired header text into the left, right and center sections. Using the icons inside the box, you can insert a picture, date stamp, pages to be included and files to be included, and you can even change the font type. Make sure you have selected a section, then click on the corresponding icon to add the desired features.
4. Click on the 'OK' button to add your header to your spreadsheet.

How to Delineate Commas in Excel

1. Open Microsoft Excel and open the comma-separated list in another program such as Notepad.
2. Click and drag with the mouse pointer to highlight each item in the comma-separated list. Right-click the list and then click 'Copy' to copy the list to the Windows clipboard.
3. Click cell A1 in the upper left corner of the Excel spreadsheet. Right-click the cell and then click 'Paste' to paste the list from the Windows clipboard into Excel.
4. Click the 'Data' tab at the top of the Excel window.
5. Click to highlight column 'A' of the spreadsheet and then click the 'Text to Columns' button in the 'Data Tools' section of the toolbar.
6. Click the 'Delimited' radio button at the top of the window and then click 'Next.'
7. Click the 'Comma' check box under 'Delimiters' and remove any other checks.
8. Click the 'Finish' button.

Tuesday, March 20, 2012

How to Make a Simple X

1. Open a blank worksheet in Microsoft Excel 2007.
2. In the first cell, enter “X.” Press “Tab,” and enter “Y” in the next cell over. These will be the column headings for your X and Y data.
3. Enter a series of X data points in the column under “X.” It’s a good idea to simply enter regular intervals of X to make up the horizontal axis. For instance, enter all the values ranging from “-5” up to “5” to display the line across a negative and positive axis.
4. Calculate the Y value that corresponds to each X value. The equation format for a line graph is Y = MX B, where M is the slope of the line and B is the Y-intercept value. If you have a linear equation for the line, plug the X values you’ve entered into the equation to calculate the Y values. Enter each value in the Y column.
5. Highlight the cells you want to graph on a chart.
6. Click the “Insert” tab. In the “Charts” group, select the “Scatter” icon.
7. Select “Scatter with Smooth Lines and Markers.” This will display X versus Y in a line graph that connects the data markers. The chart will display on your worksheet.
8. Click the “Design” tab, which appears under “Chart Tools” when you select the chart. If you want to change the format of the chart, make a selection in “Chart Layouts” and a line style under “Chart Styles.” To format individual elements of the chart, click the “Format” tab and select the element you want to change, such as “Chart Title” or “Axes.”
9. Click the “Microsoft Office Button,” and select “Save” to save the data and graph you’ve made.

How to Recover an Unsaved Spreadsheet in Excel

1. Open Excel after your computer has crashed or froze. The Document Recovery box will appear on the right side of the application after you open it.
2. Scroll over the recovered file from that task pane and then click on the arrow that appears. A menu will then pop up.
3. Click on the “Open” option to open the recovered file. You can also click on the “Save As” option to save the file so that you don’t risk losing any data again.
4. Close out of the file if it seems to need to be repaired in any way still. Click the “File” option from the top toolbar menu and then click on the “Open” button.
5. Locate the recovered file that you just saved and click on it. Click on the drop-down menu option. Click on the “Open and Repair” option and any damage to the file should be fully repaired.

How to Convert Excel 2007 Files to Excel 2000

If You Have Excel 2007
1. Open Microsoft Excel 2007 on your computer. The program file is typically located under the 'Microsoft Office' folder under the 'All Programs' option of the 'Start' menu.
2. Open the Excel 2007 workbook you wish to convert. To open a workbook, select the 'Open' option from the Microsoft Office button in the top left-hand corner of the program. Select your file and click the 'Open' button to continue.
3. Select the 'Save As' option from the Microsoft Office button. Select the 'Excel 97-2003 Workbook' option under the 'Save a copy of the document' section.
4. Name your file and save it to your preferred destination. The workbook is now compatible with Excel 2000.
If You Have Excel 2000
5. Open the Microsoft Excel conversion tool website in your web browser (see Resources). Review the system requirements to ensure your system is eligible for the compatibility pack.
6. Click the 'Download' button toward the top of the page to begin downloading the file. If the download does not start automatically, click the 'Start Download' link on the page you are redirected to. Click the 'Save File' button when prompted.
7. Open the executable file once it finishes downloading. By default, this file is named 'FileFormatConverters.exe.' Click 'Run' when prompted to begin the installation process.
8. Accept the license terms by clicking on the box next to the 'Click here to accept' text at the bottom of the screen. Click 'Continue' to begin extracting the files. Click 'OK' when the extraction is complete.
9. Open Excel 2000 from your 'Start' menu. Open the Excel 2007 file by selecting the 'Open' option from the 'File' menu and locating your file. Excel will automatically begin converting the file for use in Excel 2000. Note that the file is converted for one-time use and will need to be reconverted each time it is opened.
10. Click the 'Save As' option from the 'File' menu to permanently save your Excel 2007 file as an Excel 2000 file. Select the 'Microsoft Excel Workbook' option from the 'Save as Type' section and name your file. This file will be permanently compatible with Excel 2000.

How to Make an Organizational Chart in Excel

1. Open Excel to a new workbook. Choose the 'Insert' tab, and in the Illustrations group, select 'SmartArt.'
2. Choose 'Hierarchy' in the Choose a SmartArt Graphic gallery. Then select the organization chart layout and click 'OK.'
3. Select a box and type in your text by clicking the [Text] label in the box and either pasting or typing the information to display in this box.
4. Add more boxes to complete your organization chart. Click the box closest to where you want to add the box and choose from the following: 'Add Shape Before' adds the box on the same hierarchy level, but just before the box you've clicked; 'Add Shape After' also stays on the same hierarchy, but adds the box after; 'Add Shape Below' adds a box one level down and 'Add Shape Above' adds a box one level up.
5. Add assistants, as appropriate, by clicking box on the hierarchy and selecting 'Add Assistant.'
6. Modify the relationships among the boxes by denoting them with the appropriate line. Click the 'Format Shape' found on the shortcut palette. Select 'Line Style,' and then select either a dashed or solid option.
7. Define the correct layout of the boxes on chart. Select 'Standard' to display the boxes as hierarchical levels. Choose 'Both' to display reporting relationships, with boxes on either side of a vertical line. Select 'Left Hanging' to display the reporting relationship with all boxes on the left side of the line, which is ideal if you are displaying multiple divisions on one chart.
8. Modify the colors of the boxes to further clarify your hierarchy. Select the SmartArt graphic, and then select 'SmartArt Tools' from the Design tab. From the SmartArt Style groups, select 'Change Colors.' Then select the colors to use for your organization chart.
9. Save the chart by choosing the 'Save' icon and naming the file.

Monday, March 19, 2012

How to Plot Points on a Grid

1. Open Excel or another spreadsheet. Enter headers in cells A1 and B1 that correspond to your variables. For example, if you want to plot age against income, put 'Age' in cell A1 and 'Income' in cell B1. The A column will correspond to the X axis and the B column to the Y axis. (You can enter the data in any cells; just be consistent.)
2. Enter the appropriate data. In our example, enter the data for age and income in the appropriate columns, starting in row 2 and continuing with one row per person.
3. Plot the data. In a new cell, click 'Insert,' 'Scatter' and then click on the graph in the upper left of the panel of graphs.
4. Modify the plot. In the Charts Layout section, scroll down with the arrows until you get to layout 10, and click on it.
5. Modify the axes. Inside the graph, click on 'Axis title' on the horizontal axis and enter 'Age.' Then click on 'Axis title' on the vertical axis and enter 'Income.'

How to Extract the First Word of a String in Microsoft Excel

1. Click in the cell to the right of the one you want to edit. For example, click in B2 if you want to extract the first word from cell A2.
2. Enter the following formula and then press the 'Enter' key:=LEFT(A2,FIND(' ',A2)-1)This tells Excel to find the first word in the word string in cell A2 and to extract it to cell B2 for our example.
3. Copy the formula and apply it to all cells in the preceding column. To do that, hover your mouse over the black square in the lower right corner of the cell. When your mouse pointer changes to a plus symbol, click your mouse and drag down.

How to Make Column A the X

1. Select the data you want to use for both the x- and y-axes by clicking and holding the mouse button while selecting the cells.
2. Select the 'Line' button from the chart options on the 'Insert' tab. Select the style of the line that you want to use. In the resulting graph, Excel will have drawn columns A and B as lines.
3. Right-click the graph. After a menu appears, click the 'Select Data...' option to open the 'Select Data Source' dialog. Both 'Series 1' and 'Series 2' are listed under the 'Legend Entries' list.
4. Click 'Edit' under the 'Horizontal Axis Labels' list to open the 'Axis Labels' dialog. Click the icon that displays a red arrow, and then highlight the column on the spreadsheet that you want to denote as the x-axis. In this example, this is all the numbers in column A. Click 'OK' when finished.
5. Select 'Series 1' in the 'Legend Entries' list. Click the 'Remove' button, and then click 'OK.' The x-axis now denotes the quantities in column A.

How to Restrict Data Entered in Excel to a Specific Length of Text

1. Open the spreadsheet document you'd like to apply the text restriction to.
2. Click the 'Data' tab, then click 'Data Validation.'
3. Click 'Settings.'
4. Select 'Text Length' in the 'Allow' field.
5. Select 'Less than or equal to' as the rule type.
6. Enter the maximum number of characters you'd like to allow in the text field.
7. Select 'Ignore blank' to not count spaces and other blank characters toward the text limit. If spaces should count toward the limit, leave this unchecked.
8. Type an optional message that will be displayed to the user if she attempts to enter too much information in the 'Input Message' field.
9. Select 'Show error alert after invalid data is entered' to make sure the user receives a notification that his text was not entered.
10. Click 'Save' to save your document.

Sunday, March 18, 2012

How to Use Excel's Hyperlink Function

1. Know what the hyperlink function does. Hyperlink creates a shortcut or jump that opens a document stored on your computer's hard drive, the Internet or on a network server.
2. Follow the syntax of the Hyperlink function, which is =HYPERLINK (link_location, friendly_name). The parameter link_location refers to the area where the document to which you wish to link is stored. Make sure you include the entire address including http:// when linking to the Internet, or the full path when linking to a document on a drive. The friendly_name parameter is optional. It tells Excel what text to display for the hyperlink. If friendly_name is blank, Excel displays the link itself. Enclose both the link_location and friendly_name parameters in quotation marks.
3. Use the Hyperlink function. If you want to link to a picture on your C: drive and make a hyperlink in Excel that displays, 'Click here to see a picture of an elephant,' then use the following formula. =HYPERLINK('c:\My Pictures\elephant.jpg', 'Click here to see a picture of an elephant'). Don't put a period at the end of the equation. If you want to link to a website such as eHow, and you want the user to see the link address on which he's clicking, omit the friendly_name parameter like this: =HYPERLINK('http://www.ehow.com'). Again, don't include a period at the end of the equation.

How to Remove a Cell Border

1. Select the cell or cells containing the border that you would like to remove. To select a single cell click directly on it, and to select multiple cells, click the left-mouse button and drag until all of the cells are selected.
2. Click the 'Borders' drop-down menu. The 'Borders' drop-down menu is located on Home tab's Font menu on the toolbar at the top of the window. It has an image of a four squares with dotted or solid lines, indicating cells and borders.
3. Click 'No Border' from the 'Borders' drop-down menu to remove the boarder from the cell or cells selected.

How to Use Multiple Consolidation Ranges in Excel 2007

1. Launch Microsoft Excel from the 'Programs' or 'All Programs' menu. Click the 'Office Button' in the upper left corner of the program window and select 'Open.' Navigate to the file you wish to consolidate the ranges of, and select it. Click 'Open.'
2. Click the first worksheet of the range that will be consolidated to select it. Click the 'Home' tab. Select 'Insert' and then 'Insert Sheet' to insert a new sheet for your consolidated range. Double click the sheet name tab at the bottom of the program window to select it. Type a name of your choice. Press the 'Enter' key on the keyboard.
3. Click in the upper left cell of the area where you want the consolidated data to appear. Click the 'Data' tab in the 'Data Tools' group. Click 'Consolidate.' Click the downward pointing arrow to the right of the 'Function' drop-down box, and select the function you want to use to consolidate your data.
4. Click the select range button under the 'Reference' heading. Click the tab on the bottom of the worksheet with the first range to be consolidated. Select the range of cells to be consolidated. Notice that the range appears in the 'Consolidate -- Reference' box. Click the select range button to collapse it. Click 'Add' to add the range in the 'All references' list box. Repeat this step from the beginning for all worksheets.
5. Select 'Top row' under the 'Use labels in' heading, to have the column headings appear. Click 'Create links to source data' to have the data on the consolidated worksheet automatically update when the data on the other worksheets changes. Click 'Ok' to display the consolidated range.
6. Click the 'Office Button' in the upper left corner of the program window, and select 'Save' to save the file.

How to Make a Check Box in Excel

Display Developer Tab
1. Click the 'File' tab on the Ribbon and select 'Options' from the left panel.
2. Click 'Customize Ribbon' to display two panes.
3. Click the box next to 'Developer' to select it in the right pane. Click 'OK.'
Make Check Box
4. Click the 'Developer' tab on the ribbon.
5. Click 'Insert' and select the check box. A cross will appear on the screen.
6. Click and drag to insert a box with a check box and text. Select the text next to the check box, delete it and insert your own text.
7. Right-click the check box you created and select 'Format Control' to format it. You can modify colors and lines, change its size or add protection.

How to Create Links in Excel 2007

1. Click the cell (text box) where you want to insert the hyperlink.
2. Click the 'Insert' tab.
3. Click 'Hyperlink' in the 'Links' group.
4. Click 'Existing File or Web Page' under 'Link to.'
5. Enter the full website in the address box. Include 'http://' in the address.
6. Type the text that you want to display as the hyperlink name in the 'Text to display' box. This is so the person viewing the document sees the text that you chose, but it appears as a hyperlink. The web address will be hidden.
7. Click 'OK' when done.

How to Calculate Tax Gratuity in Excel

1. Label your Excel spreadsheet columns, if desired. For example, you can type 'Sale' into cell A1, 'Tax' into cell B1 and 'Gratuity' into cell C1. You can choose your font type, size and style from the formatting toolbar. You can also choose to center your typing or justify it to the right or left. If the formatting toolbar is not showing, select the 'View' tab from the top then 'Toolbars' from the drop-down menu and select 'Formatting.'
2. Format the Excel worksheet to display how you prefer. For example, to format the second row to display a dollar sign and two decimal places, click on the '2' at the beginning of row 2. Select the 'Format' tab from the top, and in the drop-down box choose 'Cells.' From the 'Category' box, choose 'Currency,' and in the 'Decimal places' box, select '2.' In the 'Negative numbers' box, choose how you prefer negative numbers to display, then click 'OK.'
3. Enter the taxable sale amount into a cell in your Excel worksheet. For example, if your taxable sale amount is $30, enter '30' into cell A1. With the suggested formatting above, Excel will display '$30.00' in the cell.
4. Create a formula to calculate the correct amount of sales tax. For example, if your sales tax rate is 5 percent, enter '=A2*.05' into cell B2. This formula tells Excel to multiply — denoted by the asterisk symbol — the value in cell A2 by .05, which is the decimal value of 5 percent. Cell B2 should now display '$1.50.' This is the amount of sales tax you owe on a $30 sale at a 5 percent tax rate.
5. Create a formula to calculate gratuities at your desired percentage. For example, if you want to pay a gratuity of 20 percent, enter '=A2*.2' into cell C2. This formula instructs Excel to multiply the value in cell A2 by .2, which is the decimal value of 20 percent. Cell C2 should now show '$6.00' as your desired gratuity amount on a $30 sale at a 20 percent tip rate.

Saturday, March 17, 2012

How to Count How Many Times a Word Appears in Excel

1. Open the Excel document containing the text that you want to analyze. Select the first cell that contains the text. Hold down the 'Shift' key and select the last cell that hold your text. This will create a selection box around all of your text. Press 'Ctrl' 'C' to copy this information to the clipboard.
2. Click on a new worksheet at the bottom of the spreadsheet. If all the worksheets are currently being used, press the new worksheet button, which is located directly to the right of the last worksheet.
3. Select cell 'B1' on the new worksheet. Press 'Ctrl' 'V' to paste the information to this sheet. Select the letter 'B' above the pasted information to select the entire column.
4. Click on the 'Data' tab and then select the 'Text to Columns' button. This will bring up a small wizard window. Select 'Delimited' on the first page of the wizard and click 'Next.' Click on 'Space' from the given options and click 'Finish.' Each word of the text will now have its own cell.
5. Select cell 'A1.' Enter in the following formula: =COUNTIF(B:Z, 'x') where 'Z' is the last column that contains text and 'x' is the word you are searching for. Press enter and the number of times that word appears in the text will be shown in cell 'A1.' Select cell 'A2' and enter in the following formula: =COUNTIF(B:Z, 'x?') to also count instances of the word that are followed by a piece of punctuation.
6. Select cell 'A3' and enter in the following formula: =sum(A1, A2) to get the final count of how many times the word appears in the text.

How to Create Checkbook Registers in Excel

1. Launch Excel 2007. Select the Insert tab.
2. On the Insert tab, click 'Table.' In the create table dialog box enter =$A$1:$G101 in the 'Where is your data?' field. Check the 'My table has headers' box then click 'OK.' Excel will create a table with seven columns and 101 rows.
3. Create labels for your checkbook register on row 1. Beginning with cell A1, replace each generic label with the following labels: Check No., Date, Transaction Description, Statement, Payments, Deposits, Balance. The balance label should be in cell G1.
4. Format the check register rows by first turning off the filters, which are automatically turned on when you create tables in Excel. To remove the filters, click the Data tab then click 'Filter.' Click the Design tab to begin color banding your rows and the balance column. In the table style options, check Last Column. Header Row and Banded Row should have a check. If they do not, place a check in the header and banded row boxes.
5. Format the check register columns by first clicking the Home tab then select column A and B. Click 'Center' in the alignment grouping. Select column C then click 'Format.' Select Column width and enter 35---or any any width you prefer. Select column E, F and G. In the number groupings, select Accounting Number format. With E, F, and G still selected, click 'Format' and increase the width of the selected columns.
6. Enter beginning balance and balance formula. Type 'Beginning Balance' in cell C2 and enter your beginning balance amount in cell G2. In cell G3 enter the following formula: =IF(AND(ISBLANK(E3),ISBLANK(F3)),'',G2-E3 F3). Copy this formula to cells G4 to G101.

How to Make an Amortization Table in Excel

1. Click on the 'Office' button in Excel 2007 and select 'New.'NOTE: If you do not have Excel 2007, click the 'Excel Amortization Schedule Template' link in the 'Resources' section below to download and save the amortization template. Continue to Step 4.
2. Scroll and select 'Schedules' on the left side of the template window.
3. Double-click to select and open the Amortization Schedule template that you want to use.
4. Input the 'Loan amount,' 'Annual interest rate,' 'Loan period in years,' 'Number of payments per year,' and 'Start date of loan.'
5. Press the 'Enter' key to complete each entry and see the Amortization Schedule automatically calculate in the table below.

Friday, March 16, 2012

How to Delete Duplicate Rows in Excel

1. Select the entire spreadsheet by dragging an outline around it with the mouse. Click the 'Data' tab, then click the 'Advanced' button in the 'Sort Filter' box.If you are using Excel 2002 or 2003, click 'Data,' 'Filter,' 'Advanced Filter.'
2. Click the radio button labeled 'Filter the List, in Place' and check the box labeled 'Unique Records Only.' Click 'OK.' All of the duplicate rows will be hidden.
3. Select the entire spreadsheet, and then press the 'Ctrl' and 'C' keys on the keyboard to copy the selection.
4. Press the 'Ctrl' and 'N' keys on the keyboard simultaneously to create a new workbook.
5. Click in the top-left cell (A1) of the new workbook, and press the 'Ctrl' and 'V' keys on the keyboard to paste the spreadsheet into it. All duplicate rows will be gone from the spreadsheet.

How to Create a Simple Report in Excel

1. Launch Excel with a new, blank workbook.
2. Click the 'Insert' tab and choose the 'Header Footer' icon in the Text group. Type a name for your report in the text field at the top of the report that appears.
3. Click 'Go To Footer' in the Navigation group on the Design tab. Type a report footer or click one of the choices in the 'Header Footer Elements' group such as 'Page Number' or 'Current Date' to insert that information in the footer.
4. Click any of the data cells where it says 'Click to Add Data' and then click 'Normal' in the Workbook Views group on the View tab.
5. Type some data labels into the first row and first column of the spreadsheet and press 'Tab' to save the cell and move to the next column or press 'Enter' to save the cell and move to the next row.
6. Point the mouse cursor at the small line between the column letters until the cursor turns into a vertical line with two arrows pointing in opposite directions. Click and hold the mouse button and slide the column width to adjust it as needed. Release the mouse button when the column width is the right size for the data.
7. Click in any cell to enter data into that cell and press 'Tab' to save the cell and move to the next column or press 'Enter' to save the cell and move to the next row.
8. Point the mouse at the top-left corner of a range of cells that contain data that should be grouped together. Click and hold the mouse button down and drag the cursor to the lower-right corner of the data and release the mouse button to highlight (or 'Select') the range of cells. Right-click on any cell in the highlighted range and click 'Format Cells.'
9. Select a number format from the 'Category' list on the 'Number' tab of the 'Format Cells' dialog box. Select from choices for the alignment of the data within the cells on the 'Alignment' tab.
10. Change the selected font size and typeface and apply a font color or attribute from the 'Font' tab. Create a border for the selected data on the 'Border' tab. Click 'OK' when finished formatting the selected cells and repeat for other ranges of data on the spreadsheet.
11. Select a range of cells containing data and labels for the data if desired. Click 'Other Charts' from the 'Charts' group of the 'Insert' menu and click 'All Chart Types' to display a list of all available charts. Click any of the listed charts and click 'OK' to create a dynamic chart of the selected data automatically. Note that the chart automatically updates to reflect any changes made to the data in the selected region.

How to Print an Excel Spreadsheet With Background

1. Open Microsoft Excel and open the spreadsheet you want to add the background to.
2. Select the 'Insert' tab in Excel's main window and select the 'Header Footer' option.
3. In the new view, position you mouse under the word 'Header' and click within that box.
4. Click the 'Picture' button located on the now showing 'Design' toolbar. Navigate to and select the picture you want to use as a background.
5. You will see text similar to '[Picture]' in the header box. This is the place holder for your image.
6. Select the 'Format Picture' button on the Design toolbar. In the window that appears, adjust the size of the image using the Height and Width input boxes or by using the Scale input boxes. When you are finished click anywhere inside the spreadsheet to exit out of the header display. Your picture will appear to the size you just adjusted it to.
7. Click the Microsoft office logo in the top left corner of your window and select the 'Print' option followed by 'Print Preview.' If the picture looks fine with its current size, you can finish by printing your spreadsheet.

How to Calculate Percents in Excel 2003

1. Determine the part and whole of the percentage you want to calculate. For example, assume a student receives 37 out of 75 points on a test.
2. Type the smaller number in cell A1. In the example above, you would type '37' in A1.
3. Type the larger number in cell A2. In the example above, you would type '75' in A2.
4. Type '=A1/A2' in cell A3. This is your percentage in decimal form. In this example, you will see 0.4933. Keep your cursor on this cell.
5. Press the '%' on the top of the window to convert the decimal to percentage. In the example, '0.4933' will change to '49.33%.'

How to Use Sumif MS Excel Together

1. Decide which cells you would like to sum. For example, you might want to sum cells in the range A1:A10.
2. Choose a criteria to sum by. For example, you might want to sum numbers that are less than 100. Other relevant criteria might be more than or equal to.
3. Place the SumIf statement in an empty cell:=SUMIF(A1:A10,'

Thursday, March 15, 2012

How to Apply Formatting Across Multiple Cells in Excel

1. Open the Excel 2010 file that contains the cells where you want to change the formatting. Right-click one of the cells you want to change and choose 'Format cells' from the pop-up menu.
2. Select the tab at the top of the 'Format Cells' window that corresponds to the type of formatting you want to do. You can choose from 'Number,' 'Alignment,' 'Font,' 'Border,' 'Fill' and 'Protection.' Once you select a tab, change the options in the window to get the cell formatted as you like it. Once all of your formatting changes are done, click 'OK.' You will see the cell change according to your formatting.
3. Click the cell you just changed, then click the 'Home' tab at the top of the screen. Locate the 'Clipboard' area on the left end of the ribbon. Double-click the 'Format Painter' button and a small paintbrush will appear next to the cursor. Double-clicking, instead of single-clicking, will let you change the format of multiple cells.
4. Select the cells to which you want to copy the formatting of the original cell. Select multiple cells at once by clicking and dragging the mouse or select each cell one by one. When all the desired cells have the same format, click the 'Format Painter' button again to return the cursor to normal.

How to Write a Macro in Word That Reads Data From Excel

1. Open Excel, then type any value in the first cell of the first spreadsheet. Click the Office button's 'Save' command, then type 'c:\ReadFromExcel.xlsx' for the filename. Click 'Save' to save the workbook, then close Excel.
2. Click the Office button in Word, then click the 'Options' button. Click the 'Developer' checkbox to display the 'Developer' tab, which holds Word's macro commands.
3. Click the 'Developer' tab's 'Visual Basic' button to enter the Visual Basic development environment.
4. Click the 'Tools' menu, then click the 'References' command. Click the 'Microsoft Excel objects' item, which lets your macro access the virtual objects of Excel.
5. Type the following program in the code window. This program creates a link to the Excel application, then close the link. The program doesn't yet do anything with the established link. You'll now use the link to open the Excel workbook you created in step 1.Public Sub ReadExcelData()Dim pgmExcel As Excel.ApplicationSet pgmExcel = CreateObject('Excel.Application')pgmExcel.QuitEnd Sub
6. Type the following new statements after the 'Set' statement. These statements open the Excel workbook you created in step 1, then close the workbook. The revised program doesn't yet read any information from the workbook. The next statement you'll write will read information from the workbook.PgmExcel.Workbooks.Open 'c:\ReadFromExcel.xlsx'
7. Type the following statements after the 'Open' statement. This statement uses the pgmExcel object to read the value of the first cell in the workbook from step 1. The 'MsgBox' statement displays that value.MsgBox pgmExcel.ActiveWorkbook.Sheets(1).Cells(1, 1)
8. Click any of the program's statements, then click the 'Run' command of the 'Run' menu. Your program will run and display the value of the cell into which you typed in step 1.

Wednesday, March 14, 2012

How to Create a Basic Inventory Spreadsheet With Excel

1. Launch Microsoft Excel and click on 'Create Header' to create a header or title for your inventory spreadsheet.
2. Click inside cell 'A1.' Type the word 'Items.' You have now created a column for your items to be listed. If you want the column to stand out, you can also highlight the text, make it boldface or underline it.
3. Create columns that show the date of inventory and the quantity of items on that specific date. Click inside cell 'B1.' Type the text 'Date.' Click inside cell 'C1' and type the text 'Quantity.' Move to cell 'D1' and type the text 'Date.' Continue this pattern as much as you like. You can always add more columns later if necessary.
4. Type the name of the first item being inventoried inside cell 'A2.' Type the date of the inventory and the amount of the item that is stocked inside cell 'B2.' Move to the next item being inventoried by typing the name of the item inside cell 'A3.' Type the date and quantity inside of cell B3.' Continue this pattern until you complete your inventory listing.
5. Save your inventory spreadsheet to your computer's hard drive.
6. Start in column 'C1' for your next inventory. The next inventory after that is column 'D1.' Continue this pattern for each inventory.

How to Create a Break Even Chart in Excel

Label Data
1. Type 'Fixed Costs' into cell A1.
2. Type 'Unit Expense' into cell A2.
3. Type 'Unit Revenue' into cell A3.
4. Type 'Units' into cell C1.
5. Type 'Revenue' into cell D1.
6. Type 'Expense' into cell E1.
Input Data
7. Input your fixed costs of a production into cell B1. For example, if rent for the factory is $25 per month, then type '25.'
8. Input the cost of each unit you produce into cell B2. For example, if each widget costs you $5 to manufacture, type '5.'
9. Input the revenue you make for selling each unit into cell B3. For example, if each widget sells for $10, type '10.'
10. Input the number of units sold in column C, under the words 'Units.' For example, you might write '1' in cell C2, '2' in cell C3, and so on until you write '10' in cell C11.
11. Input the following formula into cell D2:
=$B$3*C2
12. Copy the formula from the previous step by left clicking on D2 and pressing 'Ctrl' and 'C' at the same time.
13. Highlight cells D3 through D11 by clicking D3 and dragging down to D11.
14. Paste the formula by pressing 'Ctrl' and 'V' at the same time.
15. Input the following formula into cell E2:
=$B$1 $B$2*C2
16. Copy the formula from the previous step into the cells in column E by following the same steps as copying and pasting the previous formula.
Create the Chart
17. Click 'Insert' > 'Scatter' > 'Scatter with smooth lines and markers' to insert a blank chart into your spreadsheet.
18. Right click on the chart and click 'Select Data' to display the Select Data Source dialog.
19. Click 'Add' under Legend Entries to display the Edit Series dialog.
20. Type 'Revenue' into the 'Series Name' text box.
21. Click the 'Series X Values' text box, then highlight cells D2 through D11, the entire Revenue column.
22. Click the 'Series Y Values' text box, then highlight cells C2 through C11, the entire Units column.
23. Click 'OK' to exit the Edit Series dialog.
24. Click 'Add' under Legend Entries to display the Edit Series dialog.
25. Type 'Expense' into the 'Series Name' text box.
26. Click the 'Series X Values' text box, then highlight cells E2 through E11, the entire Expense column.
27. Click the 'Series Y Values' text box, then highlight cells C2 through C11, the entire Units column.
28. Click 'OK' to exit the Edit Series dialog.
29. Click 'OK' to exit the Select Data Source dialog.

How to Extract Data Onto a Table Using Excel 2007

Create a Table
1. Open the spreadsheet and highlight the data you want to put into a table.
2. On the 'Insert' tab, click 'Table.' If you want to include the headers in your data, choose the box next to 'My Table Has Headers.' Click 'OK.'
3. Your data will appear as a table. If you've excluded any data, it will still appear. If this isn't what you had in mind, try creating the table again, this time including all your data. Then, when your table is created, choose in the next step to exclude and hide any additional data.
4. Format the table by using the drop-down boxes that appear next to the headers or in the 'Table Tools Design' tab, which appears at the top of the screen when you make a table. Here you can change the color of the table, sort the data and choose to exclude data.
Convert Table Data Back to a Range of Data
5. If you decide to revert back to a range of data, select a cell in your table.
6. Click on the 'Design' tab, then choose 'Convert to Range.' This action removes the table but leaves the data and color formatting intact. The 'Design' tab will disappear.
7. Your data is now in a simple Excel format. You can manipulate it as you would a traditional spreadsheet.

How to Change the Color of a Cursor in Excel

1. Click the 'Start' button in Microsoft Windows.
2. Click 'Control Panel.'
3. Click the 'Ease of Access' category.
4. Click 'Ease of Access Center.'
5. Click the 'Make the Mouse Easier to Use' link under 'Explore All Settings.'
6. Select the color scheme you want to use for the mouse cursor. Choose from a selection of white, black or inverting, in different sizes.
7. Click 'Apply.' Click 'OK.'
8. Open Microsoft Excel.

Tuesday, March 13, 2012

How to Make Excel Calculate Division With Remainders

1. Launch Microsoft Excel and open a new, blank worksheet.
2. Enter the top number of a fraction or the number to the left of the division sign (the dividend) into cell A1. For the division problem '9/4,' type '9' (no quotes) and press 'Enter.'
3. Enter the bottom number of a fraction or the number to the right of the division sign (the divisor) into cell B1. If you are solving '9/4,' type '4' (no quotes) and press 'Enter.'
4. Use the Quotient function to find the integer portion of the result. Type the following into cell C1:=QUOTIENT(A1,B1)Press 'Enter.' For the problem '9/4,' you see a result of '2.'
5. Use the Mod function to find the remainder. Type the following into cell D1:=MOD(A1,B1)Press 'Enter.' If you are dividing 9 by 4, there is 1 left over, and you see a result of '1.'
6. Show the quotient and remainder in a single cell with the letter 'R' designating the remainder. Type the following into cell E1:=C1' R 'D1Press 'Enter.' For the division problem '9/4', you see '2 R 1.'

How to Compare Two Excel Spreadsheets

1. Open Excel and use 'File,' and then 'Open' to open the first file to use for comparison.
2. While the first document is still open, use 'File,' and then 'Open' to open the second file.
3. Select the 'View' tab, and then select 'New Window.'
4. Select 'View Side by Side.'

How to Make a Timeline in Excel

1. Plan your timeline ahead of time. Sketch it out on a piece of paper so you can refer to it as you design the timeline in Excel.
2. Open a new worksheet in Excel.
3. Go to 'File' then 'Page Setup' and change the page orientation to landscape. This will turn your worksheet horizontal.
4. Drag your cursor from the first cell to the length you expect your timeline to take up. Click 'Format,' 'Cells,' 'Alignment' and then check the box next to 'Merge Cells' and click 'OK.'
5. Place your cursor in the merged cell you just created and type the title of your timeline. Click the center alignment button under alignment options to center the title. You can format the size and font for the title under the 'Home' tab and the 'Font' group.
6. Skip down two or three rows and then enter the dates you want included on your timeline, skipping a cell between each one. Skipping a cell will help space out the dates so you can type in the information for the event and add graphics.
7. Drag your cursor over the row of cells above the dates you entered to highlight them. Start at the first date and drag it over to the last date. Click the paint can under the 'Font' option and choose a color for your timeline.
8. Type short descriptions of the events that coincide with each date in the row below that date. You can change the size and font of the descriptions under 'Home' and then 'Font.'
9. Drag your cursor over the rows with the dates and descriptions to highlight them. Go to the 'Orientation' button under the alignment option to rotate the dates and descriptions. The button is a slanted arrow with the letters 'a' and 'b' over it. Choose one of the options shown or click 'Format Cell Alignment' to customize the angle. You can type in the angle you want or drag the line in the 'Orientation' box to the angle you want.
10. Go to 'Insert,' 'Illustrations' and 'Pictures' to add a graphic to your timeline. You can choose a file you have saved or go to 'Clip Art' instead of 'Pictures' and choose a Microsoft graphic. Once you have selected the file or clip art, click on it to insert it on your timeline worksheet.
11. Click on and manipulate the corner controls around the graphic to resize the image.
12. Click on the image and drag it to the location you want it on your timeline.
13. Save your timeline when you finish.

Monday, March 12, 2012

How to Use a Subroutine in Excel Visual Basic

1. Open the Visual Basic Editor. In Excel 2003, from the menu, select 'View,' 'Toolbars,' 'Visual Basic.' From the new toolbar, click the Visual Basic Editor icon. In Excel 2007, on the Developer tab, in the Code group, click 'Visual Basic.'
2. Insert a new module. From the menu, click 'Insert,' 'Module.' You will see the new module listed in the Project Explorer with the name 'Module1.'
3. Insert a new procedure. From the menu, click 'Insert,' 'Procedure.'
4. In the 'Add Procedure' pop-up box, type a name for your new subroutine. Use underscores, rather than spaces, to separate words.
5. Under 'Type,' select 'Sub.'
6. Under 'Scope,' choose 'Public' or 'Private.' A public subroutine is accessible to other procedures; a private subroutine only works within the same procedure.
7. If desired, check the 'All Local variables as Static' checkbox. Static variables retain their values when you exit a procedure. The default behavior clears all variables.
8. Click 'OK.' In the code window, you will see the Sub and End Sub statements.
9. Add code. Between 'Sub' and 'End Sub,' insert the VBA instructions for your subroutine. For instance, use the following code to display a message box with a greeting:Sub Test()
MsgBox 'Hello World'
End Sub
10. Run the subroutine. From the menu, select 'Run.' In Excel 2003, the subroutine will execute. In Excel 2007, select the subroutine in the pop-up box, and click 'Run' to start execution.

How to Use the Excel Monthly Household Budget Template

1.
Open Excel. If you are using Excel 2007, click the 'Office Button' and then click 'New.' In Excel 2003, click 'File' and then 'New.' Type 'Family Monthly Budget' in the 'Search Online For' box and click 'Go.'
2.
Click 'Family Monthly Budget Planner' to select that template. Click the 'Download' button. The template will open as a new Excel worksheet.
3.
Start in the 'Projected Cost' column. Type in what you expect the costs to be for each item listed that month. You can round off your costs to whole numbers, or put in exact numbers and the template will round them off for you. If you are uncertain how much you might spend on an item, look at past bills or receipts and make an estimate.
4.
Enter the information into the 'Total Actual Cost' column as the month goes along. Every time you spend money or pay a bill, enter the amount into the correct cell. If you already have a number in that cell, add the 2 numbers together and enter the new number into the cell. The template will automatically calculate the difference between your projected and actual spending.
5.
Enter your projected monthly income at the beginning of the month. As you earn money throughout the month, enter this data into the cells in 'Actual Monthly Income.' Your household budget template will help you see what you actually have coming in and where it is going.

How to Darken the Selection in Microsoft Excel

1. Select the column or columns of data with your mouse. If you want to select all of the columns, position your mouse on top of the first column letter (usually column A), click, and drag it across to the last column. This will select everything in your spreadsheet.
2. Click the 'Home' tab and select the 'Cell Styles' option. You will see a listing of various cell themes.
3. Choose one of the cell themes that are listed at 20 percent opacity from the list of cell style options. From now on, when you select the cells in your worksheet, the selection box will be darker and easier to distinguish. Leave this formatting on your worksheet until you are finished working with the file and ready to save the document.
4. Use the scroll bar at the bottom of your worksheet to scroll over until you find a column that has the default Excel background and text formatting. Select the column (as you did in step one) and double-click the 'Format Painter' button on the 'Home' tab. Then click and drag your mouse across the column letters in your spreadsheet--this will return your worksheet to the default formatting.

How to Merge Excel Spreadsheets to Find Duplicates

Complete the Merge
1. Identify the duplicate values you are searching for. Identify the data type and make sure it is the same in both spreadsheets. For example, if you would like to identify duplicate dates, make sure the dates are recorded in the same format (9/12/2034) across spreadsheets.
2. Confirm the spreadsheets have the same number of columns. To merge spreadsheets, make sure they have the same number of columns. If not, make sure the column headers match until it no longer matters. For example, if spreadsheet A has four columns and spreadsheet B has six columns, make sure the first four columns of both spreadsheets are First Name, Last Name, Phone and Email. Make sure there are no empty columns or rows.
3. Copy and paste or consolidate. Copy the information from spreadsheet A and paste the information into spreadsheet B below the last record on spreadsheet B. Each column in spreadsheet B should line up with the appropriate column in spreadsheet A.
Identify Duplicate Records
4. Insert a new column. Insert the new column next to the column with the duplicate value. For example, if you are looking for duplicate phone numbers in column C, insert a new column to the left of column C. Column C should be come before column D. Type the title 'Duplicate?' into the column's first row to name the column.
5. Enter the formula for identifying one column duplicates. Select the second row of your 'Duplicate?' column and enter the duplicate formula: =IF(D2=D3, 'Duplicate',''). This formula will identify duplicate records in column D only. For duplicates in multiple columns, enter =IF(D2E2=D3E3, 'Duplicate',''). This formula will identify duplicate records in columns D and E.
6. Copy and paste the formula. Copy the formula down the length of the appropriate column. Right-click the cell with the formula and select 'Copy' from the short-cut menu. Select each cell down the length of the appropriate column and right-click and select 'Paste Special...', then click 'Values' and 'OK.'
7. Sort the 'Duplicates?' column. Sort your spreadsheet based on the duplicate column. Sort the duplicate column in descending order to move each duplicate to the top of the list.

How to Open Excel 2007 Files With Office 2003

1. Visit Microsoft's online Download Center. Search for 'Microsoft Office Compatibility Pack for Word.'
2. Click on the 'Microsoft Office Compatibility Pack for Word, Excel and PowerPoint File Formats' link.
3. Click on the 'Download' link. Click on 'Save' to save the file to your computer.
4. Click on the downloaded FileFormatConverters.exe file. It should be located in the downloads file on your computer.
5. Click on 'Run' to start the installation process. Once the installation is complete, you will be able to open and edit files made in Excel 2007 in your Office 2003 suite of applications.

How to Enter a Series of Dates in an Excel 2007 Spreadsheet

1. Open the worksheet.
2. Click on a cell that will contain the first date. Enter this date. For example, 5/12/11.
3. Point on this selected cell's lower right corner. A black ' ' symbol will appear over the tiny square in this corner. This symbol is the fill handle.
4. Drag the fill handle across the adjacent cells to fill them with the sequence of dates.

Sunday, March 11, 2012

How to Use Excel to Subtract From the Total

1. Open the document you would like to make the changes to in Microsoft Excel.
2. Locate the cell that contains the total and make note of the cell number --- for example, 'E2' or 'F15.'
3. Select the empty cell in which you would like to have the subtracted result shown.
4. Type '=X - Y' where X is the number of the cell containing the total and Y is the number or cell you would to subtract from the total.
5. Press 'Enter' and the cell will now show the subtracted total.

How to Make the Cursor Stop Highlighting

1. Launch the Microsoft Excel project in which you wish to stop the cursor highlighting.
2. Select a single cell with your cursor. The cursor highlights a range of cells only if extended-selection mode is turned on.
3. Turn extended-selection mode off by pressing the 'F8' key.
4. Select a single cell with your cursor. The cursor will highlight only the individual cell. Extended-selection mode has been turned off.
5. Restart your system if the problem persists.

How to Combine Worksheets Inside an Excel Spreadsheet

1. In Excel 2007, click on the 'Data' tab and choose 'Consolidate' from the Data Tools group. For earlier versions of Excel (97-2003), click on 'Data->Consolidate.'
2. Define the reference ranges using the Reference box. Specify the first range you want to consolidate.
3. Click 'Add.' The reference you defined in Step 2 will appear in All References.
4. Repeat Steps 2 and 3 until you have defined everything you want to consolidate. Then click 'OK.'

Tuesday, February 28, 2012

How to Make a Map Using Excel

1. Create the information you want to appear on the map, such as statistics for a region. Enter the name of the place (such as city, state, or country) in column A and the number or statistic in column B.
2. Once you have your data for your map, click File and Save. Highlight and select all your data rows and columns by holding the left button down and dragging it until all your data is selected.
3. Go to the top of the screen and use your mouse to click Insert, followed by Map. Move your cursor (which will no longer flash now) and move it the part of the Excel page where you want your map to be.
4. Holding down the left mouse button, drag your cursor across the Excel page to show how large you want the map. You will need to start from the top left corner of your desired map area and drag it down to the bottom right.
5. Wait for Microsoft Excel to display a pop-up menu, listing the maps available. Read through the list, select the one you want, and click OK.
6. Excel will then find boundary names to match the data you entered (such as the region, city or state). If it cannot automatically find the information you are looking for, a pop-up menu labeled Resolve Unknown Geographic Data will appear with other name options; choose one and click Change or select Discard if you cannot find a match.
7. Microsoft Excel will then generate a map based on statistics and data you entered. Look it over to make sure all the statistics and names are correct; if not, go back to your saved data, fix it and re-save.

How to Remove Scroll Bars in Pages

1. Open the document or spreadsheet from which you would like to remove the scroll bars.
2.
Select 'Tools' from the toolbar menu. Next, choose 'Options,' then click on the 'View' tab.
3. Uncheck the boxes next to 'Horizontal Scroll Bar' and 'Vertical Scroll Bar.' Then click 'OK.' (These options are in the 'Show' area under the 'View' tab in Microsoft Word; they are located in the 'Window Options' area under the 'View' tab in Microsoft Excel.)
4. To show the scroll bars again, repeat steps 1 and 2. Next, place checks in the boxes next to 'Horizontal Scroll Bar' and 'Vertical Scroll Bar.' Click 'OK' when finished.

How to Calculate Percentages in Excel

1. Type the amount in a cell. For example, a student received a score of 48 out of 60 on a test and wants to find his percentage. The student would type 48 in cell A1.
2. Type the total in an adjacent cell. In the example, the student would type 60 in cell B1.
3. Type '=amount cell/total cell' in an adjacent cell to the total cell. Replace amount cell and total cell with the cell addresses of the amount and total. In our example, the student would type, '=A1/B1' in cell C1. The total will display 0.8 in cell C1.
4. Highlight the cell with the decimal, and click '%' button under the 'Home' tab to convert the decimal to a percentage.

How to Create Macros in Excel 2003

1. Open the Microsoft Excel 2003 application on your computer. Click on the 'Tools' option from the top toolbar menu and then click on the 'Options' button.
2. Click on the 'Security' tab and then click on the 'Marco Security' option. Click on the 'Security Level' tab and choose either the 'Medium' or 'Low' option.
3. Click on the 'Tools' option and then scroll over the 'Marco' option. Click on the 'Record New Macro' button.
4. Enter a name for your macro in the 'Macro name' box. The first character in the name must be a letter and not a number.
5. Choose where you want to store the macro on your computer from the 'Store macro in' box. Enter a description for your macro in the 'Description' box. Click on the 'OK' button.
6. Perform the task that you want for your macro within the Excel spreadsheet, and then click on the 'Stop Recording' button when you're finished. For example, you can select a cell, press the 'Enter' key and then select the 'yellow' highlight color from the toolbar. Now, every time you press the 'Enter' key in a cell it will be highlighted in yellow.

How to Merge Sheets in Excel 2007

1. Click the Windows 'Start' button. Type 'excel' in the search text box. Press 'Enter' to open the Excel software.
2. Press 'Ctrl' and 'O.' In the dialog window that opens, double-click the XLSX file that contains multiple sheets you want to combine.
3. Click the 'DigDB' menu item at the top, then click the 'Table' sub-menu item, then click 'Append Tables in this File.' A configuration window opens.
4. Click each sheet you want to combine, and click the 'Add' button. If you want to combine all sheets together, select 'All Sheets,' and then click 'Add.'
5. Click 'OK' to combine the spreadsheets. Notice the original, first spreadsheet has all the combined data. Press 'Ctrl' and 'S' to save the changes.

How to Convert Date to Day of Week in Excel

Convert Replace Date with Day of Week
1. Open the Excel workbook in which you want to convert and replace the date data with day-of-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format.
2. Left click with the mouse to select the column or row that contains the range of date data.
3. Select 'Format' from the menu.
4. Select 'Cells' from the drop-down list.
5. Select the 'Number' tab on the 'Format Cells' pop-up window.
6. Select 'Custom' in the 'Category' list.
7. Enter 'dddd' in the 'Type' field to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).Enter 'ddd' in the 'Type' field to display the date as day-of-week, with the day abbreviated to standard three-letter abbreviation (Mon, for instance).
Convert Date to Day of Week, Without Replacing Initial Data
8. Open the Excel workbook in which you want to convert and replace the date data with day-of-the-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format. This article will presume that the date data is located in column 'A,' with the header 'Date' in cell 'A1.'
9. Determine where you want the day-of-week data located. Label that column header with DOW (cell 'B1,' for example).
10. In 'B2,' enter the formula '=TEXT(A2,'dddd')' to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).
Or, you can enter the formula '=TEXT(A2,'ddd')' to display the date as day-of-week, with the day abbreviated to standard three-letter abbreviation (Mon, for instance).
Press 'Enter' to move the cell cursor out of cell 'B2'.
This is with the understanding that cell 'A1' contains the label 'Date' and cell 'B1' contains the label 'Day of Week' and that the first date is located in cell 'A2' and the first cell to be populated with the day of the week is cell 'B2'. If your workbook is set up differently, adjust the location of the formula to the desired location of the day-of-week data and adjust the formula accordingly to refer to the correct cell.
11. Return the cell cursor to cell 'B2'. Select 'Edit' from the menu.
Select 'Copy' from the drop-down list.
12. With the cell cursor, select the entire range where you want the day-of-week data. Select 'Edit' from the menu, and select 'Paste' from the drop-down list; this will paste the formula that you entered in cell 'B2' through the entire range of cells.

Subscribe to: Posts (Atom)