Excel

Browse » Home » All posts

Thursday, November 22, 2012

How to Insert Animated GIF Files in Excel

1. Navigate to the folder that contains your animated GIF file. Write down the full path name of the folder and the name of the file. For example, if the file is contained in a subfolder of your C: drive called Pictures and the file name is bear.gif, then the full path name would be c:\pictures\bear.gif.
2. Open the Microsoft Excel document to which you would like to add your animated GIF.
3. Click on the 'View' menu and choose 'Toolbars' => 'Control Toolbox.'
4. Click on the top button in the 'Control Toolbox,' which activates 'Design Mode.' 'Design Mode' allows you to draw an item in your spreadsheet that may receive an object such as an animated GIF.
5. Click on the bottom button in the 'Control Toolbox,' which has a hammer. From the drop-down menu, select 'Microsoft Web Browser.' This activates 'Microsoft Web Browser' controls, which can be used to view animated GIFs.
6. Left-click and hold down your mouse button on the location in your spreadsheet in which you would like to embed the animated GIF. Draw a box that is large enough to contain the animated GIF image.
7. Click on the 'Design Mode' button again to disable design mode. Close the 'Control Toolbox' toolbar.
8. Hold down the 'Alt' key on your keyboard and press the 'F11' key. Doing so will open a Microsoft Visual Basic window within Excel.
9. Double-click on the worksheet to which you are adding your animated GIF in the Project list in the upper-left corner of the Visual Basic window. This will open a Visual Basic Code window.
10. Copy and paste the following code into the Code window:Private Sub Worksheet_Activate()WebBrowser1.Navigate 'C:\pictures\bear.GIF'End SubChange the path name and filename in quotes to the appropriate path for your animated GIF file.
11. Click to a different worksheet, and then click back to the worksheet to which you added the GIF. The animated GIF should be visible in the object box that you drew.

How to Highlight Duplicates in Excel

1. Highlight the cells you want to check for duplicates. You can highlight a row, a column, any array or non-contiguous group of cells, or the entire worksheet.
2. Click 'Home,' then 'Styles,' and then 'Conditional Formatting' in Excel 2010, or the 'Conditional Formatting' drop-down in the Home tab in Excel 2007.
3. Choose 'Highlight Cell Rules,' then click 'Duplicate Values....'
4. Leave 'Duplicate' selected in the drop-down list of the resulting window, and choose a preset or custom format. Click 'OK' to close the window and view the highlighted results.

How to Transfer an Adobe InDesign Table Into Microsoft Excel

1. Count the exact number of rows and columns in the InDesign table you wish to transfer. Knowing the exact number will be important when you are working in Microsoft Excel.
2. Click on the Type tool in the Tool Palette. The Type tool looks like a capital letter T.
3. Select all columns and rows of the table. To do this, hold your mouse over the left most line on the top row of the table. The cursor will turn into a solid back arrow. Hold the mouse button down to highlight the first row and drag the mouse down to the bottom of the table to highlight all of the rows. The table will be selected when all cells are solid black.
4. Choose 'Copy' from the edit menu.
5. Open Microsoft Excel and find the place you want to insert the table. You can add to an existing document or create a new document.
6. Click in the cell in which you want to start your table. Drag the mouse to the right and down to select the same number of rows and columns that you had in your InDesign document. If you do not select the same number of rows and columns, the information may not display properly.
7. Paste the data into the selected sells by choosing 'Paste' from the 'Edit' menu.

How to Add a Footer in Excel 2007

1. Click the 'Insert' tab in Microsoft Excel.
2. Click 'Header Footer' in the 'Text' group.
3. Click the bottom scroll arrow until 'Click to add footer' is visible and click it.
4. Type the text you wish to display. To enter dynamic elements, click the appropriate button in the 'Header Footer Elements' group of the current Design tab. Available elements include 'Page Number,' 'Number of Pages,' 'Current Data,' 'Current Time,' 'File Path,' 'File Name,' 'Sheet Name' and 'Picture,' which allows you to add a picture to the footer.
5. Click the 'Home' tab to access text formatting features of the 'Font' and 'Alignment' groups, such as font type, font size, bold, underline, colors and centering.
6. Click the main spreadsheet window to exit the footer.
7. Click the 'View' tab and click 'Normal' to return to the normal view mode.

Wednesday, November 21, 2012

How to Make Negative Numbers = 0 in Excel

In Formulas
1. Write a formula to evaluate data. For example, if you're subtracting A1 from B1 and showing the results in C1, type '=(B1-A1)', minus the quotes, into cell C1.
2. Add 'MAX' to the formula to designate the maximum answer allowed: '=MAX(B1-A1)'. This is only part of the formula; if you press 'Enter' at this point, you will get an error message.
3. Insert the amount for the maximum number allowed as the formula result -- in this case, zero: '=MAX(0,B1-A1)'. Any results that would normally be negative become 0, not just as display text, but as value. If the formula result of B1-A1 is -2, once you use the MAX function, it actually becomes zero, and if you add 5 to it, the result will be 5, not 3.
Formatting
4. Enter all data as usual, including negative numbers, or open a worksheet containing the data you want to use. Select all the cells you want to display only as positive numbers or zeros.
5. Click on the number format drop-down and choose 'More Number Formats....'
6. Choose 'Custom' in the left-hand pane.
7. Type '##;'0';0', without the outer quotation marks, into the field labeled 'Type:', overwriting any symbols already in that field. Click 'OK' to return to the spreadsheet, where all negative numbers will now display instead as '0' while retaining their actual values. If a cell value is -2, it will display as 0, but if you add 5 to it, it will become 3, not 5.

How to Create a Weekly 24 Hour Calendar With Excel

