Excel

Browse » Home » All posts

Tuesday, April 12, 2011

How to Resize a Chart in Excel

1. Start Microsoft Excel 2007 and open an existing workbook that contains a chart or create a chart from existing Excel data.
2. Click on the chart you want to resize so it is selected. A light blue outline will surround the chart when it is selected.
3. Point to one of the corners or edges of the selected chart where you see three small dots. Your mouse will turn into a double-headed arrow when you hover over the areas that you can resize.
4. Click with your mouse on the edge of the chart where you see the three areas and drag the mouse either outward (to make the chart larger) or inward (to make the chart smaller.)
5. Release the mouse button when you are happy with the resizing you have just completed. The chart will now be resized as you specified.
6. Continue to click and drag the sides and corners of the Excel chart until you get it exactly the size you want.

How to Delete a Drop Down Box in Excel

Remove the Drop-Down Only
1. Select the cells that have drop-down boxes.
2. Click the 'Data' tab and choose 'Data Validation.'
3. Remove the check in the box labeled 'In-cell dropdown.' Click 'OK.'
Remove Data Validation
4. Select the cells that have drop-down boxes.
5. Click the 'Data' tab and choose 'Data Validation.'
6. Select 'Any value' under 'Allow.' Click 'OK.'

How to Create a Floating Bar Chart in Microsoft Excel 2003

1. Set up your spreadsheet data with at least two rows of data. One of the rows of data will be what you actually want to show in the graph, and the other data set needs to have values that are less than the data set you want to show.
2. Highlight the data set. Click on the 'Insert' menu at the top of the screen, and then click on 'Chart' to bring up the chart options.
3. Click on the 'Bar' option from the list of chart type options on the left-hand side to bring up all of the bar chart options. Click on the 'Stacked Bar Chart' option, click 'Next' twice, and then click 'Finish' to build chart in the spreadsheeet.
4. Click on the lower series of data in the chart to select it. Right-click, and click on the 'Format Series' command to bring up the Series options window.
5. Click on the 'Patterns' tab. Select 'None' under Border and Area, and Click 'OK.' The upper series should be the only part showing on your graph, and it should be 'floating' above the axis.

How to Solve Linear Programming in Excel

1. Set up the linear program in the form:Maximize c(transpose)xSubject to: Ax ≤ b, x ≥ 0where c, x, A, and b are matrices. The objective function can also be minimized or equal to some number z. The constraints are in linear form. X does not have to have a non negative constraint. These differences in the linear program depend on the specific problem. However, it is imperative that the linear program be set up correctly. Be sure to make all calculations for the cTx, Ax, and b matrices in Excel before you solve the linear program. You can begin by either setting all values of x to 1 or leaving them unknown. It can be helpful to name the cells by clicking 'Insert' in the toolbar, 'Name,' and 'Define.' The names of the cells can by typed into Solver directly.
2. Open Solver and input the necessary cells. In order to input a cell, click on the Excel icon to the right of the text box, and then click on the desired cell. The 'Set Target Cell:' is the objective function. 'By changing Cells:' are the variables in your linear program, which is the x matrix. Click on 'Add' to add a constraint. The cell reference is the Ax matrix. Choose the type of constraint (greater than or equal to, less than or equal to, or equal to) from the pull down menu. The constraint is the b matrix. If x is non-negative, add this constraint for each x value.
3. Choose a correct linear model from the 'Select a Solving Method:' pull down menu. Standard form linear programs generally use a LP Simplex solving method. If x has a non-negative constraint, check the box 'Make Unconstrained Variables Non-Negative.'
4. Solve the linear program by clicking on 'Solve.' Allow Solver to think for a moment. If Solver finds a solution a dialogue box with the title 'Solver Results' will pop-up. You are given the choice of keeping the solver solutions or restoring all cells to their original value.

How to Sort by Cell Color

1. Click the first cell in the range of cells you want to sort. Drag down and across to highlight each of the cells that needed to be sorted by their color.
2. Navigate to the tab labeled 'Data.' Click the 'Sort' button at the left side of the 'Sort and Filter' grouping.
3. Click the 'Column' drop-down box underneath the 'Sort By' heading. Select the column in the range you want to sort.
4. Select the 'Sort On' drop-down menu and choose 'Cell Color.' Click the 'Order' drop-down menu and select the color you want to sort by, such as 'Green' or 'No Cell Color.'
5. Click the final drop-down menu and choose either 'On Top' or 'On Bottom' to specify where the sorted cells should be arranged. Click 'OK' to sort the range of cells by their color.

