April 2012 - Excel

Browse » Home » Archives for April 2012

Saturday, April 28, 2012

How to Replace All in Excel VBA

1. Open the Excel workbook where you want to enter your VBA code. Press 'Alt' and 'F11' to open up the VBA console.
2. Double-click on the module where you want to enter your code. If there is no module you can right-click on a worksheet, move your mouse over 'Insert' and choose 'Module.' Then double-click on the module to bring it up.
3. Copy the following formula into the module:Sub AReplace()Dim sb As WorksheetFor Each sb In Worksheetssb.Cells.Replace What:='XXX', Replacement:='YYY', LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseNextEnd SubChange 'XXX' to the value you are searching for and 'YYY' to the value you want to replace it with. You can also change the 'MatchCase' value to 'True' if capitalization is important. This macro will find and replace all desired values on every worksheet in your workbook.
4. Use the following code if you only want to replace all the values in a given selection:Selection.Replace What:='XXX', Replacement:='YYY', LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseYou can use this code within any other VBA macro as long as you enter this code after you have defined a selection area in your macro.
5. Click on the 'X' in the top-right corner of the VBA window to close it. All your changes are automatically saved.
6. Click the 'Developer' tab and press the 'Macro' button. Choose your macro from the list and click 'Run' to replace all the targeted values in your worksheet or workbook.

How to Filter Duplicates in Excel

1. Click 'Start' then 'All Programs.' Open Excel 2010 by clicking 'Microsoft Office Excel 2010.'
2. Click 'File' then 'Open.' Select the Excel spreadsheet that contains the duplicate data you wish to filter.
3. Click the 'Data' tab then click the 'Remove Duplicate' button. A dialog box opens which lets you select the columns and rows that you want Excel to scan for duplicate data.
4. Click 'Select All' then click 'OK.'
5. Click 'OK' once more to confirm the success of the operation. The spreadsheet is now filtered of duplicate data.

How to Add (or Subtract) in a Spreadsheet like Excel

1. Open the Excel worksheet.
2. Enter the values in a row or column.
3. Click a cell to the right of the row of values or click a cell below the column of values. The selected cell displays a black outline.
4. Click the 'Home' tab.
5. Click the 'AutoSum' button in the 'Editing' group. This summation button displays the uppercase Sigma. A formula appears with the range of cells.
6. Press the 'Enter' key. The total appears in the selected cell.

Friday, April 27, 2012

How to Convert the Date to Text in Excel

1. Open in Excel the workbook that has the dates you want converted to text. If that file isn't available, populate date data into a single column (A2) in a new Excel workbook.
2. Label your date column (presumed to be column A for purposes of this instruction) as 'Date' in cell A1. Label the column where you want the converted date to populate as 'Date-Text.'
3. Enter the following formula in the second row of the 'Date-Text' column to convert the date to dd-mmm-yyyy format:=TEXT(A2,'dd-mmm-yyyy')Example:
Date: 1/10/2010
Date-Text: 10-Jan-2010Copy this formula down the column as necessary to convert all of the date data to text data.
4. Enter the following formula in the second row of the 'Date-Text' column to convert the date to dd-mm-yyyy format:=TEXT(A2,'dd-mm-yyyy')Example:
Date: 1/10/2010
Date-Text: 10-01-2010Copy this formula down the column as necessary to convert all of the date data to text data.
5. Enter the following formula in the second row of the 'Date-Text' column to convert the date to dd-mmm-yy format:=TEXT(A2,'dd-mmm-yy')Example:
Date: 1/10/2010
Date-Text: 10-Jan-10Copy this formula down the column as necessary to convert all of the date data to text data.
6. Enter the following formula in the second row of the 'Date-Text' column to convert the date to mmm-dd-yyyy format:=TEXT(A2,'mmm-dd-yyyy')Example:
Date: 1/10/2010
Date-Text: Jan-10-2010Copy this formula down the column as necessary to convert all of the date data to text data.
7. Enter the following formula in the second row of the 'Date-Text' column to convert the date to mmm-dd-yy format:=TEXT(A2,'mmm-dd-yy')Example:
Date: 1/10/2010
Date-Text: Jan-10-10Copy this formula down the column as necessary to convert all of the date data to text data.

How to Calculate Empty Cells Using Excel

1. Add the following numbers and blank cells in the first column of your Excel 2007 document. For example, add the numbers 2,3,leave blank,5,6,7,leave blank,9,0,2,3,4,leave blank,6,7, and 8 in A2, A3, A4, A5, A6, A7, A8, A9, A10, A11, A12, A13, A14 and A15. Here 'leave blank' means don't enter any value in A3, A7 and A13.
2. Click on the 'A17' cell for this example. This is the cell where you will calculate how many total blank cells are in this column. You can choose any cell to calculate total blank cells.
3. Click 'Insert Function' on the top left-hand side of the Excel spreadsheet. The 'Insert Function' window will pop open.
4. Click on the drop-down menu of 'Or Select a Category.'
5. Scroll down to the 'Select a Function' window. Choose 'Countblank.'
6. Click 'OK.' The 'Functions Arguments' window will open. Ensure that on 'Number 1' cell, A2:A15 is populated. If A2:A15 is not populated, enter A2:A15 manually. Click 'OK.'
7. The total blank cells for the row has been successfully calculated in the 'A17' cell. In this example, the calculated value of blank count is '3'. In other words, only three cells are blank on this column. You can use this method to accurately identify blank cells regardless of the size of the data.

Thursday, April 26, 2012

How to Build a Savings Interest Calculator in Excel

1. Open a new spreadsheet in Excel.
2. Label row 1 as follows: A1 is 'Date,' B1 is 'Balance,' C1 is 'Additional deposits' and D1 is 'Interest.' Format columns B, C and D as currency by selecting the columns and clicking on the dollar sign button in the 'Home' tab. (Users of Excel 2003, click the dollar symbol on the Formatting toolbar.)
3. Call your bank or look at your account online to find out when the bank adds interest payments to your account.
4. Fill in column A with interest payment dates, starting with the most recent date your account received an interest payment. If your bank adds interest payments monthly, use monthly dates (October 1, November 1, etc.); if it pays annually, add dates by year. For example, if your last interest payment was October 1, 2009, the next date will be October 1, 2010.
5. Type the current balance of your savings account into cell B2.
6. Fill in column C with additional deposits. If you deposit $500 into the account in the interval between interest payments, enter '500' in cells C2, C3, and down as far as you'd like. If you deposit irregularly, leave those cells blank but enter figures each time you make a deposit. You can combine these approaches, entering a minimum figure and adding to it if you make more deposits.
7. Enter the following calculation in cell D2, without the quotation marks: '=B2 C2 x*(B2 C2)' where x is the interest rate. The interest rate type--daily, monthly, annually or other--must match the intervals between the dates in column A (step 4). Remember to multiply the interest rate expressed as a percentage by .01. So a 1.5 percent interest rate calculated once a year gives you a multiplier of 0.015. In this case your formula would be '=B2 C2 .015*(B2 C2)'. If the same interest rate was applied monthly instead of annually, your formula would be '=B2 C2 (0.015/12)*(B2 C2)'.
8. Copy cell D2 and paste in cells D3, D4 and on down as far as you'd like.
9. Navigate to cell B3 and type (without the quotation marks) '=D2'. This will cause Excel to display the formula result--your account balance after the interest payment--in cell B3.
10. Copy cell B3 and paste in cells B4, B5 and on down as far as you'd like.
11. Save the spreadsheet and close it.