1. Open a blank worksheet in Excel. Select cells A1 through H1 by clicking on A1, holding down the mouse button and dragging the mouse over to H1. Go to the Cells section of the Home tab, click the 'Format' drop-down arrow and select 'Row Height.' Change the row height to '26.25' and click 'OK.'
2. Type the word 'Time' in cell A1. In cell B1, type 'Monday.' Click B1 to select it, then click on the fill handle, which is a small black square in the bottom right corner. Drag the fill handle across to cell H1 to fill with in the other days of the week.
3. Type '12:00 AM' in cell A2. Select A2 and grab the fill handle. Drag it down to cell A25 to fill each cell with an hour, giving you 24 hours on your calendar.
4. Select cells A2 through H25. Right-click and select 'Format Cells.' On the Alignment tab, select center for both vertical and horizontal. On the Border tab, select a thin or dotted line in the Line Style box, then select 'Outline' and 'Inside' in the Presets section. Click 'OK' to apply these changes.
5. Select cells A1 through H1. Right-click and choose 'Format Cells.' On the Alignment tab, select center for both vertical and horizontal. On the Font tab, change the font if you want, and select a larger, legible size (16 through 20 work well). On the Border tab, select the thickest line in the Line Style box, then select 'Outline' and 'Inside' in the Presets section. On the Fill tab, select a fill color or pattern. Click 'OK' to apply these changes.
6. Change the column width in cells A1 through H1 so that the text fits properly. Place your mouse on the line between the A and B column headers in the gray area just above the worksheet. When you see a thick line with arrows pointing right and left, double-click to adjust the column to the width of the text. Repeat with the remaining columns.

How to Insert Page Breaks on a Pivot Table

1. Open the Excel file that contains the pivot table you want to format. Then select 'View,' 'Toolbars' and 'PivotTable' from the toolbar to open the 'PivotTable' toolbar if it's hidden.
2. Select 'PivotTable' and 'Table Options' from the 'PivotTable' toolbar. The 'PivotTable Options' dialog box will open. Add a checkmark to 'Repeat item labels on each printed page' and to 'Set print titles.' Then click 'OK.'
3. Go to the 'Pivot Table Field List' task pane. You can open the task pane if it's closed by clicking on the 'Show Field List' button in the 'PivotTable' toolbar.
4. Customize how each page is categorized by selecting 'Page Area' from the 'Add To' drop-down list in the task pane. Select the field you want to use to separate each page. Then drag it from the task pane into the 'Page Area' in the top-left section of your table (by the 'Row Area').
5. Choose 'PivotTable,' 'Select' and 'Entire Table' from the 'PivotTable' toolbar. Then click on 'File,' 'Print Area' and 'Set Print Area' from the menu. This will set which area of the page will be printed.
6. Select 'File' and 'Page Setup' from the menu and click on the 'Sheet' tab. Click within in the 'Rows to repeat at top' text box. Then go to the worksheet (while the dialog box is open) and select the rows you want to repeat on each page. Click within the 'Columns to repeat at left' text box. Then go to the worksheet and select the columns you want to repeat. Click 'OK' to close the 'Page Setup' dialog box when you are finished.
7. Click on 'View' and 'Page Break Preview' in the toolbar to switch to page break view. The current page breaks in your pivot table report will be revealed at the blue lines. You can insert page breaks manually on your page or after each row type.
8. Insert a manual page break by right-clicking on any row where the page break will be added. Then select 'Insert Page Break' from the pop-up.
9. Insert a page break after each row type by double-clicking on the row type heading on your spreadsheet. The 'PivotTable Field' dialog box will open. Click on the 'Layout' button to open the 'PivotTable Field Layout' dialog box. Then add a check mark to 'Insert page break after each item' and click 'OK.'
10. Click on 'OK' to close the 'PivotTable Field' dialog box.
11. Click on 'View' and 'Normal' in the toolbar to return to normal view. Then save your changes.

How to Check for Circular References in Excel

1. Start Microsoft Excel 2007 and open a workbook from your files that contains formulas that aren't properly calculating a result.
2. Study the formula that isn't calculating a result. Check to see if a cell reference in the formula is referring to the cell that the formula resides in. If it is, then you have a circular reference.
3. Select the 'Formulas' tab to display the 'Formula' ribbon. This contains all the formula options for an Excel workbook.
4. Click the arrow to the right of the 'Error Checking' button in the 'Formula Auditing' section of the 'Formula' ribbon.
5. Point to 'Circular References' from the 'Error Checking' menu to display the list of formulas that contain a circular reference in your open Excel workbook. Click on the formula you want to fix, and it will become selected in the worksheet.
6. Correct the formula in the 'Formula Bar' of the Excel spreadsheet. You will need to either move the formula to another location or change the reference within the formula to a different cell reference.
7. Press the 'Enter' key to enter the new formula into the worksheet. Continue to check for and fix all of the circular references within the spreadsheet.

Tuesday, November 20, 2012

How to Get Document Recovery Task Pane in Excel 2007

1. Open Microsoft Excel 2007 on your computer and then select the 'Microsoft Office' button. Click the 'Open' button.
2. Select any Excel file and then click the 'Open' button. Press the 'Ctrl,' 'Alt' and 'Delete' keys at the same time to bring up the Task Manager.
3. Click the 'Applications' tab and then select the Excel 2007 file you have open. Click the 'End Task' option.
4. Open the Excel 2007 program again and the Document Recovery task pane will appear. Click the 'Recovered' button from the status bar to hide the Document Recovery task pane.
5. Click the 'Recovered' option again at any time to make the Document Recovery task pane reappear.

How to Display Statistical Worksheet Functions in Excel

1. Click the Microsoft 'Office' button in the top left corner of Excel.
2. Click the 'Excel Options' button.
3. Click 'Add-Ins' in the list. The window to the right will display all the inactive add-ins that you can add. You do not need to click any of these at this point.
4. Click 'Excel Add-Ins' under the Manage section at the bottom and click 'Go.' You will get a pop-up window with available add-ins.
5. Check the 'Analysis ToolPak' box and click 'OK.' If you get a message that it is not installed and are asked if you want to install it, click 'Yes' to install it.
6. Click the 'Data' tab on the ribbon to check whether you have the statistical add-in. You should now have an 'Analysis' group under the 'Data' tab.

How to Use Countif on Excel 2003

