How to Create Target Lines in Excel Charts

1. Locate the chart data on your Excel worksheet. If you aren't sure which data was used for the chart, click once on the chart: the chart data will be bordered by a blue line on the worksheet.
2. Click on the first blank cell to the right of your chart data. If you don't have a blank column directly to the right of your chart data, create one: right click, then click 'Insert' and then click 'Entire column.'
3. Type your target. For example, if your graph consists of sales figures and your target sales goal is 20 units, type '20.'
4. Click the fill handle for the cell. The cell looks like a little black square at the bottom right of the cell. Drag it down the column to the last row in your chart data. This action copies the input for the cell (in this example, 20) to all cells in the column. This is the dummy series for your chart.
5. Highlight the entire chart data area, including the dummy series. To highlight the data, left click at the top left of the cells and then drag the cursor to the bottom right.
6. Click the 'Insert' tab. Select the chart type you want displayed. For example, click 'Column' and then click '2D.' Excel will insert the chart into the spreadsheet.
7. Click the dummy series on the chart, then click 'Change Chart Type.' Change the chart type to 'Line.'

Read more ►

How to Change the Footer in Excel 2007

1. Open the Excel document containing the footer that you would like to change. On the tab bar at the top of the page, locate and click the 'Insert' tab.
2. Click on the 'Header and Footer' button located in the text group on the right-hand side. Clicking this button displays the design tab. The header and footer tools located within this tab will allow you to create a new header or footer as well as edit the current header or footer.
3. Click the 'Go to Footer' button within the navigation group. Insert text by simply typing the text you want to appear as your footer within the footer text box. If you are changing an existing footer, delete the text or graphic that is already there, and replace it with whatever text or graphic you want to appear.
4. Select the desired cell where you want the footer to appear or where the current footer exists. Click and type text or choose pre-formatted data.
5. Click on the appropriate button within the header and footer elements group, such as page number, current date or file name, to use any of the pre-formatted data.
6. Navigate back to the body of your document and continue by clicking the 'Home' tab.

Read more ►

How to Autosave Microsoft Office Documents Every 60 Seconds

1. Open your Microsoft (Word, Excel, PPoint, Access Etc.) document.
2.
In Office 2007, e.g. Word, Click on the Office Button in the top left hand corner of your document.
3. Click on the Word Options button at the bottom of the Drop-Down Menu.
4.
In the left pane, Click on the Save option and under the 'Save Documents' heading ensure that the 'Save AutoRecovery Information every' Checkbox has a tick in it.
5.
In the dropdown list that is directly to the right, type 1 or use the arrow keys to decrease the number from 10 to 1. Click Ok to close the dialog box and you are done.
6. In Office XP-2003, (Word, Excel, PPoint, Access Etc.), Go to 'Tools' choose 'Options' from the menu and in the dialog box, Click on the 'Save' tab and you will see the same feature similar to Office 2007, make your changes and click ok to accept the changes and you are done.
Next time office crashes in the middle of your work AutoRecovery would have saved the last 60 seconds of your edited document(s) for you.

Read more ►

How to Make a Sorted List on Excel

1. Start Excel 2010 using the shortcut in the Microsoft Office folder, and open the spreadsheet containing data that you want to sort.
2. Click the button between the 'A' and '1' in the upper-left corner of the window to highlight all of the data in the spreadsheet. Alternatively, click and drag the mouse pointer across the letters at the top of the window to highlight only certain columns. If you do this, the data in the columns not highlighted remains in its current location after sorting.
3. Click the 'Home' tab at the top of the window.
4. Click the 'Sort Filter' button in the 'Editing' section of the top toolbar, and then click an option such as 'Sort A to Z,' 'Sort Smallest to Largest' or 'Sort Oldest to Newest' to sort the data automatically. The options available are context-sensitive and vary depending on the leftmost column selected. To sort by multiple columns, select 'Custom Sort' and continue.
5. Click the drop-down menu next to 'Sort by,' and select the first column that you want to use for sorting.
6. Click the drop-down menu under 'Order,' and select the sort order for the selected column such as 'Oldest to Newest.'
7. Click the 'Add Level' button to add an additional column to the sorting criteria, and then repeat steps 5 and 6. If multiple rows have matching information in the first selected column, Excel sorts them according to the second column selected. You can add as many columns to the sorting criteria as you like.
8. Click 'OK' to sort the data according to the criteria you selected.

Read more ►

How to Remove Old Items in a PivotTable Drop

1. Right-click on any cell in the PivotTable.
2. Click 'PivotTable Options' from the drop-down menu. A small window will appear.
3. Select the 'Data' tab at the top of the new window.
4. Click on the drop-down arrow under the 'Retain items deleted from the data source' areas. Choose 'None' from the list and click 'OK' at the bottom of the window.
5. Select the 'Options' tab at the top of the screen, directly underneath 'PivotTable Tools.'
6. Click 'Refresh' in the 'Data' area of the toolbar. Your PivotTable should briefly flash and all deleted entries will now be removed from drop-down boxes in the PivotTables.