Monday, April 11, 2011

How to Create a Loan Amortization Table in Excel

1. Download the free Amortization Schedule for Excel 1.6 spreadsheet program available by Vertex42 and CNET. The program is designed for the layman and makes it easy to create a loan amortization table that quickly visualizes the relationships between a loan's principal, its time period and interest rate. Once created, the table may be changed by adjusting any of these variables on-the-fly. The flexibility of this template makes it easy to see how the loan is affected by the occasional extra payment. It also demonstrates the consequences of a missed payment. The table supports all the popular payment schedules, from weekly to annual payments. It optionally allows payment information to be rounded.
2. Download a Microsoft template for loan amortization. The company that makes Excel also makes its own amortization tables available to users. These are provided for free from the Microsoft Office website. The template takes advantage of Microsoft's own financial formulas built in to the Excel program. The variables for a fixed interest rate, payment schedule and loan principal are input and the template creates an interactive amortization table to show all the relationships. In addition to the table itself, the Microsoft template also creates a loan summary section where the overall parameters and outcomes of the loan are spelled out. This includes the total amount of interest paid after the loan cycle completes.
3. Buy a more robust loan amortization package from a company such as the Spreadsheet Store, which provides a set of six Excel loan analysis spreadsheets for a fee of $25 as of March 2010. While all loans have similar parameters, the Loan Calculator for Excel package creates an amortization schedule for many different scenarios for added flexibility to nearly any loan situation. Some loans follow a 'balloon' style which requires a large lump sum at the end of the payment term. Other loans break the loan cycle up into a graduated repayment plan where the initial round of payments cover the interest only. Still other loans do not use a fixed interest rate and thus the payment sizes can change periodically. This package of spreadsheets covers all these loan situations and more so the user is ready to visualize any credit opportunity with greater clarity. In addition to the loan amortization tables, these spreadsheets also create graphs that depict the loan parameters in a particularly visual layout.

How to Create Tabs in an Excel Spreadsheet

1. Open Microsoft Excel. There will be multiple tabs at the bottom with the name 'Sheet' and a number for each tab. Each of these tabs is a separate worksheet. Click on the tab to open that worksheet.
2. Right-click on the tab and select 'Rename' to change the name on the tab.
3. Add tabs to the file by right-clicking on a tab and selecting 'Insert.'
4. Copy an existing worksheet as a new tab by right-clicking on a tab and selecting 'Move or Copy.' Choose where to place the new tab and select the copy check box to leave the original tab in its location.
5. Rearrange tabs by dragging them to a new location among the tabs.
6. Delete unused worksheet tabs by right-click on the tab for the worksheet that you do not want and selecting 'Delete.'

How to Paste Special in Microsoft Word

1. Start Microsoft Word and open an existing file or start a new document.
2. Copy the text you want to paste from another Word file or other application.
3. Place your cursor in your open Word document where you want to paste the copied text.
4. Right-click and choose 'Paste' from the shortcut menu. You can also use the keyboard shortcut of CTRL V to paste the text into the new location. After you paste, a clipboard will appear at the end of the pasted text.
5. Hover your mouse over the clipboard until an arrow appears to the right of the clipboard.
6. Click on the clipboard arrow to show a drop-down menu. These are your paste special options.
7. Choose 'Keep Source Formatting' to keep the formatting that was in the original document. 'Match Destination Formatting' matches the formatting of the pasted text to the surrounding formatting within the document to which you are pasting. 'Keep Text Only' pastes the text you have copied while ignoring any type of formatting.

Monday, March 28, 2011

How to Prevent Excel 2003 From Automatically Converting Dates to Julian

1. Log on to your computer and open Microsoft Excel 2003. Open a new or existing spreadsheet.
2. Click the 'Format' menu and choose 'Cells.' Highlight the 'Date' option.
3. Click the date format you want to use and click 'OK.' Enter a date in the cell to make sure it displays properly.

How to Convert Excel 2003 AutoFormat PivotTables to Excel 2007

1. Open Excel 2007.
2. Click 'File,' 'Open.' Double-click the Excel 2003 workbook that contains the PivotTable. The workbook opens in Compatibility Mode.
3. Click the Microsoft Office Button in the upper-left. Click 'Convert.' Click 'OK' to confirm.
4. Click 'Yes' and the newly converted workbook will open and is ready to use.
5. Click 'File,' 'Save' to confirm the new settings.