1. Open Excel 2003 and select a workbook. Click 'File' on the menu bar. Select 'Open.' Browse your computer file and locate the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Click on a cell to enter the Countif function. Enter the following formula in the cell: '=Countif.' Notice the Excel hint that appears beneath the formula. Hints serve as a mini guide to assist you as you enter the formula. Click the 'Insert Function' button on the formula bar. The Function Arguments dialog box appears.
3. Click the box with the red arrow in the 'Range' field. The Function Arguments dialog box becomes minimized. Highlight the range that you want to count. When you stop highlighting, the Function Arguments dialog box reappears. Enter your criteria in the Criteria box. In this example, you will count all values that are greater than 1000. So to notate greater than, enter the '>' symbol. In this criteria, enter the following: '>1000.' Click 'OK.' Excel displays the results of the Countif function.

How to Create a Curve Graph in Excel or Word

1. Input your x-values into the first column, starting at cell A1.
2. Input your y-values into the second column, starting at cell B1.
3. Select 'Insert' from the toolbar and choose the 'Scatter' button. Choose 'Scatter With Smooth Lines' from the drop-down menu.
4. Select the chart by left clicking on it. Go up to the top toolbar and select 'Layout' underneath 'Chart Tools.' Select 'Primary Horizontal Axis' from the 'Axes' drop-down menu (press the down arrow beneath 'Axes' for the menu). Select 'More Options,' and change the minimum and maximum x-values by selecting the top two radio buttons. Enter a value that corresponds with your data. For example, if your spread of data is from -10 to 9.1, make your minimum x-value -10 and your maximum y-value 10.
5. Repeat Step 4 for the 'Primary Vertical Axis,' entering your y-values in place of the x-values.
6. Press close.
7. Select the graph by placing your cursor on it and left clicking on the mouse. Hit Ctrl C to copy the graph. Hit Ctrl P in Word to paste the graph into the document.

Sunday, November 18, 2012

How to Remove a Password on Excel

Office 2003
1. Launch Excel from the desktop icon or the “Start” menu of your computer. Go to the “File” menu and select “Open.” Navigate to the folder that contains the password-protected Excel workbook and double-click the file to open it.
2. Enter the password that you created for the Excel workbook, when prompted. Press “Enter” to open the workbook.
3. Go to the “File” menu in Excel and choose “Save As.” Open the “Tools” menu in the “Save As” dialog box and select “General Options.”
4. Highlight the asterisks in the “Password to open” box. Press the “Delete” key and click “OK” to close the password box.
5. Click the “Save” button. Confirm that you want to replace the existing Excel workbook, and the file is saved with the password removed.
Office 2007
6. Launch Excel. Click on the “Open” folder icon, navigate to the file on the hard drive and double-click the file name to open it.
7. Type in the password that you set for the workbook and press “Enter.” The workbook should open on the screen.
8. Click on the button with the Microsoft Office icon in the upper-left corner. Hover over “Prepare” and choose “Encrypt document” from the options that appear.
9. Highlight the asterisks in the “Encrypt document” box that pops up. Press “Delete” and click “OK.”
10. Press the “Save” button at the top of the screen to save the file. Confirm that you want to write over the original file, if prompted, and the file is saved without the password.

How to Use Excel Formula Functions

1. Launch Microsoft Excel. You can begin with a blank document or open an existing document for which you want to use formula functions. If you do not have MS Excel, use the link in Resources for a free trial.
2. Populate the spreadsheet with the data appropriate to one or more of the Excel functions that you want to use. Click once inside the cell in which you want to use a formula. Click once on the 'Insert Function' button, which is represented by the symbol 'fx' and is located immediately to the left of the 'Formula Bar.' This will launch a separate window in which you may choose the appropriate formula.
3. Use the 'Category' drop-down menu on the 'Insert Function' window to choose from a list of formula types. This will narrow the results of available formulas, but you can also choose the 'All' listing to display all available function formulas. If you are not certain which formula to use, you can type a description into the 'Search' field and click once on the 'Go' button. Click once on the function that you want to use and once on the 'OK' button. This will usually launch a separate window, if further detail is required for Excel to run the function.
4. Enter the appropriate information into any of the required fields on the 'Function Arguments' window. The names and ranges of selected criteria will display to the right of any required field, and will be red if using the entry will result in an error, or green if accurate. The anticipated result of any formula will appear to the bottom of the 'Function Arguments' window. Once the appropriate criteria have been met, click the 'OK' button once. The result will appear in the selected field.

How to Unprotect Specific Areas in Excel

1. Launch Microsoft Excel 2010 and open the spreadsheet that's protected.
2. Click the 'Review' tab on the toolbar and click 'Unprotect Sheet' from the 'Changes' group. Enter the password if prompted.
3. Highlight the cell or cells that are to become unprotected.
4. Hold 'CTRL' and 'Shift' and press the letter 'F' on the keyboard and then release all of the keys to bring up the 'Format Cells' dialog box.
5. Click the 'Protection' tab and clear the check from the 'Locked' check box. Click 'OK' to save the changes. Repeat for additional cells or ranges as necessary.
6. Click the 'Review' tab and click 'Protect Sheet' under the 'Changes' group to protect the sheet.

How to Create Percentages in Microsoft Excel 2003

1. Write out your fraction. The numerator, or top number, will be the amount you want to determine as a percent of the total. The denominator, or bottom number, will be the total amount. For example, if you have 18 marbles, five red and 13 green, and you want to determine the percent of green marbles, then 13 is the numerator and 18 the denominator.
2. Type the numerator in cell A1 of your Excel spreadsheet. Type the denominator in cell B1. Using our example, you would type 13 in cell A1 and 18 in cell B1.
3. Type the following equation in cell C1 to get a percentage from your equation:=(A1/B1)*100

How Do I Print Out Row Numbers for My Excel Spreadsheet?

Microsoft Office Excel 2007
1. Select the worksheet whose row numbers you want to print. If you want to have row numbers print on multiple worksheets, you must repeat the process for each worksheet.
2. Click 'Page Layout' from the options across the top of the screen.
3. Locate the 'Sheet Options' subgroup, which will be the fourth subgroup from the left.
4. Click the box next to 'Print' under 'Headings' to turn on the row and column titles for printing for the worksheet.
Microsoft Office Excel 2003
5. Select the worksheet whose row numbers you want to print. If you want to have row numbers print on multiple worksheets, you must repeat the process for each worksheet.
6. Click 'File' from the menus at the top.
7. Select 'Page Setup' from the drop-down menu, and then click 'Sheet Tab.'
8. Check the box next to 'Row and Column Headings,' and then click 'OK.'

