Excel

Browse » Home » All posts

Monday, June 11, 2012

How to Create a Regression Equation Chart in Excel 2003

1. Input your data into your spreadsheet in two columns. For example, use fertilizer use and crop yield. Make sure the data 'lines up' -- that the fertilizer use and crop yield of plot A are in the same row; the same for plot B and so on.
2. Select the height and weight data that you want to graph and use the Chart Wizard to plot the data. The dependant variable, the one that you expect is affected by the other variable (in this case crop yield), should be on the y-axis.
3. Right-click on one of the data points on the graph and select 'Add trendline.'
4. Select the 'Linear' trend/regression type and then click on the 'Options' tab.
5. Tick the box for 'Display equation on chart' and click 'OK.'
6. Move the text box containing the equation to somewhere clear of data points to improve the clarity of the graph.

How to Add a Data Table to an Excel Chart

1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart to which you would like add a data table.
2. Select the chart you would like to add a data table to by clicking on it. You should see a light blue border surrounding the chart once it is selected.
3. Choose the 'Layout' tab at the top of the Excel 2007 screen to display the options in the 'Layout' ribbon. Find the 'Labels' group near the center of the groups in the 'Layout' ribbon.
4. Click the 'Data Table' button in the 'Labels' group of the 'Layout' ribbon. A drop-down list of options will appear.
5. Use the 'Show Data Table' from the drop-down list to display a data table for the selected chart. Choose 'Show Data Table with Legend Keys' to display a data table that includes a legend key to help users read data. Whatever data table you choose will be displayed at the bottom of the selected chart.

How to Convert Mailing Labels to Columns in Excel

1. Open the Word document containing the mailing labels. In Word 2003 or earlier, go to the 'File' menu, select 'Open,' click on the document name and click the 'Open' button. In Word 2007, click the 'Office Button,' select 'Open,' click on the document name and click the 'Open' button. In Word 2010, click the 'File Tab,' select 'Open,' click on the document name and click the 'Open' button.
2. Select all of the information in the labels by clicking the 'Table Move Handle' at the top left side of the table. This handle looks like crossed, double-headed arrows. Because Word views the label document as a table, clicking this button will highlight and select the entire document. You can also select all of the information on the tables by clicking anywhere in the document and pressing the keyboard shortcut 'Ctrl A.'
3. Copy the selected information by clicking the 'Copy' button on the 'Standard' toolbar in Excel 2002 or 2003 or in the 'Clipboard' group of the 'Home' tab in Excel 2007 or 2010. You can also use the keyboard shortcut 'Ctrl C.'
4. Open the Excel worksheet where you want to convert your mailing labels. Click inside the top cell of the first column into which you want to paste the data. Although you are only clicking inside of one cell, Excel will convert the data into the specific number of columns and rows required, saving you from having to count the number of cells into which you need to paste the data.
5. Click the 'Paste' button on the 'Standard' toolbar in Excel 2002 or 2003 or in the 'Clipboard' group of the 'Home' tab in Excel 2007 or 2010. You can also use the keyboard shortcut 'Ctrl V.'
6. Click the 'Paste Options' button that appears as a small clipboard at the bottom right side of the pasted data. Select 'Keep Source Formatting' if you want the converted information to match the formatting used in the Word labels. Select 'Match Destination Formatting' if you want the formatting you are using in your Excel worksheet applied to the data.

How to Use Excel for Optimization Calculations

1. Compute an equation for the optimization. For example, suppose the problem is to create a fence that encloses 100 square feet while using the least amount of fencing. Then, the two equations would be 'area = length * width' and 'total fence = 2*l 2*w.'
2. Enter the formulas into Excel. Enter an example length of 25 feet in box 'A1.' Enter an example width of 4 ft in box 'A2.' Type '=A1*A2' into box 'A3.' Box 'A3' is the area parameter. Type '=(2*A1) (2*A2)' into box 'A4.' Box 'A4' is the perimeter which is to be optimized.
3. Select 'Solver' from the 'Tools' menu.
4. Enter the value to be optimized into the 'Target Cell' box. Type 'A4' into the 'Target Cell' box, as this is the perimeter to be minimized. Click on the 'Min' dial.
5. Enter the values that can be changed into the 'By Changing Cells' box. Type 'A1, A2' into the 'By Changing Cells' box, as the length and width are the parameters that can be changed.
6. Enter the problem constraints into the 'Constraints' box. Type 'A3 = 100' into the 'Constraints' box, as this is the constraint of the problem.
7. Press 'Solve.' In this example, the length and width would both be 10 feet, to produce a minimum fence length of 40 feet.

How to Use Minutes Seconds in Excel

1. Select and highlight the group of cells in your spreadsheet (such as a column or a row) where data will be entered as time in minutes and seconds.
2. Select 'Cells' under 'Format' in the top menu bar.
3. Click the 'Number' tab and select the 'Custom' option from the list of categories on the left.
4. Select 'h:mm:ss' from the list of custom format or type 'h:mm:ss' in the field under 'Type.'
5. Click the 'OK' button.
Calculating Time
6. Select the cell that will be used to calculate the total number of minutes and seconds entered.
7. Type the following part of the formula, '=sum(' then select the first cell containing the data.
8. Type ' ' and then select the next cell containing the data. Repeat this step to select the cells to be calculated.
9. Type ')' to complete the formula and press the 'Enter' key.