How to Make a Relative Frequency Histogram on Excel 2007

1. Create a column for your independent variables (your x-values). For example, if you are comparing the performance of salespersons, write 'Jim' in cell A2, 'John' in cell A3, 'Sue' in cell A4, 'Pat' in cell A5 and Joe in cell 'A6.'
2. Create a column with your dependent variables (your y-values). For the example given in Step 1, write 12 in cell B2,11 in cell B3,10 in cell B4, 9 in cell B5 and 4 in cell B6.
3. Sum the dependent variable column by clicking an empty cell at the bottom of the data and entering the summation formula. For the Step 1 example, you have information in cells B2 to B6, so the formula is =SUM(B2:B6).
4. Create a second set of y-values by calculating the percentage of sales for each item. Use a formula to calculate this for you; for the example given in the steps above, enter =B2/B7 in cell C2, =B3/B7 in cell C3, =B4/B7 in cell C4, and =B5/B7 in cell C5.
5. Click on the down arrow below 'Column' in the 'Insert' tab. Choose '2-D Column' from the menu.
6. Click on the column representing the total number of sales. Press the 'Delete' key. This action leaves you with the relative frequency distribution graph only.

How to Calculate the Time Difference Between Two Times in Excel 2003

1. Open Excel 2003, and select a workbook that contains columns with time information. Click 'File' on the menu bar, and select 'Open.' Browse your files, and select the workbook. Click the 'Open' button. The workbook opens.
2. Highlight the first column that contains time data. Right-click the highlighted data, and select 'Format Cells.' The Format Cells dialog box appears. Click the 'Number' tab. Select 'Time' from the 'Category' list. Select the first time format displayed. Click 'OK.'
3. Highlight the second column that will appear in the timed calculation. Press 'F4.' This repeats the previous timed number format.
4. In the third column, enter a time difference formula. The formula will subtract the first column from the second column. The formula will begin with an '=.' Click on the first cell in the first column and press the '-' key on your keyboard. Click the first cell in the second column and press 'Enter.'
5. Highlight the third column. Right-click the highlighted data, and select 'Format Cells.' The Format Cells dialog box appears. Click the 'Number' tab. Select 'Custom' from the 'Category' list. Select the time format that displays the following: '[h]:mm:ss.' Click 'OK.' Your difference column now displays the timed difference between the first and second column.

Wednesday, April 25, 2012

How to Use MegaStat in Excel 2007

1. Create a new spreadsheet in Excel 2007.
2. Open MegaStat.
3. Click the 'Add-Ins' menu option at the top-right of the screen, and you will see a new available option for 'MegaStat.' Click it to gain access to all MegaStat's functions.

Tuesday, April 24, 2012

How Do I Create a Color Drop Down Box in Excel?

Create Lookup List With Background Color
1. Go to a section of unused cells in the spreadsheet.
2. Type the choices that will appear in the drop-down list. Type one choice per cell and compose a list of choices with each selection under the previous one.
3. Highlight all cells in the list and then right-click on the highlighted block of cells.
4. Click 'Format Cells' from the context menu.
5. Click the 'Fill' tab and then click on the desired color for the background of the lookup list (the selected cells).
6. Click 'OK.' The background color of the cells is set.
Add the Lookup Cells As a Drop-down Box
7. Select the cell where the drop-down options will be placed.
8. Click 'Data' from the top navigation bar. Click 'Validation.'
9. Click the 'List' option from the 'Allow' drop-down list.
10. Click the 'Source' icon from the top navigation bar and drag the cursor over the lookup list. Alternatively, type the range of cells defining the lookup list, such as '(=$A$1:$A$4).'
11. Tick the box next to the 'In-Cell Dropdown' option and then click 'OK.' The lookup list now appears as a drop-down box in the selected cell.

How to Create Conditional Formats in Excel 2007

1. Open an existing Microsoft Excel document in which to create these conditional formats. Double click on the 'My computer' option on the main operating system desktop. Choose the location of the Excel file and double click on the file icon to open the document.
2. Ascertain which cells or worksheet the conditional formatting will apply to. Prior to opening the conditional formatting manager, choose particular cells, the entire worksheet or workbook to apply the conditional format.
3. Choose the 'Conditional Formatting' button from the home menu ribbon. Then select the 'Manage Rules' option from the conditional formatting menu.
4. Click on the 'New Rule' button from the conditional formatting rules manager dialogue box.
5. Select the type of rule to create. There are six types of rules that a user can create, in this case the 'Format All Cells Based on Their Values' type will be utilized.
6. Determine the scheme and value basis for the condition format rule. For instance, a user can make the color of each cell vary based on the number in the cell. The highest number gets a certain color while the lowest gets another color. All the numbers in between will be assigned a variation on color scheme based on the value relative to the highest and lowest number in the spreadsheet.
7. Apply the format style to the conditional format. There are four options to choose from. In this case, a '2-Color Scale' was used.
8. Save the rule and the spreadsheet once the conditional formatting rule is complete.

How to Make Mailing Labels in Excel 2007

1. Open the Excel 2007 worksheet containing the list you want to use to make mailing labels. If you do not yet have a list, open a blank worksheet. Enter a name for each column in the first row of the worksheet. Choose names that will be easy to understand in the merge, such as 'First Name,' 'Last Name' and 'Street Address.' Save and close the worksheet.
2. Start Word 2007. Go to the 'Mailings' tab and click 'Start Mail Merge.' Select 'Labels' from the drop-down list. The 'Label Options' dialog box opens.
3. Select the label settings for the label pages you are using. Pick the label brand name from the 'Label Vendor' drop-down list. Select the product number that is listed on the label package in the 'Product Number' list. Click 'Detail' to view the details for a label, such as the size and the number of labels per page. If the label you are using is not listed, click 'New Label' and enter the specifications for your label. Click 'OK' when you have made your selections.
4. Click 'Select Recipients' in the 'Start Mail Merge' section of the 'Mailings' tab. Click 'Use Existing List.' Navigate to the Excel 2007 worksheet containing the list you want to use to make mailing labels. In the 'Select Table' dialog box, click the sheet of the workbook that has the list you want to use. Click 'OK.'
5. Click 'Address Block' in the 'Write Insert Fields' group of the 'Mailings' tab. Click the 'Match Fields' button. Match the address block fields to the field names you used in your Excel worksheet. Click 'OK' to close the 'Match Fields' dialog box. Click 'OK' to close the 'Insert Address Block' dialog box.
6. Click 'Update Labels' in the 'Write Insert Fields' group of the 'Mailings' tab. Click 'Finish and Merge' in the 'Finish' group of the 'Mailings' tab. Select 'Edit Individual Documents' to create a new document with the merged mailing labels and print them later or click 'Print Documents' to print the labels immediately.

How to Put Excel Data Into a Pie Chart