How to Use the CORREL Function in Microsoft Excel

1. Start Microsoft Excel. You can do this by clicking on Start, highlighting Programs, and clicking Microsoft Excel, or double-clicking the Microsoft Excel icon on your computer's desktop.
2. Before we work with data, it's important to understand an important fact about the correlation coefficient, the value that represents the strength of the relationship between two random variables. The correlation coefficient ranges from -1 to 1, with -1 indicating a perfect negative correlation and 1 indicating a perfect positive correlation.
3. Enter the data. I'll use cells A1 to A25 and B1 to B25 for this purpose. The A column represents each student's homework average and the B column refers to each student's exam average.
4. Enter the following values, starting with cell A1 and ending with cell A25: 89, 92, 88, 76, 90, 68, 100, 87, 93, 77, 81, 80, 94, 90, 83, 79, 73, 83, 91, 84, 88, 90, 93, 80, 91.
5. Now enter the following values, starting with cell B1 and ending with cell B25: 76, 83, 80, 84, 81, 90, 92, 78, 70, 93, 82, 90, 98, 75, 67, 72, 90, 82, 77, 81, 87, 63, 92, 71, 82.
6. Now we can calculate the correlation coefficient. In cell A27, type =CORREL(A1:A25, B1:B25). A1 to A25 and B1 to B25 is where the data appears. Hit ENTER.
7. The result is -.07965, indicating a very slight weak relationship between the two variables.

How to Multiply Using Excel 2007

1. Determine the cell references that will be multiplied together. The cell reference refers to the labels on the columns and rows where the cell is located and will include a letter and a number.
2. Choose the cell where the multiplication results will be returned by clicking within that cell.
3. Click in the Formula Bar (denoted by 'Fx' at the top of the screen). Type = , then the cell reference of the first cell, * and finally the cell reference of the second cell. A correct formula will appear as: =G10*X10, according to Home Learn.
4. Press 'Enter' on the keyboard to multiply the cells together. The correct answer will now appear in the cell.

Saturday, November 17, 2012

How to Center Numbers in a Cell in Excel

1. Select the cell with the numbers you want to center.
2. Click the 'Home' tab.
3. Click the 'Center' button, located in the Alignment section.

How to Remove Duplicate Rows From an Excel Spreadsheet

1.
Click on the 'File' menu and then the 'Open' option to open the spreadsheet with the duplicate rows. The example highlights the duplicate rows for easier viewing.
2. Select the entire spreadsheet by clicking on the top left cell of the column header and then scrolling to the last row. Press the 'Shift' key and click on the lowest right cell with information. The rows are highlighted.
3.
Click on the 'Data' menu, 'Filter' and then 'Advanced Filter.' The Advanced Filter dialog box appears.
4. Choose 'Filter the List, In Place' and 'Unique Records Only.' Then click the 'OK' button. Excel hides the duplicated records.
5. With the filtered records still selected, press the 'Ctrl' and 'C' keys to copy the rows. The list is highlighted with bounding outlines.
6.
Click on the 'View' menu, 'Toolbars' and then 'Clipboard.' The Clipboard window appears with your copied data in the first icon.
7. Click on the 'Data' menu, 'Filter' and then 'Show All' to display the original list.
8. With the original list still selected, press the 'Delete' key. The list is deleted.
9.
Click the first icon in the 'Clipboard' window. The list appears with no duplicates. The example highlights the filtered rows for easy viewing.
10. Click the 'Save' button to save the spreadsheet.

How to Install Speech in Office Excel

1. Click the Windows 'Start' menu. Click 'Control Panel' and select 'Add or Remove Programs.' The 'Add or Remove Programs' screen will appear.
2. Click on 'Microsoft Office' and click the 'Change' button. Select 'Add or Remove Features' and click 'Next.' Double-click 'Office Shared Features.'
3. Double-click 'Alternative User Input' and select 'Speech.' Click the down arrow next to 'Speech' and select 'Run from my computer.' Elect to update Microsoft Office. The installation of speech recognition is complete after the update finishes.
4. Open Microsoft Excel. Click the 'Tools' menu button. Click 'Speech' to begin speech recognition.

Friday, November 16, 2012

How to Calculate the Percentage Change in Two Numbers in Excel

1. Find the beginning number and the ending number. For example, assume a business has income of $700 in 2009 and $1,300 in 2010.
2. Type the beginning number in cell A1 of your Microsoft Excel page. In the example, type '700' in cell A1.
3. Type the ending number in cell A2. In the example, type '1,300' in cell A2.
4. Type '=((A2-A1)/A1)' in cell A3. This calculates the percent change. In the example, your results should be 0.857. You can convert this to a percentage by formatting the number as a percentage using the tool bar.

How to Learn Descriptive Statistics Using MS Excel 2007

1. Define variability in data using the range, variance and standard deviation functions. In MS Excel 2007, use the Data Analysis Tool under the Data Tab in the top navigation bar. If the option is not available, install the Excel Analysis Toolpack using the MS Office Button. First select Excel Options, then Add-ins, then select Analysis Took Pac, click Go, then OK. Once installed, select the Data tab, then Data Analysis, then Descriptive Statistics; click on the input field and highlight the data. Then check the summary statistics checkbox and click OK.
2. Construct a histogram for analysis of frequency. Open a spreadsheet and enter a set of data values in a column. Select the charting function under the Insert tab and select histogram charts, then click the input field and highlight the data in your column using your mouse. Click OK.
3. Test kurtosis, skewness and distribution using the charting feature. Enter the data in rows or columns, then select Bar or Scatter Plots under the Insert Chart tab to reveal the graphical representation of data dispersion. For kurtosis, select the Formulas tab, then More Functions, then scroll down until you see KURT. Click the input field, highlight your data and select OK.
4. Utilize the stem-leaf and box-plot functions to perform projections (explanatory data analysis). The stem-leaf and box plots are available under charting using the histogram option.
5. Invest in other statistical analysis software to perform more advanced analysis. Several personal and commercial brands exist in the market. MINITAB is a popular personal tool that can be utilized with Excel for point analysis, and SPSS is available from IBM for a more comprehensive package of tools.