How to Create Invoice Templates in MS Excel

1. Open Excel. Click on the 'Office' button. Select 'New.' Choose 'Blank and recent' from the 'Templates' pane. Click 'Blank Workbook' in the middle pane. Click 'Create' Button.
2. Select column A by clicking at the top of the column with the mouse. Hold the 'Shift' key while clicking the mouse on column F. Right-click on the selection. Set 'Column Width' to 15.
3. Click in cell A1. Select 'Home' from the menu bar. Choose 'Cell' and 'Format' from the toolbar. Set the 'Row Height' to 58.
4. Choose 'Insert' from menu bar. Select 'Picture' or 'Clip Art' from toolbar. Choose and insert picture or clip art.
5. Right-click on the picture with the mouse. Select 'Size and Properties.' Set 'Height' for the picture in the 'Size' tab under 'Size and Rotate.' Check 'Lock Aspect Ratio' under 'Scale.' Click 'Close' button.
6. Highlight columns B, C and D. Select 'Merge and Center' from 'Alignment' group on the 'Home' tab. Type your company motto.
7. Highlight columns E and F. Select 'Merge and Center' from 'Alignment' group on the 'Home' tab. Type your company motto. Type 'Invoice' in cell E1.
8. Type your company name, address, city, state, ZIP code and contact numbers in cells A3 to A6. Type: 'Invoice:' in cell D3; 'Date:' in cell D4; and 'To:' in cell A9.
9. Type 'Payment Terms' in cell A15 and 'Due Date' in cell B15. Select and highlight these cells. Right click on selection. Select 'Format Cells.' Choose a light gray color from 'Background Color' area under 'Fill' tab. Click 'OK' button.
10. Type: 'Quantity' in cell A18; 'Description' in cell B18; 'Unit Price' in cell C18; and 'Line Total' in cell D18. Select and highlight these cells. Right-click on the selection. Select 'Format Cells.' Choose a light gray color from the 'Background Color' area in the 'Fill' tab. Click the 'OK' button.
11. Select and highlight from cells A18 to F38. Right-click on the selection. Choose 'Format Cells.' Under the 'Border' tab, set 'Color' to 'Automatic.' Click on 'Outline' and 'Inside' boxes under 'Presets.' Click the 'OK' button.
12. Type: 'Subtotal' in cell E39; 'Sales Tax' in cell E40; and 'Total' in cell E41.
13. Select and highlight from cells E38 to F40. Right-click on the selection. Choose 'Format Cells.' Under 'Border' tab, set 'Color' to 'Automatic.' Click on 'Outline' and 'Inside' boxes under 'Presets.' Click 'OK' button.
14. Highlight and select cells E19 to F40. Right-click on the selection. Choose 'Format Cells.' Select 'Currency' from 'Category' pane under 'Number' tab. Click the 'OK' button.
15. Type '=A19*E19' in cell F19. Copy and paste formula from F19 to cells F20 through F37.
16. Type '=SUM(F19:F39)' in cell F40. Save file as an Excel Template (*.xltx).

Monday, May 28, 2012

How Can I Put a Last Updated in My Excel Workbook?

Create a User-Defined Function
1. Open the workbook and go to the sheet where you will be inserting the last updated date.
2. Click on 'Tools,' 'Macro' and 'Visual Basic Editor,' or just press the 'Alt' and 'F11' key at the same time.
3. Click on 'Insert' and select 'Module.' Your cursor will automatically be placed at the beginning of the open module.
4. Type (or copy and paste) the following user-defined function command into the module:Function SavedDate() As Date
Application.Volatile
SavedDate = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
End Function
5. Click on 'File' and 'Close and Return to Microsoft Excel.'
Insert Last Updated Date
6. Go to the cell in your worksheet where you want to display the last updated date.
7. Type, or copy and paste, the following function:=SavedDate()
8. Press 'Enter.'
9. Right-click on your cell and select the 'Format Cells' menu.
10. Format your date by selecting the 'Date' category and choosing how you want your date to appear from the'Type' option. Click 'OK.'

How to Cross Reference Between Excel Spreadsheets

1. Launch Microsoft Excel and open the spreadsheet that is going to display information contained in other files.
2. Open each of the other files that has information you want to reference in the display spreadsheet.
3. Switch back to the display spreadsheet and click once in the cell that is going to display information from another spreadsheet.
4. Press the 'Equal' ('=') key on the keyboard and immediately switch to the spreadsheet that contains the first piece of information that needs to be displayed.
5. Click the cell that contains the information that will be displayed in the display spreadsheet and press 'Enter.' The display spreadsheet now contains a reference to the information contained in the other spreadsheet, and this information will automatically update when changes are made to the data.

How to Import PDFs Into Excel 2007