1. Open a new worksheet in Microsoft Excel. Add headings in the first column down the left side of the page. For instance, if you are making a pie chart of my favorite things, you would write, 'Football,' in cell A1, 'Chocolate,' in cell A2, and 'Television,' in cell A3. Then, put the numbers in the 'B' column. So, using the above example, this would mean you'd put 50% in cell B1 next to 'Football,' a 30% in cell B2, next to 'Chocolate,' and a 20% in cell B3 next to 'Television.'
2. Highlight all of the cells in both columns, from A1 to B3. In Microsoft Excel 2007, simply click the 'Pie' button in the toolbar at the top of your screen. In earlier versions of Excel, and on the Mac 2008 version, click on the 'Insert' menu at the top of the screen and select the 'Chart...' option. A smaller window will pop-up called the 'Chart Wizard.'
3. Hit the 'Pie' button in Microsft Excel 2007, and a drop down menu will appear. Choose the type and style of pie chart you want, and that chart will then be inserted into your spreadsheet. This is the final step to make a pie chart in Excel 2007. If you are using earlier versions of Excel or Excel 2008 for Mac, proceed to the next steps.
4. Click on 'Pie' in the 'Chart Type' menu. To the right of the menu, you will see six different sub-types for how you want your chart to look. Click on the type of pie chart you want and then click and hold the button that says, 'Press and Hold to View Sample.' This will give you a preview of how your chart will look using the data you have entered. When you are happy with your chart, click 'Next' at the bottom of the window.
5. Clarify that the data range is correct in the pie chart. In the space titled, 'Data Range,' click on the gray button with red, blue, and black dots to the right of the blank space. This will shrink the Chart Wizard window. Use your mouse to highlight the area of data you want, including the headings and corresponding numbers. Click 'Next.'
6. Title your chart in the space on the left that says, 'Chart title.' When you are finished, click 'Next.' Finally, choose where you want your chart to be displayed. You can have it displayed directly in the document, or place it in a new spreadsheet. When you have made your choice, select, 'Finish,' and your pie chart will appear in the document.

Monday, April 23, 2012

How to Merge Excel Worksheets Into a Workbook

1. Open Microsoft Excel. Press and hold the 'Ctrl' key on your computer's keyboard and press the 'O' key to start the 'Open' window. Click on the first workbook you would like to open. Press and hold the 'Ctrl' key and click on the second workbook. Press the 'Open' button at the bottom right of the 'Open' window to open both workbooks.
2. Click the workbook on the bottom Windows toolbar that contains the sheet that you would like to merge. Right-click the sheet tab that you would like to move or copy. For example, right-click 'Sheet 1' tab at the bottom left of the Excel workspace to select the first sheet to merge. Click 'Move or Copy...' from the drop-down list.
3. Click the 'To Book' drop-down menu on the 'Move or Copy' window. Click on the workbook to which you want to move the worksheet. For example, if your other workbook is called 'Book1,' click on the 'Book1' workbook. Click the sheet name of where you want the sheet to appear in the new workbook from the 'Before sheet' menu. If you want your sheet to appear at the end of the workbook, click 'move to end' from the 'Before sheet' menu.
4. Check the 'Create a copy box' if you want to maintain the sheet in the current workbook. Otherwise, leave the box unchecked to move the sheet from one workbook to the other. Press the 'OK' button to move or copy the worksheet from one workbook to the other.

How to Delete Cells in MS Office 2003

1. Open an existing worksheet in Microsoft Excel.
2. Select the cell (or cells) you want to delete from your worksheet. To select an individual cell, simply click the cell. For a range of cells, click the first cell and drag your cursor down to the last cell. If it's a large range, click the first cell in the range, and press 'Shift' while you click the last cell. Press 'CTRL' while clicking individual cells that aren't adjacent.
3. Open the 'Edit' menu.
4. Click 'Delete.' As a shortcut, right-click the selection and click 'Delete.'
5. Make a selection in the 'Delete' dialog box. Select 'Shift Cells Left, Shift Cells Up, Entire Row or Entire Column.' This will determine what to do with the remaining cells after you delete.

How to Divide Cells in Excel

Divide the Numbers in a Cell
1. Click on a cell to enter your data.
2. Type an equal sign (=).
3. Type your first number. For this example, we will divide 15 by 5.
4. Press the forward slash key on your keyboard (/). This is the division sign in Excel.
5. Type in your next number, 5.
6. Press the 'Enter' key on your keyboard or click on the check mark on the tool bar. This displays the result of the calculation.
Divide Numbers in a Range of Cells
7. Type the number 15 into cell A1.
8. Type the number 5 into cell A2.
9. Click cell A3 and type an equal sign (=).
10. Click on cell A1. 'A1' should appear after the equal sign in A3.
11. Press the forward slash (/) key on your keyboard.
12. Click on cell A2. 'A2' should appear after the forward slash. Your formula should look like this: = A1/A2.
13. Press the 'Enter' key on your keyboard or click on the check mark button on the tool bar to perform the calculation. The answer 3 should appear in cell A3.
Divide Numbers Using the Quotient Function
14. Use the quotient function. You can divide numbers with the quotient function: QUOTIENT (Numerator, Denominator). The only difference between using this function and using regular division is that, if there are any remainders, the answer from the quotient function will not display it.
15. Type the number 15 into cell A1. This is your numerator.
16. Type the number 2 into cell A2. This is your denominator.
17. Click on A3, where the result will be displayed.
18. Click on the 'Paste function' button (fx) on the tool bar to open the list of functions. Select 'Quotient.' The quotient dialog box should appear.
19. Click on the 'Numerator' line. Click on cell A1.
20. Click on the 'Denominator' line in the dialog box. Click on cell A2.
21. Click on 'OK' to close the Quotient dialog box. The number 7 appears as the answer in cell A3. Notice that the answer does not include any remainders.

How to Calculate a Percentile Using Excel

1. Enter your dataset in a continuous column in Excel. For this example, assume that the data is entered in cells A1 to A100.
2. Determine what percentile you want to calculate, and convert it to a number between zero and one. For example, the 20th percentile is 0.2, the 55th percentile is 0.55, the 100th percentile is 1, and so on. This example will use the 40th percentile (0.4).
3. Enter the following formula in the cell where you want the percentile result to show:=PERCENTILE(A1:A100,0.4).This tells Excel to calculate the 40th percentile of the data in cells A1 to A100.

How to Add a Shadow to a Chart in Excel

1. Start Microsoft Excel and open an existing workbook from your files that contains a chart to which you would like to add a shadow.
2. Select the chart you would like to add a shadow to by clicking on it with your mouse. The chart will be surrounded by a light blue outline when it is selected.
3. Choose the 'Format' tab at the top of the Excel screen to show the formatting options that you can apply to your selected chart. Locate the 'Shape Styles' section of the 'Format' ribbon.
4. Click the small half box and arrow button that lies on the 'Shape Styles' title bar on the lower right corner. This will open the 'Format Chart Area' dialog box.
5. Select 'Shadow' from the left side of the 'Format Chart Area' dialog box to display the shadow settings you can add to the selected chart.
6. Use the 'Preset' drop-down menu to choose a preset shadow from Excel. You can change the color of the shadow by selecting a new color from the 'Color' drop-down menu. The settings for the shadow can be changed using the 'Transparency,' 'Size,' 'Blur,' 'Angle' and 'Distance' sliders.
7. Click the 'Close' button to close the 'Format Chart Area' dialog box and return to your chart, which is now highlighted with a shadow.

Sunday, April 22, 2012

How to Make a Pictograph on Excel

1. Launch Excel 2010 and open a spreadsheet containing data that can be expressed in a graph. Highlight the cells that you want Excel to include in the graph.
2. Select the 'Insert' tab at the top of the window. Click 'Column' or 'Bar' in the 'Charts' section of the toolbar to generate a graph from the selected data.
3. Double-click one of the graph's bars. This opens a Format Data Point window.
4. Select the 'Fill' heading on the left side of the Format Data Point window.
5. Click to select the 'Picture or Texture Fill' radio button.
6. Click the 'File' button under 'Insert from' and double-click the image you want to use for the selected bar.
7. Click the 'Stack' radio button to display several stacked copies of the selected image in place of the bar, or click the 'Stretch' radio button to display a single image stretched to fill the bar.
8. Click 'OK' and repeat Steps 3 through 7 for the remaining bars of the graph.