How to View the Contents of a Cell in Excel 2003

1. Double-click the Excel file that you want to work with to open it in Excel 2003.
2. Click on the cell that you want to see the contents of. If the cell is hidden behind a window, you can also navigate to it by clicking any cell and then using the arrow keys to move the selection to your desired cell. Finally, you can also click on the box just above the 'A' column and type in the column and row of the desired cell.
3. Look at the formula bar, which starts just above the 'C' column, to see the contents of the cell.

How to Make a Number Line in Excel

1. Open the Excel spreadsheet that needs to have a number line. Click the 'Insert' tab at the top of the window.
2. Click the 'Shapes' button inside the 'Illustrations' heading. Select the image of a line with an arrow at either end inside the 'Lines' heading.
3. Click the bottom line of the cell that needs to be the far left end of the number line. Drag across to the last cell that needs to the far right end of the number line. For example, click the bottom line of 'D6' and drag across to the end of the 'N6' cell to make a number line that spans from the 'D' column to the 'N' column of row 6.
4. Click the second cell in the range, such as 'E6.' Select the 'Home' tab and click the 'Borders' icon inside the 'Font' heading. Click the 'Left Border' button to add the first vertical line on the number line.
5. Repeat the process with each other cell in the range that needs to have a vertical line to designate an entry on the number line.
6. Click one of the cells below the horizontal number line that needs to have a number inserted. For example, click 'E7' if you want the first number to appear in row 7.
7. Navigate back to the 'Insert' tab and click the 'Text Box' button in the 'Text' heading. Click the cell again and drag until the text box is the size you want for the first number. Type the number in the box and then select the font size in the 'Font' drop-down menu.
8. Click the left edge of the text box and drag the number to a different location if it doesn't appear exactly where you want it on the number line. Repeat the process of creating text boxes for any other numbers that need to appear on the line.

Thursday, November 15, 2012

How to Make Excel Accept 20 Digit Numbers

1. Right-click on the cell in which you want the 20-digit number to appear. Use shift or control to select multiple cells first, if multiple cells will be accepting 20-digit numbers.
2. Click on 'Format Cells...'
3. Choose 'Text' and click 'OK' to exit.
4. Paste or type the 20-digit number into the cell. Every digit will remain unchanged.

How to Copy Formulas Without Changing Cell References in Excel

1. Click the cell containing the formula that you would like to copy, then use the mouse pointer to highlight all of the text in the formula bar (the bar with 'fx' on the left side) at the top of the screen. Alternatively, press the 'F2' key to display the formula in the cell itself, then highlight it with the mouse pointer.
2. Press the 'Ctrl' and 'C' keys simultaneously to highlight the copied text.
3. Press the 'Escape' key to take Excel out of the formula editing mode.
4. Click the cell that you would like to paste the formula into.
5. Press the 'Ctrl' and 'V' keys simultaneously to paste the formula into the selected cell. Repeat steps 4 and 5 for each cell that you would like to paste the formula into.

How to Calculate Compound Interest in Excel

1. Create a new blank Excel document by clicking on the 'File' menu and selecting 'New.'
2. Enter a column heading corresponding to the following values in the first row of your document starting in column A.Column - Heading
A - Amount Invested
B - Annual Percentage Rate
C - # Times Compounded Annually
D - # of Years
F - Future Value
3. Enter the desired values for calculation in row 2 starting in column A. Example values are listed below for each column. This represents $1,000 compounded quarterly at a rate of 2.25% for 10 years.A - 1000
B - .0225
C - 4
D - 10
4. Enter the following function in row 2 of column F.=A2*((1 B2/C2)^(C2*D2))The result of this formula should be approximately 1251.53.
5. Apply this formula to more rows by clicking on it and dragging your mouse down the desired number of rows. When you have highlighted enough rows in one column hit the Control (Ctrl) and D keys at the same time to 'Fill Down' the formula.

How to Enter Formulas in Excel to Show Not Less Than Zero

Hide Negative Values With Conditional Formatting
1. Select the cell(s) in your Excel spreadsheet you wish to format.
2. Click “Conditional Formatting” under the “Format” menu.
3. Select “Cell value is” from the first drop-down menu for Condition 1.
4. Select “less than or equal to” from the second drop-down menu.
5. Enter 0 in the next box.
6. Click on the Format button and select white in the color drop-down menu. If the background color of your cells is something other than white, select a color that will blend in and make it appear as if the cell is empty.
7. Click OK. Any cells you have applied this conditional formatting to will appear to be blank if the value in the cell is equal to or less than zero. If you place your cursor in the cell you will be able to see the cell’s actual value in the formula bar at the top of the worksheet.
Display Negative Values As Blank Cells or Dashes
8. Use the IF function to specify the format of a result that is equal to or less than zero. The syntax of the IF function is IF(logical_test,value_if_true,value_if_false)
9. Enter 1 in cell A1 and enter 2 in cell A2 of your worksheet.
10. Create an IF formula in cell A3 by typing: =IF(A1-A2>0,A1-A2,“-”). In this example the logical test is whether cell A1 minus cell A2 is greater than zero. If it is the “value_if_true” it will display; if it isn’t, the “value_if_false” will display. In our formula we defined the value_if_true as the difference between cells A1 and A2 and the value_if_false as a dash. Therefore, if A1-A2 is zero or less you’ll see a dash in cell A3, if the result is positive, you will see a number.
11. Change the values in cells A1 and A2 to see how it affects what displays in cell A3.

How to Create a One Way ANOVA in Excel