Read more ►

How to Calculate Softball Standings With Excel

1. Launch Microsoft Excel 2010 to open a new spreadsheet. Click on cell 'A1' and type 'Team Name' into the cell. Click on cell 'A2' and enter the name of the first team. Continue to enter names down the first column until you have entered the name of every team.
2. Select cell 'B1' and type 'Wins' into this cell. Select cell 'B2' and enter the number of wins for the team listed in cell 'A2.' Repeat this process for every team.
3. Click on the cell at the top of the next empty column and type 'Winning Percentage' into this cell. Click on the next cell in this column and enter the following formula:=(B2 (D2*0.5))/SUM(B2:D2)If ties are worth a third of a win in your league, enter '0.3333' instead of '0.5.' If your league does not track ties, use the following formula instead:=B2/SUM(B2:B3)Press 'Enter' to complete your formula.
4. Move your mouse over the small fill-handle in the lower right corner of the cell that contains your formula. Click and drag the mouse down to the last row that contains team information and release the mouse button. Excel will automatically copy the formula down the entire column.
5. Click on cell 'A1.' Click the 'Insert' tab at the top of the screen, then click the 'Table' button in the Tables area of the ribbon. Excel will automatically select every cell where you entered data, so just click 'OK' in the Create Table window to continue.
6. Click the drop-down arrow next to the 'Winning Percentage' cell and choose 'Sort Largest to Smallest' from the menu. Your softball standings will now be sorted by winning percentage, placing the best teams at the top of the standings.

Read more ►

How to Disable One Macro in Excel 2003 Using the VBA

1. Open the Microsoft Excel 2003 software and access the worksheet containing the macro you need to disable. Navigate to the 'Tools' option at the top of the screen and click the 'Macro' option.
2. Select the 'Macros' entry and click 'This Workbook' to see a list of macros associated with the spreadsheet. Scroll through the list and click the name of the macro you need to disable.
3. Click 'Delete' to remove the macro from the worksheet. Close the 'Macros' window and return to the 'Tools' menu if you want to disable a macro by manually deleting the code.
4. Choose the 'Macro' option and select the 'Visual Basic Editor' entry. Click the name of the worksheet at the left end of the window that contains the macro.
5. Scroll down to the start of the macro, which begins with 'Sub MacroName().' Highlight the opening segment of the macro and scroll down to the ending 'End Sub' command. Press the 'Backspace' key to delete the macro.
6. Click 'File' and choose 'Save' to save the changes to the VBA code.

Read more ►

How to Use Excel to Solve a Polynomial

1. Input the coefficients into separate cells in row 4 in Excel, starting at cell 'B4.' for example, if you have a cubic equation x^3 - 2x^2 - 3x, then your coefficients are 1, -2, 6 and -3. Click on cell 'B4' and type '1', then click on cell 'C4' and type '-2,' then click on cell 'D4' and type '6,' then click on cell 'E4' and type '-3.'
2. Click on cell 'B6' and type a guess for the value of X. If you have no idea of what the value might be, just type '0.'
3. Click on cell 'G4' and then type the full equation into the cell. In this example, the equation would be 'x^3 - 2x^2 - 3x.'
4. Click on the 'Data' tab, then click on 'What-If Analysis,' in the Data Tools group and then click 'Goal Seek.'
5. Click on the 'Set cell' text box and type 'G4.'
6. Click on the 'To value' text box and then type '0.'
7. Click on the 'By changing cell' text box and then type '$B$6.'
8. Click on 'OK.' Excel will solve the polynomial and return the result in cell B6 (the initial guess box).

Read more ►

How to Consolidate Data in Microsoft Excel 2003