How to Set Printing Options in Excel

1. Start Microsoft Excel and open a spreadsheet that you would like to print.
2. Choose the 'File' button and click 'Print' to bring up the 'Print' dialog box.
3. Change the printer you are using by clicking the 'Name' list under the 'Printer' section of the 'Print' dialog box. You can view the printers you have installed and click on which printer you would like to use to print your spreadsheet.
4. Check the 'Print to file' check box in the 'Printer' section of the 'Print' dialog box to print the spreadsheet to another computer file instead of from a printer.
5. Set the print range of your spreadsheet by selecting the 'All' radio button to print all of the pages or 'Page(s)' radio button to print only a certain page or multiple pages in your spreadsheet. If you choose the 'Page(s)' option you will need to use your keyboard to specify which pages you would like to print.
6. Select what you would like to print by choosing either 'Selection,' 'Active sheet(s),' 'Entire workbook' or 'List' under the 'Print what' section of the 'Print' dialog box.
7. Set the number of copies that you want to print by using your keyboard to type in the number beside 'Number of copies' under the 'Copies' section of the dialog box. You can also choose to collate the spreadsheet by putting a checkmark in the 'Collate' check box in this same section.

How to Use Excel 2003 Formulas Functions for Budgets

1. Decide which formula you want to use. For example, you may want to add a column of data or multiply certain budgetary numbers by 12 to convert from a monthly to yearly budget.
2. Locate the formula you need in the 'Microsoft: Excel Commonly Used Formulas' (see Resources). For example, click on 'Add Numbers' in the Math subsection. One of the functions you can use is the 'Sum' function, which adds numbers in a single cell or multiple cells.
3. Enter the formula into the cell where you would like the answer to appear. For example, you may want to add a list of budget items in cells B2 to B10 and display the result in cell B11. All formulas begin with an equals sign ('='), so type the following text into cell B11:=SUM(B2:B10)The colon means 'to' as in 'Cells B2 to B10 inclusive.'
4. Use a function instead of a formula to perform calculations. For example, you can use the autosum function in Excel instead of specifying a formula. To use the autosum function, click on the cell where you would like the total to appear (in the above example, that was cell B10). Then click the autosum symbol on the standard toolbar. The autosum symbol is an uppercase sigma. Click on the cells you would like to sum (for example, B2 through B10). The autosum function will place your result in cell B10.

How to Scroll With a Mouse Wheel in Excel 2007

1. Open Microsoft Excel 2007 and launch the document you need to view.
2. Click inside the document somewhere (or on the scrollbar at the side of the page). This tells the mouse you are working inside the loaded Excel document.
3. Drag your finger from top to bottom over the scroll wheel on the mouse. This moves the page down and you can see the scrollbar on the side of the screen move with your movements.
4. Drag your finger from the bottom of the scroll wheel to the top. Doing so scrolls the page back up toward the top of the document.

Saturday, April 21, 2012

How to Make Vertical Words in Excel

1. Open your spreadsheet in Microsoft Excel.
2. Click the cell containing the text you wish to make vertical.
3. Click the 'ab' symbol in the Alignment group of the Home tab and select 'Vertical Text' to align letters on top of one another. Alternatively, select 'Formal Cell Alignment,' enter '-90' or '90' in the 'Degrees' field, and click 'OK' to rotate normal text 90 degrees. Selecting '-90' will align the bottom of the characters to the left, while selecting '90' will align them to the right.

How to Use Excel 2007 to Make a Climate Graph With Negative Temperatures

1. Open the Worksheet.
2. Enter a date or time in each cell along one column or one row. For example, the dates April 1, April 2 and April 3 can fill cells B4, B5 and B6, respectively.
3. Enter the temperature values, one value per cell, in this temperature column. The values can be positive or negative. Enter these values in cells adjacent to the categories in Step 2. For example, enter '7' '-2'and '4' in cells C4, C5 and C6, respectively.
4. Click the cells that contain the dates and temperature values.
5. Click on the 'Insert' tab on the Ribbon. The 'Charts' group includes the 'Line' chart button.
6. Click on the 'Line' button to display the different line charts available. The '2-D' line chart displays values over time and different categories, such as dates.
7. Click on the 2-D chart button to convert the data. The chart displays both positive and negative temperature values. Try the other line chart styles to see the effect.
8. Edit the chart for a custom look. For example, the 'Design' tab includes 'Chart Layouts' and 'Chart Styles' groups. The 'Format' tab includes the 'Shape Styles' group that changes the color of the outline and background.
9. Save this worksheet.

How to Remove Password Protection in Excel

1. Open Excel and open the workbook for which you wish to remove the password protection. At this point, if you are using Excel 2003 or an earlier version, proceed to Step 2. If you are using Excel 2007, skip straight to Step 5.
2. Click on the 'File' menu, and then select 'Save As.' Click on the 'Tools' menu and select 'General Options.'
3. Double-click on the asterisk that is in the 'Password to open' box, and then press 'DELETE' on your keyboard. DO the same for the box labeled 'Password to modify.'
4. Click on 'OK,' then 'Save,' and finally 'Yes' to complete the password protection removal if your are using Excel 2003 or earlier.
5. Click on the 'Review' tab at the top of the Excel 2007 window, and locate the group of icons within the 'Changes' group. Click on 'Unprotect Sheet.' Type in the appropriate password when and if you are prompted, and you are finished.

How to Get Data From Excel Into Word

1. Click Excel's Office button, then click the 'Open' command. Navigate to and double-click a file you'd like to open to Word. Click the 'Save as' command after the file loads, then click the 'Excel 97-2003' format from the 'type' dropdown list. This format lets Word read the Excel data. Click the Office button's 'Close' command to close the workbook.
2. Click Word's Office button, then click the 'Open' command. Click the 'Excel' type from the 'Type' dropdown list, then navigate to the folder in which you saved the Excel workbook in the previous step.
3. Double-click the workbook to begin loading it. Word will display a warning message indicating the possible presence of malicious content. Click 'Yes' to continue opening the workbook.
4. Click the 'Entire' option from the dropdown control labeled 'Open document,' if you want to load the entire Excel workbook into Word. Otherwise, click one of the sheets listed in the dropdown to select just that sheet for importing to Word. For example, if your Excel data is only on 'Sheet1,' click that sheet in the 'Open document' dropdown.
5. Click the 'Name range' dropdown list if you chose to import only a particular spreadsheet in the previous step. Skip this step if you're importing the entire workbook. Otherwise, click a named range of cells in the workbook to import just that named range. For example, if the workbook contains a blocked of cells named 'ArtPatrons,' you can click that name in the 'Name range' dropdown to import just that block of cells.
6. Click 'OK' to import the Excel data into Word. Word will format the imported data as a table in a new Word document.

How to Change the Toolbar in Excel 2007

1. Locate the Quick Access Toolbar in Excel 2007. It will be either next to the Office button or under the ribbon.
2. Right-click on the Quick Access Toolbar and select 'Customize Quick Access Toolbar.' Check or uncheck the 'Show Quick Access Toolbar Below the Ribbon' box to place the toolbar where you want it.
3. In the ribbon, navigate to the feature you want to add to the Quick Access Toolbar.
4. Right-click on the feature you wish to add, and a menu will appear.
5. Select the 'Add to Quick Access Toolbar' option. The feature should now be on the toolbar.