1. Open the Excel 2010 file where you want to create your one way ANOVA. Enter your data sets into the Excel cells. Separate your data sets by columns with the top cell in each column describing the set.
2. Click the 'File' tab at the top of the screen. Choose 'Options' from the list that appears on the left side of the screen. Select 'Add-Ins' from the left side of the window that appears. Click 'Go' at the bottom of the window.
3. Place a check mark next to 'Analysis ToolPak' and click 'OK.'
4. Click the 'Data' tab at the top of the Excel window. Find the 'Analysis' area and click the 'Data Analysis' button found there. Click on 'Anova: Single Factor' in the pop-up window and click 'OK.'
5. Place your cursor into the 'Input Range' box. Click and hold on the top-left cell in your data sets. Drag your mouse to the bottom-right cell and release the button. If you included the headers in your selected range, click the 'Labels in First Row' box in the ANOVA window.
6. Click the radio button next to 'Output Range' and then place your cursor into the text box next to the radio button. Select a cell on your worksheet where you want the ANOVA information to go. Click 'OK' to create the one-way ANOVA.

Tuesday, November 13, 2012

How to Remove Extra Rows in Excel 2003

1. Open Excel 2003. Click the 'File' menu and select 'Open'. Locate the spreadsheet with the extra rows and double-click its file name.
2. Press and hold down the 'Ctrl' key on the keyboard. Click once on each row number, in the leftmost column, to delete.
3. Right-click any of the highlighted rows. Select 'Delete Row'. The rows are deleted and the rest of the spreadsheet bumps up to replace those rows.

How to Use Excel 2007

1. Enter text or numbers into a cell by clicking in the cell, typing the desired text, and press 'Enter.' Press 'Tab' to move to the next cell.
2. To insert a row of cells into your spreadsheet, select where you wish your new row to be, click the Home tab and the 'Insert' button, and then click 'Insert Sheet Row.' Create a new column by clicking 'Insert Sheet Column' under 'Insert.'
3. To merge multiple cells together, either vertically or horizontally, select the cells you wish to merge, and click 'Merge Cells,' which appears under the 'Merge and Center' button in the Alignment group under the Home tab.
4. Add two or more cells together by first clicking in a cell. Type '=SUM(' (open parenthesis), and enter the cells you wish to be added together, followed by ')' (close parenthesis). For example, =SUM(F4,F5,F6) would add the values of cells F4, F5 and F6. Write a long list of cells quickly by entering the starting cell of a row or column, typing ':' (colon), and entering the ending cell, for example, F4:F13. Use calculation operators to multiply, divide, add, subtract, or exponentiate multiple cells. For example, a cell with the formula =B7/B3 would divide the value in B7 by the value in B3.
5. Use a variety of functions to fit your needs. Click the 'Insert Function' button on the formula bar to pull down a long list of functions, and click the function you want. To average two or more cells, go down to AVERAGE, and type the cells you wish to average, between parentheses.

How to Enter Dates in SUMIF

1. Open the Microsoft Excel 2010 spreadsheet where you want to create your SUMIF formula.
2. Click on the cell where you want to create your formula. Enter the following into the cell, without quotes: '=SUMIF(A1:A10,'. Change 'A1' to the first cell in the column of dates that you are searching through, and 'A10' to the last cell in that column.
3. Enter the following formula, without quotes, after the comma in your SUMIF formula: 'DATE(2011,x,y),'. Change '2011' to your desired year, 'x' to your desired month and 'y' to your desired day. The DATE function will ensure that your spreadsheet will still work if someone using a different date format opens it.
4. Use a range of dates, instead of a single date, by placing a greater than or less than sign before the date. Since you are using the DATE function, you will need to connect the two commands with an ampersand. For example, to sum all the dates on or before March 30th, 2011, enter the following after the first comma in the SUMIF formula: ''
5. Complete the formula by entering the range of cells that hold the actual values you want to add. Enter this range of cells as follows, without quotes: 'B1:10)'. Change 'B1' to the first cell you want to sum, and 'B10' to the last cell. Press 'Enter' to complete your formula.

How to Construct Yield Curves in Excel 2007

Preparation
1. Identify yield data for the construction of the yield curve. This data requires clear yield information associated with time factors. Yahoo! Finance provides yield data as part of its free service for sharing bond information.
2. Open Excel 2007.
3. Create a new blank worksheet. Press the 'New' button at the top of the program window, or choose the 'File' menu and the 'New' option.
Import Yield Data
4. Type or import the yield information into a new spreadsheet in Excel 2007. If using Yahoo! Finance, Excel 2007 can pull this information directly from the website. Click the 'Data' tab followed by the 'From Web' command. A pop-up window will appear.
5. Type in the URL of the Yahoo! Finance web page that features the yield data you wish to import. Press the 'Go' button.
6. Locate the table on the web page which contains the yield data. A yellow button with an arrow will appear in the upper left corner of the data table.
7. Click the yellow button and then press the 'Import' button in the lower right of the window. The yield data is imported.
Format the Data
8. Copy the 'Yield' column in your data set. Click the column letter at the top and press the 'Copy' button at the top of the program window, or the 'Control-C' keyboard combination.
9. Click the first cell in the first empty column to the right of the data set. Press the 'Paste' button or the 'Control-V' keyboard combination. This places the 'Yield' data as the last column, which Excel requires for creating the proper yield curve chart.
10. Delete the first 'Yield' column. Right-click on the column letter and choose the 'Delete' function.
11. Change the 'Maturity' data in the first column to numeric data only. For example, delete the word 'Month' from the cell which contains the content '3 Month' so the result is simple '3.'
Create Yield Curve
12. Hold down the mouse and drag from cell A2 to cell B9. Press the 'Control' key and do the same from cell E2 to cell E9.
13. Click the 'Insert' tab at the top of the Excel 2007 window. Locate the 'Charts' group and click the 'Scatter' option. Choose any of the 'Scatter' design formats. The Excel Chart Wizard will open.
14. Complete the Chart Wizard. It is acceptable to choose the 'Next' button for each step and use all the default settings.
15. View the resulting yield curve.

How to Disable Scroll Lock in Excel 2007