Consolidate Data by Position
1. Open the Excel worksheets that you want to consolidate. Check to make sure that there are no blank spaces within each range.
2. Open a new worksheet. Copy and paste the range from each worksheet that you wish to consolidate that you wish to consolidate into the new worksheet.
3. Select each range one at a time. Click “Named Cells” located on the toolbar. Click “Formulas.” Click “Name a Range.” A pop-up box will open. Type the range name into the “Name” field. Click “Save.” Create a name for each range.
4. Open the master worksheet where you plan to consolidate the data.
5. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
6. Click “Data Tools” on the master worksheet. Click “Data.” Click “Consolidate.” Click on the function you wish to use in the “Function” box which opens.
7. Click “Browse” if the worksheets you want to locate are in a different workbook. Choose the location from the drop-down menu and click “Okay.”
8. Type into the range dialog box the name that you gave the range of data that you wish to consolidate. Click “Add.” Do this step for all of the ranges that you wish to consolidate.
9. Click to check the “Create links to source data” box if you want the master worksheet to be updated automatically when you add data to the worksheets in a different workbook. This function only works if you are consolidating data from different workbooks. To update the data manually, uncheck this box. Do not fill out the boxes in the “Use Labels” categories.
10. Click “Save.”
Consolidate Data by Category
11. Open the Excel worksheets that you want to consolidate. Check to make sure that there are no blank spaces within each range and that each range is formatted with label titles located in the top row, left column, or both.
12. Open a new worksheet. Copy and paste the range from each worksheet that you wish to consolidate that you wish to consolidate into the new worksheet.
13. Select each range one at a time. Click “Named Cells” located on the toolbar. Click “Formulas.” Click “Name a Range.” A pop-up box will open. Type the range name into the “Name” field. Click “Save.” Create a name for each range.
14. Open the master worksheet where you plan to consolidate the data.
15. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
16. Click “Data Tools” on the master worksheet. Click “Data.” Click “Consolidate.” Click on the function you wish to use in the “Function” box which opens.
17. Click “Browse” if the worksheets you want to locate are in a different workbook. Choose the location from the drop-down menu and click “Okay.”
18. Type into the range dialog box the name that you gave the range of data that you wish to consolidate. Click “Add.” Do this step for all of the ranges that you wish to consolidate.
19. Click to check the “Create links to source data” box if you want the master worksheet to be updated automatically when you add data to the worksheets in a different workbook. This function only works if you are consolidating data from different workbooks. To update the data manually, uncheck this box.
20. Click the boxes in the “Use labels in” section. Click “Top row” if the category titles are located in the row of your data ranges, click “Left column “if the category titles are located in the left column, or choose both boxes if titles are located in both areas.
21. Click “Save.”
Consolidate Data by Formula
22. Open the Excel worksheets that you want to consolidate. Locate the reference codes of the cells you will be consolidating on the master worksheet.
23. Open the master worksheet where you plan to consolidate the data.
24. Type in the titles of the column and/or rows onto the master worksheet. You can also cut and paste the titles from the original worksheets.
25. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
26. Type in the formula, which contains the reference codes of the cells you are consolidating into the cell you chose. To consolidate formulas located in different areas on separate worksheets, type an equal sign followed by the word SUM. Next, within parentheses, type the name of the worksheet followed by an exclamation point. Next, type the location of the formula. Type all of the formulas you want to consolidate separating each formula with a comma. For example, = SUM (Inventory!J8, Stock!A3, Tax!B7). If the formulas are located in the same areas of different worksheets, create the using the worksheet names and the area. For example, SUM (Inventory:Stock:Tax!B7)
27. Click “Save.”

Read more ►

How to Create a Quiz in Excel

1. Open a blank spreadsheet in Excel. Use column A for the question, column B for the answer and column C to grade the answers.
2. Type your first question into cell A1. For the purposes of this demonstration the question is, 'If you mix red and blue together, what is the resulting color?'
3. Scroll or arrow over to cell C1 and type the following function: =IF(B1='', '', IF(B1='answer', 'Right', 'Wrong')). Replace 'answer' with the correct answer. Using the example, 'If you mix red and blue together, what is the resulting color?' you would replace 'answer' with 'purple' and as long as the person taking the quiz typed 'purple' in the corresponding B1 cell, the answer would show 'Right'. If someone gives a wrong answer, 'Wrong' shows up and if they put no answer, cell C1 remains blank.
4. Think of questions and answers to fill the quiz. Remember column A is for inputting questions, column B should be left blank with enough space to type answers and column C is where you input the formula.
5. Continue inputting questions into column A by typing your questions into subsequent cells.
6. Skip over to the C column and begin typing in the formula into the cells in the C column. Remember to change 'B1' from the original formula to the B cell number that corresponds to the question. For a question in cell A2 you will want the formula to read =IF(B2='', '', IF(B2='answer', 'Right', 'Wrong')), again replacing 'answer' with the correct answer.
7. Decide if you want to quiz to score the participant's answers. If so, simply input the following into the corresponding D cells: =IF(C1='Right', 1, 0). Remember to update the cell numbers to correspond with each question and answer.
8. Type the following formula at the bottom of the quiz if you have decided to have Excel calculate the score: =sum(D1:D10). This is the formula for a 10 question quiz--adjust accordingly. You can either write in the cell numbers, or after you have typed the opening parenthesis you can use your mouse pointer to click and drag to select the range of cells you want added together. If you chose to select the cells this way they should be highlighted with a blue box.
9. Hide the answers before you send the quiz out. Select column C and choose 'Format> Cells>Protection' and check the 'Hidden' selection box. Select 'OK' to close the window and apply the setting. Now you can send the quiz out knowing the answers are hidden.

Read more ►