1. Open Adobe Acrobat Reader. Click 'File' and select 'Open File.' Select the file that you want to import into Excel.
2. Press and hold the 'Shift' and 'V' keys on your computer. This opend the Table/Formatted Text tool.
3. Click and drag a box around the table in the PDF that you want to import. This highlights the table. Select 'Save As...' from the window that appears.
4. Type a name for your new file. Click the drop-down menu next to 'Save As Type.' Choose 'ANSI Text' from the list. Choose a destination folder for your new file and click the 'Save' button.
5. Open the Excel 2007 program. Click 'File' and select 'Open.' Navigate to find the ANSI file you've just saved. Click the 'OK' button. Click the 'Delimited' option in the new window and then click the 'Next' button. The table appears in an Excel spreadsheet as it appeared in your PDF document.

How to Freeze the Top Rows in an Excel 2007 Document So They Appear at the Top of Every Page

1. Open the Excel 2007 spreadsheet.
2. Identify the row or rows at the top of the spreadsheet that you wish to freeze. Often just the first row is needed for this. You may choose multiple rows if desired.
3. Click in the first row of the spreadsheet that is not to be frozen. That is, click any cell in the first row at the top of the spreadsheet that will be a part of the rows that will scroll. All other rows above this row will become frozen.
4. Click the 'View' tab at the top of the Excel 2007 ribbon bar.
5. Identify the 'Window' group on the 'View' tab. Click the menu arrow that appears under the 'Freeze Panes' button. A menu will appear.
6. Click the 'Freeze Top Row' option if you are only freezing one row. If freezing multiple rows at the top of the spreadsheet, choose the 'Freeze Panes' option. The rows are now frozen.
7. Click the same menu and choose 'Unfreeze Panes' when you wish to return the spreadsheet to its initial state.

Sunday, May 27, 2012

How to Calculate a Moving Average in Excel

1. Open a new worksheet in Microsoft Excel. Enter dates and their corresponding data points in two columns. For instance, to analyze monthly revenue figures, enter each month in column A and the corresponding revenue figure next to it in column B. A year's worth of data, then, would fill cells A1 through A12 and B1 through B12.
2. Determine the time interval of the moving average you want to calculate, such as a three-month or six-month moving average. Go to the last value of the first interval and click on the corresponding empty cell to the right. Using the example from Step 1, if you want to calculate a three-month moving average, you would click on cell C3 because B3 contains the last value of the first three months of the year.
3. Use the AVERAGE function and type a formula into the empty cell you selected, specifying the data range for the first interval. In this example, you would type '=AVERAGE(B1:B3)'.
4. Position your mouse on the lower right corner of the cell with the formula until you see a ' .' Left click and drag the formula down to the empty cell next to the last data point in the adjacent column. In the example above, you would drag the formula from cell C3 down to cell C12 to calculate the three-month moving average for the rest of the year.

How to Convert Open Office Files to Microsoft Excel

1. Download and install the Open Office suite if it isn't already on the computer.
2. Open 'Open Office Calc.'
3. Click the 'Open' icon (folder that's open) at the top of the program.
4. Choose the Open Office file you want to convert to Microsoft Excel and click 'Open.'
5. Click the 'File' menu and choose 'Save As' and a window will appear.
6. Select the drop box labeled 'Save as type,' and choose one of the listed types of Microsoft Excel formats. The format you select will be determined by the version of Microsoft Excel you plan on using to open the converted file (such as Microsoft Excel 5.0 or Microsoft Excel 95).
7. Rename the file in the 'File name' box if you wish, and then click 'Save' to convert the file.

How to Plot a Scatter Chart in Excel 2007

1. Enter labels into the first row of two columns and enter the data beneath it.
2. Click and drag the mouse over all of the data you entered into your spreadsheet to highlight it, including the labels.
3. Click the 'Insert' tab on the menu. Click 'Scatter' in the Charts section and select one of the five scatter chart types. Excel creates the scatter chart and displays it on the spreadsheet.

How to Close MS Excel

1. Navigate to the 'File' tab in the top left-hand corner of the Excel program. Click on 'File' and click on the 'Save As' tab underneath 'File.' In the pop-up window, type in the name of the workbook and navigate to the location where you want it saved. Click 'Save.' This will ensure that none of your information is lost.
2. Point your mouse cursor to the upper right-hand corner of the Excel program and hit the 'X' symbol to close out your workbooks. Do this with each open workbook.
3. Navigate your cursor to the very upper-left hand corner of the Excel program and click on the Excel symbol. Scroll down on the menu and click on 'Close' or 'Exit Excel.'

How to Change a Cell to 26Pt in Microsoft Excel

1. Open Excel 2007. Click the 'Office' button and select 'Open.' Browse your computer for the spreadsheet you wish to revise; after you locate it, click on it. Click the 'Open' button and the document will open.
2. Click a cell, then click the 'Home' tab. Select the 'Font' group and click the 'Font Size' drop-down box. Change the 'Font Size' to 26; the type in the cell is adjusted to 26 points.
3. Save your changes by clicking the 'Save' icon in the Quick Access Toolbar.

Subscribe to: Posts (Atom)