1. Click the Windows 'Start' menu.
2. Click 'Programs' and click 'Microsoft Office.' Click 'Microsoft Excel' to open Excel.
3. Press the 'ScrLk' or 'Scroll Lock' button on your keyboard. The abbreviation 'SCRL' will appear in the lower-right corner of Excel if scroll lock is turned on and will disappear when it is turned off.

Monday, November 12, 2012

How to Enter Sum Formulas into Multiple Cells in Microsoft Excel 2003

1. Select and activate an empty cell. Do this by left-clicking on that cell; you will also need at least two cells that contain data to be summed.
2. Enter the sum formula perquisites into the cell you have selected. Your first part of the formula should read as: =Sum(.
3. Highlight cells you wish to include in the sum formula. These cells will appear in a blue formula box.
4. After highlighting the desired cells to be included in the sum formula, press the enter button to implement this new formula.

How to Make a Date Change Color After Expiraton in Excel

1. Open Excel 2007 and select a workbook. Click the 'Office' button and select 'Open.' Browse your files and locate the workbook. Double click the workbook name. The workbook opens.
2. Highlight a range of cells that contains dates. Select the 'Home' tab and click 'Conditional Formatting.' Select 'New Rule.' Select 'Format only cells that contain.' In the Edit the Rule Description field, select 'Cell Value' and 'greater than.' In the next field, enter your expiration date.
3. Click 'Format' and assign a format to cells that match this condition. To change the color of the cell, modify the 'Fill' tab. Emphasize the text in the cell by changing font using the 'Font' tab. Click 'OK.' The changes are immediately applied to your cells. Cells in the range matching the condition will reflect the fill color selected.

How to Delete an Objects Worksheet in Excel Download

1. Double-click the Microsoft Excel file you downloaded. The file will open in the Excel program window.
2. Locate the 'Objects' worksheet. This will be presented as a tab at the bottom of the Excel window. The tab will contain a clear label called 'Objects'. A worksheet labeled 'Objects' may contain a variety of different content, depending on the intended use of the Excel file. Microsoft uses the 'Objects' term to refer to any integrated content based on another Office application. The 'Objects' worksheet could thus contain Microsoft Word text, or a PowerPoint presentation, displayed within the Excel environment. This could contrast other worksheets which contain conventional Excel-only data.
3. Click once on this 'Objects' worksheet tab to select it.
4. Right-click with the mouse on this 'Objects' tab label. A context menu will appear next to the mouse cursor.
5. Select the 'Delete' option from the pop-up context menu that appears. The 'Objects' worksheet is promptly deleted.

Sunday, November 11, 2012

How to Put Cells in a Microsoft Excel Header

1. Open your Excel spreadsheet. Type the data in the uppermost cells of the spreadsheet. For example, type 'test' in cells A1 and D1.
2. Click the 'Page Layout' tab. Click on the square with the arrow on the 'Page Setup' panel to see additional options.
3. Click the 'Header/Footer' tab, and then select the header to use from the 'Header:' drop-down box, or press the 'Custom Header' button and type your preferred header.
4. Click the 'Margins' tab, and adjust how far the header will be from the top of the page. Press the up or down arrow buttons beside the 'Header' box.

How to Create a Button Object in Excel Spreadsheet

1. Open Excel. Create a new spreadsheet or use an existing sheet.
2. Click the 'Insert' menu at the top of the Excel program window. Choose the 'Object' command. If the 'Object' command is not listed, the menu is automatically collapsed to show only the most frequently-used features. Click the double arrow at the bottom of the menu to expand and show all features. A pop-up window will appear after the 'Object' command is chosen.
3. Click the 'Create New' tab at the top of the 'Object' window if you wish to insert a blank object into the Excel spreadsheet. The object can be edited later, if desired. Alternately, click the 'Create from File' tab if you wish to insert an object from a file that already exists.
4. Choose the file type or the file name for your object. If creating a new blank object, scroll through the list of file types and click once on the program format you wish to create. For example, choose 'Microsoft Word Document' to create an object for Word in the Excel spreadsheet. If inserting an existing file, click the 'Browse' button on the 'Create from File' tab and locate the file on your computer and press the 'Insert' button.
5. Check the 'Display as icon' check box on either tab to create a button for the object. This will prevent the object from displaying as a full document within the Excel spreadsheet. Instead, the user will click a button to launch the object in its native program window.
6. Press the 'OK' button. The button object is created in the Excel spreadsheet.

How to Make a Combo Box in MS Word

1. Open a Word document. Click the “Microsoft Office” button and select “Word Options.” Choose “Show developer tab.” Click “OK.”
2. Click the “Developer” tab on the Word menu ribbon. Click “Design Mode” in the 'Controls' section. Click “OK” to enable macros.
3. Select the area on the document where the combo box will be placed and click the “Combo Box” icon in the Controls section. A gray box will appear on the document that reads “Choose an item.” This will be your combo box. Click on the words “Choose an item” and type in user instructions for the combo box. Click outside of the box when you have finished.
4. Click on the combo box and select “Properties” in the 'Controls' section. The properties options box will open. Type in a title for your combo box in the 'General' section.
5. Check the type of editing control that you want for the box in the 'Locking' section. These options can keep users from deleting the box or editing its contents.
6. Click “Add” in the drop-down 'Properties' section to add a drop-down option to the combo box. Type in a title for the option. By default the option’s title will become the option’s value (the title that appears when the mouse rolls over the option). If you want the option to have a different value, type it the “Value” box. Click “OK” to place the option in the combo box.Repeat the process until you have added all of the box’s options.
7. Click “OK” to save the changes made to the combo box.

How to Link Two Excel Spreadsheets

Create a Document with Multiple Spreadsheets
1. Create and name the first spreadsheet. At the bottom of the worksheet, right-click on the first tab (Sheet 1) and select 'Rename.' Type a new name. Example: Feb26.
2. Create and name the second spreadsheet. At the bottom of the worksheet, right-click on the second tab (Sheet 2) and select 'Rename.' Type a new name. Example: Feb27.
3. Make sure you are in spreadsheet Feb27. Enter numeric data in A1 and B1. In cell A1, type the number 2. Then in cell B1, type the number 3.
4. Enter a formula in cell C1 on the Feb27 spreadsheet. Click in the cell C1 and then click 'Formula' in the ribbon. Click AutoSum and then select Sum. In the formula field, you should see the formula '=SUM(A1,B1).' Press Enter.
5. Check to see that the formula functions correctly. If your formula has been entered correctly, you should see a 5 displayed in the C1 cell.
Make a Link from One Spreadsheet to the Other
6. Navigate to the Feb26 spreadsheet. Scroll to the bottom and click on the Feb26 tab. This will put you on the Feb26 spreadsheet.
7. Add a link from C1 on Feb26 to C1 on Feb27. On Feb26, click on C1 then click on the 'Insert' tab on the ribbon. Select the 'Hyperlink' icon.
8. Specify the cell on the Feb26 spreadsheet. In the formula bar, type: =Feb27!C1
9. Check the cell. Click the C2 cell. You should now see the value that came from cell C1 in Feb27 displayed in the cell C1 in Feb26; that is, you should see the number 5 in cell C1 Feb26.
10. Test to see if it's working properly. Navigate to spreadsheet Feb27 by clicking on the tab at the bottom. Change the contents of A1 to 3. You should see the 5 change to a 6. Navigate back to spreadsheet Feb26 by clicking on the Feb26 tab. You should see a 6 displayed in C1 Feb26.

How to Change the Name Order From First to Last in Excel

1. Open your list of names in Excel. The list of names should be in column A.
2. Enter the following formula in column B1:=RIGHT(A1,LEN(A1)-FIND(' ',A1))Note that there is a space between the quotation marks in the find function.
3. Enter the following formula in column C1:=LEFT(A1,LEN(A1)-LEN(B1)-1)
4. Enter the following formula in column D1:=CONCATENATE(B1,', ',C1)
5. Copy the contents of cells B1, C1 and D1 down for as many rows as you have names. For example, if your name list goes to row 200, make sure you copy the contents of cells B1, C1 and D1 down through B200, C200 and D200.

How to Do Fractions in Microsoft Word

1. Place the cursor in the document where you would like to insert a fraction.
2. Click the 'Insert' tab above the Ribbon. Notice that the options in the Ribbon change.
3. Click the 'Equation' button in the Ribbon. New options will appear.
4. Select 'Fraction' from the Structures section.
5. Choose the desired fraction type from the resulting drop-down menu. An equation box will appear where your cursor is.
6. Type the fraction's denominator and numerator into the equation box. Click outside of the box when finished.

How to Split a Cell in Excel 2007

1. Open the Excel document that has the cell you need to change.
2. Click the 'Home' tab in the Office ribbon at the top of the screen.
3. Click on the merged cell you want to split. The button labeled 'Merge Center' in the alignment group is highlighted in orange by default, indicating that you've selected a merged cell.
4. Click the 'Merge Center' button to split the cell into separate cells.

How to Unprotect an Excel Spreadsheet

1. Open the Excel workbook with the worksheet you want to unprotect.
2. Right-click on the worksheet's tab at the bottom of the window.
3. Select 'Unprotect sheet.'
4. Enter the password, if applicable, and press 'Enter.'

Sunday, October 28, 2012

How to Insert a PDF File Into Excel 2003

1. Open the Excel file that you want to place the PDF into.
2. Click on the cell you want to insert the PDF on. The upper left corner of the PDF will align against the upper-left corner of the cell you have selected, although you can move it later if you want.
3. Click the 'Insert' menu from the top the Excel window.
4. Select 'Object.'
5. Click on the 'Adobe Acrobat Document' in the 'Object type' section and then click the 'OK' button.
6. Navigate to the PDF that you want to insert.
7. Click the PDF and then click the 'Open' button. Your computer will open the PDF in Adobe Reader and insert it into your spreadsheet.

How to Use the Range.sort Method in VBA

1. Open the Excel 2010 spreadsheet that you want to sort using VBA. Press 'Alt' and 'F11' simultaneously to bring up the VBA editor.
2. Double-click on the module, which is located on the left side of the editor, where you are writing your VBA code. Your code will appear on the right side of the screen. Place your cursor into an empty line of code where you want to use the Sort method.
3. Enter the following code into the VBA editor, without quotes: 'Range('A:B').Sort key1:=Range('A1'), Order1:=xlAscending'. Change 'A:B' to the Excel range that you want to sort and change 'A1' to the first cell in the column you want to use to sort by. Change 'xlAscending' to 'xlDescending' if you want to sort from high to low, instead of low to high.
4. Place a comma and a space at the end of your code if you want to add more parameters to the sort method. Enter the name of the parameter, followed by ':=' and then the value for the parameter. Place another comma and a space after each parameter, except for the last one.
5. Use the 'Key 2,' 'Order 2,' 'Key 3' and 'Order 3' parameters to set up additional sorting rules for when the first key find identical values. Use the 'Type' parameter to determine whether to sort the range by labels or values, and use 'xlSortLabels' and 'xlSortValues,' respectively. Use the 'Header' parameter, set as either 'xlYes,' 'xlNo' or 'xlGuess' to let the sort function know if your data has headers or not.Use the OrderCustom parameter, using an integer as the value, to use a preset custom sort order that you created. Use the 'MatchCase' parameter, setting it to either 'True' or 'False' to determine whether to make the sort case sensitive. Use the 'Orientation' parameter, set as either 'xlSortRows' or 'xlSortColumns' to determine how your data table is orientated. Use the 'SortMethod' parameter, set as 'xlStroke' or 'xlPinYin' if you are sorting Chinese characters. Finally, use the 'DataOption1,' 'DataOption2,' or 'DataOption3' parameters, set as 'xlSortTextAsNumbers' or 'xlSortNormal' to determine whether the respective key will have its text be treated as numbers for the sort.

How to Reverse the Order of a Chart in Excel 2003

1. Click the axis for the categories, series or values that you want to change the order of.
2. Click the 'Format' tab, click 'Selected Axis' and then click the 'Scale' tab.
3. Check 'Categories in Reverse Order,' 'Values in Reverse Order' or 'Series in Reverse Order.'

Subscribe to: Posts (Atom)