How to Write Sideways in Excel

1. Open Excel to a blank spreadsheet or open an existing spreadsheet.
2. Click a cell you want to add text to. Click the 'Home' tab on the ribbon if it is not already open. Click the 'Text Orientation' button in the Alignment group. The button has no name but shows the letters 'ab' with an arrow underneath pointing diagonally.
3. Select the type of rotation you want. You can select 'Vertical text' to have letters appear vertically one at a time in your cell or select 'Rotate up' or 'Rotate down' to face your text left or right.
4. Type the text as you would normally. Press 'Enter' to see your text align sideways.

How to Print Head Rows on Each Page in Excel

1. Click 'Insert.'
2. Select 'Header and Footer.'
3. Click on the left, center, or right header, depending on which one you want to use.
4. Type in your header.
5. Print your spreadsheet and the header will display on every page.

Sunday, March 27, 2011

How to Insert Time Into an Excel Spreadsheet

Static Time
1. Select the cell where you wish to enter the time.
2. Press 'Ctrl' 'Shift' ';'.
3. Click the 'Format' menu and select the 'Cells' option.
4. Click the 'Numbers' tab.
5. Select 'Time' from the drop-down list and select your preferred format in the second column. You can also create a custom display format. When you're finished, click 'OK.'
Dynamic Time
6. Select a cell in your spreadsheet and type '=NOW()' into the cell.
7. Press 'Enter.' The time and date are displayed in the default format.
8. Change the format by following steps 3 to 5 in Section 1.

How to Add a Column Number in Microsoft Excel 2003

1. Choose a cell on your spreadsheet to display the result of your formula.
2. Click the 'Insert Formula' button next to the formula bar.
3. Select 'Lookup and Reference' from the drop-down list of function categories.
4. Select 'COLUMN' from the list of functions and click 'OK'.
5. Provide a reference for the 'COLUMN' formula to use. If you want to use the cell you selected in Step 1, do not enter a reference; if you want to use a different cell, enter that cell's coordinates in the reference box.
6. Click the 'OK' button. Your cell will now display the numerical value of the column you selected.

How to Calculate Linear Regression Using Excel

1. Type these data pairs starting in cell c3. For this and the remaining steps, press the 'Tab' key where a comma is shown. These numbers are data pairs collected from a hypothetical science experiment. In this experiment, assume there's the possibility of a linear relationship between 'x,' the first column of numbers, and 'y,' the second column.X, Y1, 5.22, 7.83, 10.74, 13.95, 16.5
2. Type these additional three columns, starting with the first cell to the right of the cell containing 'y.' These columns are factors in the calculations for the slope, y-intercept and R values of linear equations of the form y = mx b. Letter 'm' is the slope, 'b' is the y-intercept and 'R' is a measure of how closely the computed line matches the actual data points. The closer 'R' is to 1.0, the closer the data points are to forming an actual line whose 'm' and 'b' values are those you're computing.xy, x^2, y^2c4*d4, c4*c4, d4*d4
3. Select the second row you just typed, then click the lower-right corner of the rightmost cell. Drag downward until the selection is five rows high. This action extends the formulas to all the x-y data pairs.
4. Type these additional six cells starting at cell b11. These cells contain summations of the columns you entered in the previous step.n, sum of x, sum of y, sum of (xy), sum of (x^2), sum of (y^2)count(c4:c9), sum(c4:c9), sum (d4:d9), sum (e4:e9), sum (f4:f9), sum(g4:g9)
5. Type these formulas starting in cell c14. These are squares of two of the summation calculations you entered in the previous step.(sum of x)^2, (sum of y)^2c12^2, d12^2
6. Type these labels and calculations starting in cell c17. These are the slope, y-intercept and 'R' values of the estimated line, as described in step 2. After entering these final calculations, look first at the 'R' value, 0.9994. This number is close to 1.0, which means the line you calculated is close to fitting the data points. Next, compare how close the slope, 2.87, is with the value 3.0, which is the slope of the actual line used to create data points for this article. Last, relate the y-intercept value, 2.21, to the value 2.0, which is the y-intercept of the linear equation used to create data points for this article.slope, (B12*E12-C12*D12)/(B12*F12-C15)y-intercept, (D12-D17*C12)/B12R, (B12*E12-C12*D12)/SQRT((B12*F12-C15)*(B12*G12-D15))

Subscribe to: Posts (Atom)