How to Link an Excel Spreadsheet to a Visio Diagram

1. Open the Microsoft Excel spreadsheet that contains the data you would like to link to the Visio diagram.
2. Go to the 'Insert' tab and click on the 'Object' button.
3. Go to the 'Create from File' tab. Click on the 'Browse' button and navigate to the Visio file that you would like to link. Check off the 'Link to file' box. Click 'OK.'

Read more ►

How to Copy and Paste in Excel

1. Open an existing Excel file to edit or create a new one. When creating a new file, select 'New' from the 'File' menu. To edit an existing file, select 'Open' from the 'File' menu.
2. Enter data that you want to copy into a cell or identify a cell that you wish to copy that already has data in it. Select the text by highlighting the exact text you want to copy within the textbox at the top of the screen or by clicking the individual cell the text is in.
3. Copy the selected text or cell by selecting 'Copy' from the 'Edit' menu at the top of the screen. You can also use a shortcut key by holding the 'Ctrl' and 'C' keys on your keyboard to copy the data. A third option would be to right-click your mouse and select 'Copy' from the short-cut menu that pops up.
4. Select the cell you wish to paste the copied text into by clicking on it. Paste the data into the selected cell by choosing 'Paste' from the 'Edit' menu. To use a shortcut key you can hold the 'Ctrl' and 'V' keys down to paste the data more quickly or right-click the mouse again and select 'Paste' from the short-cut menu that pops up.
5. Continue editing, copying and pasting data until all the data is entered in your spreadsheet. Save your work by selecting 'Save' or 'Save As' from the 'File' menu.

Read more ►

How to Set a Fixed Height Width in Excel

1. Open the Microsoft Excel 2010 file with which you want to work.
2. Click and hold the mouse button on the row number or column letter that you want to adjust to a fixed height and width. Then drag the mouse to select the adjacent rows or columns. You can then press 'Ctrl' and click to select nonadjacent columns or rows. If you want to change the height and width of the cells for the entire spreadsheet, just click the box to the left of 'A' and just above '1.'
3. Click the 'Home' tab at the top of the screen. Locate the Cells area of the Ribbon and click on the 'Format' button found there.
4. Choose 'Row Height' from the menu, and a small window appears. Type the value for your desired row height and click 'OK.'
5. Click the 'Format' button again and choose 'Column Width' from the menu. Enter your desired column width into the small window that appears and click 'OK.' Your column width and row height are now set.

Read more ►

How to Hide Indefinite Errors in Excel

1. Open the Excel 2010 worksheet where you want to hide the error values. Click the top-left cell in the range of cells you want to work with, then hold 'Shift' and select the bottom-right cell. If you want to work with the entire worksheet, you can just click the box above the '1' on the end of the top row.
2. Select the 'Home' tab and click the 'Conditional Formatting' button at the top of the screen. Select 'New Rule' from the list that appears.
3. Click 'Format only cells the contain' from the list at the top of the New Formatting Rule window. Click on the drop-down box under 'Format only cells with' and choose 'Errors' from the list of options.
4. Click the 'Format' button and then select the 'Font' tab in the new window that appears. Select the drop-down box under the 'Color' heading and click the white box located at the top-left of the drop-down menu. Click 'OK' twice and the errors will disappear.
5. Click the 'File' tab at the top of the screen. Then click 'Options' on the left side of the screen.
6. Click 'Formulas' on the left side of the Options window. Locate the 'Error Checking' heading and click the box next to 'Enable background error checking' to remove the check mark. Click 'OK' and the small arrows will disappear from the cells with errors.

Read more ►

How to Search a Cell Range for a Matching Value in Excel VBA

1. Open the worksheet in which you want to find a match. Press the 'Alt' and 'F11' keys together. This opens the Visual Basic Editor (VBE).
2. Click 'Insert' and then click 'Module.' This inserts a blank module window.
3. Copy and paste the following code into the blank window:Sub Find_First()Dim FindString As StringDim Rng As RangeFindString = InputBox('Enter a Search value')If Trim(FindString)
'' ThenWith Sheets('Sheet1').Range('A1:Z256')Set Rng = .Find(What:=FindString, _After:=.Cells(.Cells.Count), _LookIn:=xlValues, _LookAt:=xlWhole, _SearchOrder:=xlByRows, _SearchDirection:=xlNext, _MatchCase:=False)If Not Rng Is Nothing ThenApplication.Goto Rng, TrueElseMsgBox 'Nothing found'End IfEnd WithEnd IfEnd Sub
4. Change the 'Range' in the code to the cell range in which you want to look for a match. For example, if you want to look in cells C1:C1000, type 'C1:C1000' in place of 'A1:Z256.'
5. Press 'F5,' then type the string of text or data you want to find into the text box. Excel will find the match and highlight the cell.

Read more ►