How to Make a Time Line in Microsoft Excel

1. Gather all the information you need for the time line.
2. Open Microsoft Excel. Click on the Excel desktop icon or find Excel in the Start Menu under 'All Programs.'
3. Click the 'File' tab and pick 'New' to start a new spreadsheet.
4. Add a title to your time line. Click 'Insert' and then choose 'Header and Footer.' Type the title in the header section. Format the title to suit your needs and click 'OK.'
5. Move a few rows down the spreadsheet and input the first date. You can format the cells all at once or individually. If you are including times in your chart you may want to format the cells separately.
6. Format the descriptions so they appear vertically instead of horizontally in your time line. Highlight the cell or groups of cells you want to change. Click the 'Format' tab. Choose 'Cells.' When the format window opens pick 'Alignment.' Change the number in the 'Degrees' box. The diagram on the side displays the look of the angle you chose.
7. Create borders around the cells or the entire time line. Highlight the cells. Select 'Format' from the menu bar. Now pick 'Borders' and choose the type of border to use from the list provided.
8. Add a background color to specific cells. Select the cells you want to add color to. Choose 'Fill Color' or select the paint brush from the draw tool bar at the bottom of the screen. Pick a color and click 'OK.'
9. Insert pictures into the time line. Choose 'Insert' from the menu bar and then click on 'Picture.' Choose a file from your computer or a clip art from Excel's clip art gallery.
10. Save the time line. Select 'File' from the menu bar and then click 'Save' from the drop-down menu.
11. Print your time line. Click 'File' on the menu bar. Pick 'Print' from the drop-down menu and click 'OK.'

Friday, April 20, 2012

How to Manipulate Data in an Excel 2007 Pivot Table

1. Open Excel 2007 and select a workbook containing data. Click the 'Office' button and select 'Open.' Browse your files and select the workbook. Click the 'Open' button to open the workbook.
2. Highlight the data you want included in the PivotTable. Select 'Insert' on the menu bar, and then select 'PivotTable.' A drop-down menu appears. Select 'PivotTable' again. Click 'OK.'
3. Add fields to the PivotTable by checking fields from the right 'PivotTable Field List.' By default, the fields appear in the column section. Manually drag the fields to any of these sections (row, totals or filter) to see how manipulating them changes your pivot table.

How to Convert Excel Time Difference to Decimal Number

1. Click a cell where you want the decimal representation of the time difference to appear.
2. Type '= A1 * 24' and then press 'Enter.'
3. Change the cell location 'A1' to the actual location where your time difference appears. For example, if the time difference is 1:15 and appears in cell C3, type 'C3' into the formula instead of 'A1.' Pressing 'Enter' for this result will give you the decimal result '1.25.'

How Do I Graph on a Secondary Axis in Excel Office Professional 2003?

1. Click on the data series on the chart that you want to plot on a secondary axis. For example, if you have a chart that shows price and volume and you want to display volume on a secondary axis, then click on 'volume.'
2. Click on 'Selected Data Series' on the Format menu.
3. Click on the 'Axis' tab, then click on 'Secondary axis.' Excel inserts a secondary axis into your worksheet.

How to Create a Pivot Table from Multiple Worksheets

1. Open the workbook in which you want to create the PivotTable.
2. Click 'PivotTable and PivotChart Report' on the 'Data' menu.
3. Follow the instructions in the wizard. For the first step, you will be asked to identify the source for your data. Click the black box on the right-hand side of the source data box. This will disconnect the source box from the wizard.
4. Select the data ranges in the worksheets you need data from. Start with the first range and then move on to the next. The wizard will automatically put a comma between each range of data you select.
5. Click the black box on the right-hand side of the source box again to attach the box back to the wizard. Click 'Next' in the wizard until you reach the end and then click 'Finish' to exit the wizard and create the PivotTable.

Wednesday, April 18, 2012

How to Calculate Days by Subtracting Two Dates in Excel

1. Launch Microsoft Excel and open a new worksheet. Type the date in one cell. For example, type '1/1/2010' in cell A1.
2. Type your first date in cell A1. For example, input the date '1/1/2010.'
3. Type your second date in cell B1. For example, input the date '3/14/2011.'
4. Select the cell C1, type '=' and click the date in cell B1. Then type '-', click the first date and press 'Enter.' Cell C1 now shows 438, the number of days between the two dates you input.

How to Create an Application in Word or Excel

Create an Application in Word
1.
Start Microsoft Word. In Word 2003, go to the 'File' menu and click 'New.' In Word 2007, click the 'Office Button' and click 'New.'
2.
Type 'Employment Application' in the 'Templates on Office Online' box and click the 'Search' button. Select 'Employment Application 2-pp Online Form' in the 'Search Results' list. Click the 'Download' button, and an application will open as a new Word document.
3.
Enter your company logo by selecting 'Your Logo Here.' Go to the 'Insert' menu in Word 2003, point to 'Picture' and click 'From File.' In Word 2007, go to the 'Insert' tab and click on 'Picture.' Find the logo on your computer and insert it. Type the name of your business over 'Company Name.'
4.
Change any of the template example fields by selecting them and typing over the text. To alter the properties of a form field, double-click the gray field area, and the 'Form Field Options' dialog box will open. Select an option from a drop-down menu, or click the 'Add Help Text' button for more options.
5.
Save the application when finished. Before allowing an applicant to fill in an application in Word, open the document and save it with a new name, such as the applicant's name or the date, so that the original will not be changed.
Create an Application in Excel
6.
Start Microsoft Excel. Open a new, blank workbook. Insert a company logo by going to the 'Insert' menu in Excel 2003. Point to 'Picture' and click 'From File.' In Excel 2007, go to the 'Insert' tab and click on 'Picture.' Find the logo on your computer and insert it on the left side of the page. Enter a text box on the right side and type the name of your business inside it.
7.
Type the desired questions into cells on the spreadsheet. Leave empty the adjoining cells for the applicants to type into.
8.
Create 'Yes' or 'No' drop-down lists from which applicants can select an answer. Go to the 'Data' tab in Excel 2007 or the 'Data' menu in Excel 2003 and select 'Data Validation.' Go to the 'Settings' tab of the Data Validation dialog box.
9.
Select 'List' in the 'Allow' drop-down list. In the 'Source' field, type 'Yes,No' (without quotes). Click 'OK' to apply the drop-down list.
10.
Enter other data into the 'Source' field of the Data Validation box to create custom lists. Type a comma between each list item. When applicants click in the cell, they will be presented with a list of options from which to choose. Save the application when complete.

How to Change Macro Security Settings on Windows Vista

Changing Macro Security in Word or Excel 2007
1. Click the circular Office logo in the upper-left corner of the Word or Excel window, then click the 'Word Options' or 'Excel Options' button at the bottom of the menu.
2. Click the 'Trust Center' link on the left side of the screen, then click the 'Trust Center Settings' button.
3. Click the 'Macro Settings' link on the left side of the window to access the macro settings for the Office 2007 program you are using. The menus for Word 2007 and Excel 2007 are the same. The default selection is 'Disable all macros with notification.' To run your own macros, select 'Enable all macros.'
4. Click 'OK' to save your changes.

How to Keep Track of Changes in an Excel Document

Tracking Changes
1. Start Microsoft Excel and open the file you want to change.
2. Open the Tools menu and select Track Changes, then Highlight Changes.
3. In the Highlight Changes dialog box, select 'Track changes while editing.'
4. Select 'Highlight changes on screen.'
5. Open the When menu and select All.
6. Open the Who menu and select Everyone.
7. Click OK.
8. Click OK. This will save your changes and your file/workbook.
9. Enter your new changes.
Accepting or Rejecting Changes
10. Open the Tools menu and Track Changes menu and select Accept and Reject Changes option.
11. In the Select Changes to Accept or Reject dialog box, select 'Not yet reviewed' to see all changes or 'Since date' to see changes after a certain day.
12. Click OK.
13. In the Accept or Reject Changes dialog box, review the edits to the spreadsheet.
14. Select the Reject or Accept button for each edit.

Tuesday, April 17, 2012

How to Unfreeze a Window Pane in Excel 2007

1. Select the 'View' tab from the top menu in Microsoft Excel 2007.
2. Go to the 'Window' menu ribbon. Select 'Freeze Panes.'
3. Choose 'Unfreeze Panes' from the pop-up menu to unfreeze your Excel spreadsheet.

Monday, April 16, 2012

How to Install Office 2000 on Excel 2007

Installing Office 2000 without Excel 2000
1. Insert your Office 2000 installation CD. A dialog box should open.
2. Provide details about your name and organization as well as the 25-digit CD key to continue the installation process, then select the 'Next' button. You will be redirected to another dialog box containing the Office 2000 End-User License Agreement; read it. If you agree, select the 'I accept' option, then click 'Next' to continue with the installation process.
3. Select 'Customize' as your installation type, then select all Office 2000 component programs except Excel 2000.
4. Click on the 'Install now' button to finalize the installation process.
Installing Excel 2007 without Office 2007 component programs
5. Insert your Office 2007 CD. A dialog box will open, asking you to enter your Office 2007 key.
6. Enter your Office 2007 key, then select 'Continue.' You will be redirected to another dialog box, where you will accept the Microsoft Software Terms; accept the terms to continue.You will see another dialog box asking you to choose your installation type.
7. Install only Excel 2007 by selecting the 'Customize' option. Click the 'Installation options' tab, then choose the 'Not available' option for all of the component programs except Excel 2007. Click the 'Install now' button.

How to Make a Cell Required in Excel 2007

Making Cells Required to Print Spreadsheet
1. Open Excel 2007. Check for the 'Developer' tab in the ribbon across the top of the screen. If you do not have the 'Developer' tab activated, click the round blue 'Microsoft Office' button in the top left corner. Click 'Excel Options' followed by 'Popular.' Check the box for 'Show Developer Tab in the Ribbon,' and then click 'OK.'
2. Click the 'Developer' tab, and then click the 'View Code' button under the 'Developer' menu. This will open Microsoft Visual Basic.
3. Copy and paste the following code into the blank window:[vba]Private Sub Workbook_BeforePrint(Cancel As Boolean)If Sheet1.Range('A1:B2').Value = '' ThenMsgBox 'Cannot print until required cells have been completed!'Cancel = TrueEnd IfEnd Sub[/vba]
4. Replace the 'Sheet1' and 'A1:B2' values within the code with the range of values you would like to require in your spreadsheet. For example, if you want to require the first 10 cells in column A of Sheet 2 of your spreadsheet, you would change the second line of the code to:If Sheet2.Range('A1:A10').Value = '' Then
5. Close Microsoft Visual Basic. Save your Excel file to make the code a permanent part of the file.
Making Cells Required to Save Spreadsheet
6. Open Excel 2007 and check to see if the 'Developer' tab is present in the ribbon across the top of the screen. If you don't see the 'Developer' tab, click the round blue 'Microsoft Office' button in the top left corner. Click 'Excel Options' followed by 'Popular.' Check the box for 'Show Developer Tab in the Ribbon,' and then click 'OK.'
7. Click the 'Developer' tab, and then click the 'View Code' button under the 'Developer' menu to launch Microsoft Visual Basic. Visual Basic allows you to view and organize any code you've added to a spreadsheet, as well as write or add new code.
8. Copy and paste the following code into the empty window within Microsoft Visual Basic:[vba]Private Sub Workbook_BeforeSave(Cancel As Boolean)If Sheet1.Range('A1:B2').Value = '' ThenMsgBox 'Cannot save until required cells have been completed!'Cancel = TrueEnd IfEnd Sub[/vba]
9. Customize the code by replacing the 'Sheet1' and 'A1:B2' values with the range of values you would like to require in your spreadsheet. For example, if you want to require the first 10 cells in column A of Sheet 2 of your spreadsheet, you would change the second line of the code to:If Sheet2.Range('A1:A10').Value = '' Then
10. Attempt to save the Excel sheet without filling in all of the required cells. If a message box pops up reading 'Cannot save until required cells have been completed!', your code is working correctly.

How to Build Pivot Tables in Excel 2007

1. Open Excel and open a spreadsheet on your computer that has data in at least 5 columns and 10 rows. Make sure this data has column headers.
2. Press 'Control' and 'A' to select all of the data in your spreadsheet. Click on the 'Insert' tab on the ribbon and select 'PivotTable.' Select 'PivotTable' again. Excel will open the Create PivotTable dialog box. In the section titled 'Choose the data that you want to analyze,' choose 'Select a Table or Range.' In the section titled 'Choose were you want the PivotTable report to be placed,' choose 'New Worksheet.' Click 'OK.' A new worksheet opens displaying an empty PivotTable.
3. Design your PivotTable by selecting a column header from the 'PivotTable Field List.' The column data will populate the PivotTable. Notice the field is dropped into the 'Row Labels' area of your PivotTable. You can move it around using the section titled 'Drag fields between areas below.' Add another field to the PivotTable by selecting it from the 'Pivot Table Field List.' Drag both fields to either the 'Column Labels' or 'Row Labels' and see how your PivotTable is impacted.
4. Add a field that includes a numerical value for these two column headers. Move this field to the 'Sum Values' field of your PivotTable. You should see data that is being summarized in a compact manner. You can display the summarized data as a count or sum value. If you want to change the type of value being displayed, click on the drop-down arrow next to the field in the 'Sum Values' field and select 'Value Field Settings.' Change the value to represent the type of summary you are looking for. Click 'OK.'

Sunday, April 15, 2012

How to Use Page Orientation on Excel 2007

Selecting Page Orientation
1. Open Microsoft Excel 2007.
2. Click the 'Page layout tab.'
3. Click 'Page setup.'
4. Click 'Orientation' and click either 'Landscape' or 'Portrait.'

How to Merge Cells in a Shared Workbook

1. Navigate to the location of the shared workbook on your computer.
2. Double-click on the workbook to open it.
3. Click the top left cell that is going to be part of your cell merge, then drag the mouse pointer until all of the desired cells have been selected.
4. Right-click anywhere in the highlighted area of cells, then select the 'Format Cells' option.
5. Click the 'Alignment' tab, then check the box next to 'Merge cells.'
6. Click the gray 'OK' button to close the window.

How to Write Macros in Excel 2007

1. Make the 'Developer' tab of the ribbon visible. Click the 'Office Button' and select 'Excel Options.' Select 'Show Developer Tab in the Ribbon' in the 'Popular' category. Click 'OK.'
2. Enable macros. Go to the 'Developer' tab. Click 'Macro Security' in the 'Code' group. Select 'Enable All Macros' and click 'OK.'
3. Click 'Visual Basic' in the 'Code' group. The 'Microsoft Visual Basic for Applications' window opens. Go to the 'Insert' menu and select 'Module' to open a new module.
4. Type or paste the macro that you want to use in Excel 2007. Press the 'F5' key to run the macro and ensure that it works correctly.
5. Go to the 'File' menu and select 'Close and Return to Microsoft Excel' when you are finished.

How to Unlock an Excel Password

1. Download an Excel macro designed to unlock passwords.
2. Double-click 'allinternalpasswords.xls' to open the macro. The workbook itself is hidden and the macro displays a toolbar.
3. Click the 'File' menu and click 'Open ...' to open the workbook that you wish to unlock.
4. Click the button on the toolbar to unlock the password. The macro provides you with a password that will work in the locked workbook.
5. Enter the provided password in order to unlock the workbook.

Saturday, April 14, 2012

How to Calculate Correlations Between Three Sets of Data Using Excel

1. Open your Excel file, click the 'File' tab, then click 'Options.'
2. Click 'Add-Ins' at the left side of the window.
3. Click the drop-down menu to the right of 'Manage,' click 'Excel Add-Ins,' then click 'Go.'
4. Check the box to the left of 'Analysis Toolpak,' then click 'OK.'
5. Click the 'Data' tab at the top of the window, then click 'Data Analysis' in the Data Analysis section of the ribbon at the top of the window.
6. Click 'Correlation' and 'OK.' This opens a new Correlation window.
7. Check the 'Columns' option if your data is sorted by columns, or check the 'Rows' option if your data is sorted by rows.
8. Hold down the 'Ctrl' key and use your mouse to highlight each set of data for which you want to calculate correlations.
9. Click the 'OK' button to calculate your correlation and have it displayed on a new worksheet in your workbook.

Friday, April 13, 2012

How to Remove Dashes From Social Security Numbers in Excel

1. Open the Excel worksheet containing the Social Security numbers or other data from which you want to remove the dashes. Select the cells containing the numbers. If you want to remove the dashes from the entire worksheet, click the 'Select All' button, which is the rectangle between 'A' and '1' in the upper left of the worksheet. To select an entire row or column, click on the number or letter pertaining to that row or column. To select several cells in the worksheet, press and hold the 'Ctrl' key while you click on each cell.
2. Click the 'Format' drop-down in the 'Cells' group of the 'Home' tab in Excel 2007 and select 'Format Cells.' In Excel 2003 or earlier, go to the 'Format' menu and click 'Cells.'
3. Go to the 'Number' tab of the 'Format Cells' dialog box. Select 'Number' in the 'Category' box. Change 'Decimal Places' to '0' and click 'OK.'
4. Open the 'Find and Replace' dialog while the cells are still selected. Go to the 'Find Select' drop-down on the 'Home' tab in Excel 2007 and select 'Replace.' In Excel 2003 or earlier, go to the 'Edit' menu and click on 'Replace.' You can also use the keyboard shortcut 'Ctrl H' to open 'Find and Replace.'
5. Type a dash into the 'Find What' box. Leave the 'Replace With' box empty. Click the 'Replace All' button. Excel will find all of the dashes in the selected cells and remove them.

How to Create a Text Box in Excel

1. Open Microsoft Excel 2007 and or start a new blank workbook or open an existing workbook that you want to create a text box in.
2. Select the 'Insert' tab from the top of the Excel 2007 screen to display the Insert ribbon. This ribbon includes all of the objects you can insert into an Excel workbook.
3. Locate the 'Text' section of the Insert ribbon. It is the section at the end or the right of the Insert ribbon.
4. Choose the 'Text Box' button from the Text section of the Insert ribbon. The button will be highlighted once it's clicked.
5. Click in the area in the worksheet where you want to begin drawing the text box. Hold down your left mouse button and drag outwards from that point to create the text box.
6. Release the mouse button once you are happy with the size and shape of the text box. The text box looks like white empty space with a border and sizing handles surrounding it.
7. Click inside the text box to insert the cursor, then type text or add objects inside it.

Thursday, April 12, 2012

Access Denied When Saving From the Excel 2007 Format to Excel 2003

1. Click the Windows 'Start' button and enter the folder location of the Excel file in the search text box. For instance, if the file is located in 'C:\myfolder,' enter this value into the text box and press 'Enter.' This opens an Explorer window that displays the folder contents.
2. Right-click the folder that contains your Excel spreadsheet and select 'Properties.' A window opens that displays several options for your folder.
3. Remove the check mark in the box labeled 'Read-Only.' This allows you to save your converted files to the folder and stops errors such as 'Access Denied.'

How to Change X Axis Values in Excel 2007

1. Click on the x-axis to select it. Click somewhere beneath the bottom horizontal line of the chart to ensure that you select the axis rather than the chart area.
2. Right-click within the selected area and choose 'Format Axis' from the drop-down menu that appears.
3. Click the circle for 'Fixed' next to 'Minimum:' under 'Axis Options' and type the smallest number you want displayed on the x-axis in the box to the right. Do the same for 'Maximum:' but type the largest number you want displayed on the x-axis.
4. Select the 'Fixed' circle next to 'Major unit:' and enter a number in the box to the right if you want to change what numbers are displayed on the x-axis. For example, type '0.1' into the box if you want to display every tenth.
5. Click the 'Close' button at the bottom of the 'Format Axis' box to accept your changes.

How to Make a Graph With a Z

1. Open Microsoft Excel and enter your data into the vertical columns. Highlight your data. Click the 'Insert' tab, then click the 'Chart' button under it.
2. Choose the graph you prefer, making sure that it is in 3-D. Click 'Press and Hold to View Sample' in order to preview the chart you selected before applying it. Click 'Next' once you have found the chart that works best for your purposes.
3. Enter your data range by highlighting the appropriate columns of data and then clicking the button beside the field your selected information appears in. Click the 'Series' tab to edit or modify your data series and their names.
4. Click 'Next,' then click the 'Titles' tab of the next window. Type in a title for your graph and titles for the x, y, and z axes.
5. Click the 'Axes' tab for options to make your axes' information visible or invisible. Click the 'Gridlines' tab to make gridlines visible or invisible. Click the 'Legend' tab to make your legends visible or invisible. You can also use this tab to determine legend placement.
6. Click 'Data Labels' to choose which pieces of series information and titles you want to show out of all you have entered.
7. Click 'Data Table' to view all of the series data you have entered in your graph in table form.
8. Click 'Next.' Select your chart location and placement in the final window that pops up. Click 'Finish.'

How to Use Excel 2010

1. Open a new Excel 2010 spreadsheet by double-clicking on the Excel icon on your desktop. Look at the spreadsheet and its layout. New files have three worksheets. Move between the three spreadsheets by clicking 'Sheet 1,' 'Sheet 2' or 'Sheet 3' in the lower left part of your screen.
2. Examine the tabs along the top -- 'File', 'Home,' 'Insert,' 'Page Layout,' 'Formulas,' 'Data,' 'Review' and 'View.' Click on each one to see its options. Most basic Excel 2010 commands are found on the 'Home' tab.
3. Click the cell in the upper left part of the spreadsheet marked A1, found in column A, row 1. Type the name of your first variable in cell A1 and additional variable names in B1, C1 and so forth.
4. Create your data table by entering data beneath each variable name in the cells A2, A3 and so forth. Use as many cells as necessary to fit your data set. Scroll down using the arrows on the right hand of your screen.
5. Change the formatting of the cells using the commands found on the 'Home' tab. Click on a cell or group of cells and change their font, color, size or appearance.
6. Move cells by highlighting them and hovering over the thick black border until you see a four-headed arrow. Click and drag the cells to your desired location. Copy cells by highlighting them and right-clicking on them. Click 'Copy,' click to where you want to move them, right-click again and click 'Paste.'
7. Perform basic functions by going to the 'Formulas' tab and clicking 'Insert Function.' Search for the name of the function you want to perform, such as adding, averaging or finding the maximum. Input a range of cells to tell the spreadsheet what calculation to perform. Press the 'Enter' key to view the result of the formula.
8. Save your worksheet by clicking the green 'File' tab. Select 'Save As' to designate a destination folder and unique name for your Excel 2010 spreadsheet. Click 'Print' to print a copy of your spreadsheet.
9. Access the Excel 2010 help files by clicking the blue question button in the top right corner of the screen. Type your question into the search box to find an answer from Microsoft.

How to Remove Hyperlinks From Excel 2007

1. Double-click your Excel file to open the file in Microsoft Excel 2007.
2. Select a blank cell in your spreadsheet, then type the number '1' into the cell.
3. Right-click on the cell and select 'Copy.'
4. Hold down the 'Ctrl' key on your keyboard as you select each cell containing a hyperlink that you want to remove.
5. Click the 'Home' tab at the top of the Excel 2007 window.
6. Click the arrow below 'Paste' in the 'Clipboard' section, then click 'Paste Special.'
7. Click 'Multiply' in the 'Operation' section, then click 'OK.' This removes the actual hyperlink from the cell, but it leaves your data underlined.
8. Click 'Cell Styles' in the 'Styles' section at the top of the window.
9. Click 'Normal' under the 'Good, Bad and Neutral' section to remove the underlines.

How to Construct a Histogram Using Excel 2007

1.
Capture the stock data. Go to Yahoo Finance and type 'SBUX' in the symbol box. Click on the link that says 'historical prices'. Go to the bottom of the page and click 'download to spreadsheet'. The info will download automatically and then open in an Excel spreadsheet.
2.
Get the Max and Min values of the 'Adj Close' column. Go to the editing option of the Home menu. In the drop-down box of mathematical functions select 'Max.' Type 'G2:G501' and press 'Enter.' Repeat the same with 'Min.' The Max value should be 28.29 and the Min value should be 7.17.
3.
Create the bin values. Round 28.29 to 30 and 7.17 down to 5. Start at 5 and add increments of 1 until you get to 30. Type those numbers in the column next to 'Adj Close' and call it 'Bin values.'
4.
Open the histogram dialog box. Under the 'Data' tab select 'Data Analysis'. In the dialog box select 'Histogram' and press 'OK.' The Histogram dialog box will then open.
5.
Input the histogram setting. In the 'Input Range' field type '$G$2:$G$501' and in the 'Bin Range' field type '$H$2:$H$25'. These are the cell ranges of the data and bin values respectively. In 'Output Options' select 'New Worksheet Ply' and name the worksheet 'Histogram.' Then select chart output and press 'OK.'
6.
Survey the Histogram. Observe the distributions of the lines. If they bundled in the middle the histogram is said to be 'evenly distributed' or 'bi modal.' This is usually the case with test or survey data. As is sometimes expected with erratic stock data the histogram in this article turned out to be 'saw-toothed.' This kind of distribution might indicate that the stock under inspection has been quite volatile over the past 500 days.

How to Make Sums in Excel 2010

1. Open the Microsoft Excel 2010 spreadsheet that contains the numbers you want to add together.
2. Click on an empty cell in the spreadsheet, where you want the summation to appear. Type the equals sign into the cell to start a formula.
3. Enter 'SUM(' if you want to add together a number of cells. Type in the column letter and row number of the first cell that you want to include. Place a colon after this cell reference if you are adding together a continuous range of cells, then type in the last cell in the range. Place a comma after the first reference if the selected cells are not continuous on the spreadsheet, and enter in the second cell reference. Continue adding commas and cell references until you have entered all of the cells you want to add together. Enter a close parenthesis and press 'Enter.' For example, if you want to add the cells from 'A1' to 'B10,' enter: '=SUM(A1:B10).' If you want to add just 'A1' and 'B10,' enter '=SUM(A1,B10).' If you want to add 'A1' through 'A10,' but include 'B10' as well, enter '=SUM(A1:A10,B10).'
4. Enter 'SUMIF(' if you only want to add together some of the cells, based on the values of adjacent cells. Type in the first cell in the range that you want to check for a given criteria, then place a colon followed by the last cell in the range of cells. Type in a comma, then enter the value that you want to check for in the range of cells. This can be a simple number, a piece of text enclosed in quotes, or a cell reference. Type in another comma, then type in the range of cells that you want to add together if the cells qualify given your condition. So if you entered '2001' as your criteria value, Excel will look through the first range of cells and whenever it finds '2001,' it would add together the corresponding cell in the second range of cells. Type in a close parenthesis and press 'Enter' to complete the formula. For example, if you want to check cells 'A1' through 'A10' for the value '23,' and then add together the corresponding cells in the range of 'B1' through 'B10,' enter: '=SUMIF(A1:A10,23,B1:B10).'
5. Type 'SUMIFS(' into the cell if you want to sum together a range of cells based on multiple criteria. SUMIFS is similar to SUMIF, except the arguments are arranged in a slightly different order. Type the range of cells that you want to sum together into the formula, then enter a comma. Type the range of cells to check for a criteria, followed by a comma and the criteria itself. Enter another comma and continue to add criteria ranges and criteria until you have entered all data. Place a close parenthesis at the end of the formula and press 'Enter.' To add together cells 'A1' through 'A10' whenever '23' appears in column 'B,' and '25' appears in column 'C,' enter: '=SUMIFS(A1:A10,B1:B10,23,C1:C10,25).'

Wednesday, April 11, 2012

How to Link Data in Other Excel 2007 Workbooks

1. Open the Excel 2007 workbook that contains the data for which you want to link to other workbooks.
2. Double-click in the cell on the Excel workbook the contents of which you want to link to other workbooks. This will select all the contents in that cell.
3. Right-click in the selected cell and then click 'Copy.'
4. Open the other Excel 2007 workbook to which you want to link the data you selected from the workbook that you opened in Step 1.
5. Right-click in an empty cell on the workbook, click the 'Home' tab, click the downward pointing arrow below 'Paste' and then click 'Paste Special.'
6. Click the 'Paste Link' button on the Paste Special dialog box that opens and then click 'OK.' Repeat steps 3 to 6 in other workbooks to which you want to link the selected data.

How to Use the Cursor to Highlight Rows in Excel 2003

1. Move the cursor to the row number on the left side of your spreadsheet and click the number to highlight the row.
2. Move the cursor to another row number, hold down the 'Shift' key, and click the row number to highlight all the rows between the first highlighted row and the new row. For example, highlight row 12, move the cursor to row 20 and shift-click the row number to highlight all rows between 12 and 20.
3. Move the cursor to another row, hold down the 'Ctrl' key and click the row number to highlight only individual rows. For example. highlight row 12, move the cursor to row 20 and ctrl-click the row number to highlight rows 12 and 20.

Subscribe to: Posts (Atom)