How to Change Decimal Formatting Placement Within Cells in Microsoft Excel 2003

1. Open and access your spreadsheet file by clicking on the “File” tab and scrolling to “Open.”
2. Activate the cell or cells in which you wish to change the decimal formatting placement. To activate the cells that you wish to change, either left-click on a single cell or left-click and hold as you drag the cursor over multiple cells to highlight them.
3. Open the cell properties box. To do this, right-click on the cell or cells that you wish to change the properties for. A sub-menu will open. Select “Format Cells.”
4. Change the cell decimal formatting placement. Under the cells properties box, left-click on the “Number” tab. Under the category drop-down menu left-click on the “Number: selection; a “Decimal Places” box will open inside of the cells properties box to the right. Use the up/down arrows to change the decimal places. Click “Okay” to implement changes.

Read more ►

How to Calculate a Weighted Average in Excel 2007

1. Enter your two sets of values that you wish to find the weighed average for into your spreadsheet, separating them into two columns. For the sake of this example, assume that the values in the first column stretch from A1 to A5 and the values in column B stretch from B1 to B5.
2. Type the following formula into a blank cell, cell C1, without quotes: '=SUM(B1:B5)' This formula will calculate the sum of the second column of numbers.
3. Type the following formula into another blank cell, cell C2, without quotes: '=SUMPRODUCT(A1:A5,B1:B5)' This formula calculates the total amount paid into the first and second columns.
4. Type the following formula into a final blank cell, cell C3, without quotes: '=C2/C1' This calculates the total weighted sum for the first column when compared to the second column.

Read more ►

How to Use Percentages in MS Excel

1.
Use MS Excel to display decimals as percentages.
Format a cell to display a percentage. In Excel, type a decimal value into cell A1, such as 0.3. Press 'Enter.' On the 'Home' tab of the ribbon, in the 'Number' section, click '%' to display 30%.'
2.
Calculate percentages by dividing an amount into the total.
Calculate a percentage. If you have 100 boxes and 12 of them are red, find the percentage of red boxes by dividing the amount,12, by the total, 100. In Excel, enter the following:Cell A1: 12
Cell A2: 100
Cell A3: =A1/A2In cell A3, press 'Enter' to view the result, 0.12. Format cell A3 as a percentage---as in Step 1---to display '12%.'
3. Figure the amount of a percentage. If you are buying a camera for $150 and you want to find how much a 5 percent sales tax will be, multiply the total, 150 by the decimal that represents the percentage (divided the percent, 5, by 100), 0.05. In Excel, enter the following:Cell B1: 150
Cell B2: .05
Cell B3: =B1*B2In cell B3, press 'Enter' to see the result, 7.5. To show the value as a dollar amount, format cell B3 as currency. On the 'Home' tab of the ribbon, in the 'Number' section, click '$' to display '$7.50.'
4.
Use Excel to calculate a discount or an original price.
Find the original amount from a total that has been reduced by a percentage. If you are shopping for shirts at 30 percent off and the reduced price is $21, calculate the original price by dividing the discounted price, 21, by the difference between 100 percent and the 30 percent discount (1 minus 0.3), in this case 0.7. In Excel, enter the following:Cell C1: 21
Cell C2: .7
Cell C3: =C1/C2In cell C3, press 'Enter' to see the original price, 30. Format cell C3 as currency to display '$30.00.'
5.
You can use percentages to figure dollar amounts.
Increase a total by a percentage. If you get a 3 percent raise and your salary is $48,000, figure your new salary by multiplying your current yearly wages, 48,000, by 1 plus the percentage of the increase (1 plus 0.03), 1.03. In Excel, enter the following:Cell D1: 48000
Cell D2: 1.03
Cell D3: =D1*D2In cell D3, press 'Enter' to see your new salary, 49,440. Format cell D3 as currency to display '$49,440.00.'
6.
MS Excel can help with budgets and accounting tasks.
Decrease a total by a percentage. If your monthly entertainment expenses are $100 and you want to reduce expenditure by 5 percent, multiply your current budget, 100, by 1 minus the percentage of the decrease (1 minus 0.05), or 0.95 in this example. In Excel, enter the following:Cell E1: 100
Cell E2: .95
Cell E3: =E1*E2In cell E3, press 'Enter' to view your revised entertainment budget, 95. Format cell E3 as currency to display '$95.00.'

Read more ►

How to Get to VBA in Excel 2007

1. Click the Microsoft Office icon at the top-left corner of the Excel spreadsheet. Select the 'Excel Options' button at the bottom of the drop-down menu.
2. Navigate to the 'Popular' tab at the left side of the window. Locate the 'Top Options For Working With Excel' heading.
3. Check the check box labeled 'Show Developer Tab In The Ribbon' underneath the 'Top Options' heading. Click 'OK.'
4. Click the 'Developer' tab at the top of the screen. Select the 'Visual Basic' option underneath the Microsoft Office button to bring up the VBA screen.
5. Double-click the 'Sheet1' entry at the left side of the VBA window to bring a blank screen. Type your VBA code into the blank area. Click on the other sheet options if you want to add VBA code to the other sheets in your Excel workbook.

Read more ►

How to Center a Worksheet Horizontally in Excel 2007

1. Select the Office Button and click 'Open.' Double-click the file you want to modify in the 'Open' dialog box. It loads on your page.
2. Select the worksheet you want to align in the workbook. To center each worksheet in your file, right-click on any worksheet tab (at the bottom of your page) and choose 'Select All Sheets' from the pop-up list.
3. Select the 'Page Layout' tab. Click the arrow on the right of the 'Page Setup' group.
4. Select the 'Margins' tab in the 'Page Setup' dialog box.
5. Go to the 'Center on page' section and check 'Horizontally.' Click 'OK.'

Read more ►

How to Put a Picture in the Background on an Excel Spreadsheet

Microsoft Excel 2003
1. Open your spreadsheet. Click on the tab labeled 'Format.'
2. Scroll over 'Sheet,' and then select 'Background.'
3. Browse through your pictures to find the one you want to add. Click on the file name, then click 'Insert.'
Microsoft Excel 2007
4. Open your spreadsheet. Click on 'Layout' in the top menu bar.
5. Click 'Background,' and then browse through your pictures.
6. Select the picture you want to set as the background and click 'Insert.'

Read more ►

How to Create a Drop

1. Click in the cell that you want to have a drop-down 'pick' list. Select the 'Data' tab. In the Data Tools group, click 'Data Validation.' The Data Validation dialog box appears.
2. Select the 'Settings' tab. In the Allow box, select 'List.'
3. If you have the items typed in a range (one word per cell, either across or down) and in the same worksheet, then enter the range in the Source box (or click the icon to select the range from the worksheet). Alternatively, type the items in the Source box, separating them by a comma. When you are finished, click 'OK.'
4. Navigate to your worksheet. You will see a blank cell with a drop-down arrow. When you click the drop-down arrow of the box, a drop-down will display where you can select the desired response.

Read more ►

How to Create a Run Chart

1. Enter column headers. In Excel, put 'Date' in cell A1, the name of the variable you are tracking (such as weight/value) in cell B1 and 'median' in cell C1.
2. Add dates. Add the dates that you recorded data on in column A. For example, you might have 10/1, 10/2, 10/3, 10/7, 10/8. Usually, though, you will have a longer series.
3. Add the values. Add the values you observed in column B. For example, you might have 150, 151, 152, 151, 150.
4. Find the median. At the bottom of column B, click 'Formulas,' then 'More functions,' then 'Statistical' and 'Median.' Copy this number into column C next to each row that has data in column B. Then delete it from column B. In our example the median is 151, so add 151 to cells C2 through C6.
5. Add a chart. Click 'Insert,' then 'Line with markers.' This will be your run chart.

Read more ►

How to Make a Loan Amortization Table in Excel

1. Install the Amortization Schedule for Excel product by Vertex42. This is a free download through CNET. You can quickly build a table that reflects all the different parameters of a loan. While most loans are based on a set payment schedule, this Vertex42 template shows how occasional changes to the payments affects the overall loan. If a payment is missed, or the occasional payment is increased, this table reflects the consequences of these actions on the final payback amount. Simply download the file and open it in Excel, and the interface is straightforward from there. No prior financial experience or programming background is required to quickly build amortization tables.
2. Open an official Microsoft Excel template designed for amortization tables. Microsoft offers hundreds of free templates for all Microsoft Office products in a wide range of application categories. As Excel is commonly used for financial analysis, amortization templates are readily available from the Microsoft website. Excel natively supports many financial formulas and calculations relating to interest rates and scheduled loan payments. The Microsoft templates simply organize these built-in functions into an easy interface so anyone can take advantage of Excel's programming functions without having to construct formulas manually. Users who desire added functionality or custom formatting can build on the template with their own content, if desired.
3. Purchase an Excel add-in for a more comprehensive analysis of loan scenarios. The Spreadsheet Store provides an Excel loan analysis package called 'Loan Calculator for Excel.' This suite of multiple Excel worksheets covers nearly any loan scenario you will encounter. As of March 2010 the price for this package is $25. Special payment schedules are supported by the program. As many loan types do not involve a set payment amount each month, the 'Loan Calculator for Excel' may be the best option. Variable interest rates and graduated payment plans are among the situations not easily analyzed by free Excel software. This package easily manipulates these extra variables. Unlike other products, it also creates charts to more effectively display the parameters of a loan.

Read more ►

How to Default to Excel 2003 and Not 2007

1. Close any open spreadsheet programs on your computer.
2. Click 'Start' in the lower left corner and then 'Documents' to open the directory containing your spreadsheet files.
3. Find a spreadsheet file that you want to associate with Excel 2003. The file will have a spreadsheet extension such as xls, csv or xlsx. Right-click the file name and select 'Properties' to view the file extension if it is not otherwise visible.
4. Right-click the file name and scroll to 'Open with' in the dialog box that appears. Then click 'Choose default program' to open another dialog box.
5. Click the Excel 2003 icon to set this as the default program for opening all spreadsheet files with the same extension as you selected earlier.Click 'Browse' if the Excel 2003 icon is not visible and double-click the program's name from you directory list of available programs.

Read more ►

How to Create a Break Even Graph in Excel

1. In cell A1, type 'Fixed Cost,' and in B1 enter the dollar amount of your fixed costs. For example, the supplier of mylar balloons requires that you pay $100 membership fee to be a buyer, and you are charged that amount no matter how many balloons you buy. In that case you would type '100' into B1.
2. In cell A2, type 'Cost per Unit,' and in B2 enter the dollar amount of the cost per unit. For example, each balloon cost $1. You would enter '1' into B2.
3. In cell A3, type 'Revenue per Unit,' and in B3 enter the dollars amount of the revenue per unit. If you plan to sell your balloons at the county fair, and you know you can charge $6 per balloon, then enter '6' into B3.
4. In cell A5, type 'Units.' In cell A6, enter the number 1. Under the number one (in cell A7) enter the number 2, and continuing entering numbers until you reach 25.
5. In cell B6, type 'Cost.' In B7 type '=A7*$B$2 $A$2' without any quotes. This formula means 'Multiply the number of units by the cost per unit, then add the fixed cost.'
6. Copy B7, and paste it into every cell in the Cost column. In our example, the first cell should read '101,' and each cell should grow in value by 1, until the final value is '125.'
7. In cell C6, type 'Revenue.'In C7 type '=A7*$C$2' without any quotes. This formula means 'Multiply the numbers of units by the revenue per unit.'
8. Copy C7, and paste it into every cell in the Revenue Column. In our example, the first cell should read '6,' and each cell should grow in value by 6, until the value is '150.'
9. In cell D6, type 'Profit'. Profit is Revenue-Cost, so enter the formula '=C7-B7' in cell D7.
10. Copy that cell, and paste it into every cell in the Profit column. In our example, the first cell should read '-95' or '(95)' (meaning negative 95). The final column should read '25.'
11. Highlight the area from A6 to D30 by holding down the left mouse key and mousing over the area.
12. Click the Insert tab on the ribbon at the top of the Excel interface. Inside the 'Charts' area on the Insert tab, you'll see a 'Line' button.
13. Click that button then choose 'Stacked Line' from the sub menu. This will bring up a line chart. The break even point is the point on the chart where the profit graph crosses the cost graph.

Read more ►

How to Create a Pivot Table to Consolidate Multiple Data Ranges

1. Open the Microsoft Excel application that contains that data you want to create a PivotTable for on your computer. Click on any blank cell within your worksheet.
2. Click on the 'Data' option and then click the 'PivotTable and PivotChart Report' option for Excel 2003. Press the 'ALT', 'D' and 'P' keys at the same time for Excel 2007. The PivotTable and PivotChart Wizard will then appear.
3. Click on the 'Multiple consolidation ranges' option and then click on the 'Next' button. Click on the 'I will create the page fields' option and then click on the 'Next' button.
4. Click on the 'Collapse Dialog' option and then select the first data range you want to add from your worksheet. Click on the 'Add' button to add the data range.
5. Click on the 'Collapse Dialog' option again to select another data range that you want to add. Click on the 'Add' button again to select your second data range. You can then continue to add as many data ranges as you want.
6. Click on the '0' option below the 'How many page fields do you want?' field and then click on the 'Next' button. Select a location for your PivotTable to be placed in your worksheet and click on the 'Finish' button.

Read more ►

How to Label the Columns of Microsoft Excel 2007 with Letters

Windows version of Excel 2007 or higher
1. Open Excel. You do not need to bring up a specific file. Just click to open the program, which will automatically open a blank spreadsheet.
2. Select the Office symbol button in the top left corner of Excel to open the menu. At the bottom right, click on the 'Excel Options' button. A new window will appear.
3. Click on 'Formulas' from the menu on the left and look mid-page under the heading 'Working with Formulas.' Uncheck the first item ('R1C1 Reference Style'), and you will return to columns labeled with letters. To change back to numbers, simply check the box once again. Click on 'OK' to close the window and save your selection.
Mac version of Excel
4. Open Excel and click on the 'Excel' menu option in the furthest top left corner. Scroll down and select 'Preferences' from the menu selections. A new window will pop up.
5. Click 'General' under the 'Authoring' heading to bring up the selections in the next window.
6. Remove the check from the box beside 'Use R1C1 reference style' to return column labels to letters, instead of numbers. Select 'OK' at the bottom right to save your options and close the window.

Read more ►

How to Use Vlookup in Excel 2007

1. Open MS Excel 2007. Click on the Windows icon on the top left of the screen. Scroll to and click 'Open.' Search your computer for the file you need to find values on. Click on it to select it and click on the 'Open' button to open the file.
2. Make sure there are definite options of things to search for. For example, if you want to list the city of people who contacted your organization, but you only have the cities listed by code, you will use a VLOOKUP to look up which city corresponds to that code.
3. Build a table array which lists your options somewhere else on the spreadsheet. In the option above, list all of your city codes in one column and the corresponding cities in the column next to it.
4. Add a column in your main spreadsheet to list the values you are looking up by right-clicking on the existing column to the right and clicking 'Insert.' In this city example, your column could be titled 'City Name.'
5. Type '=VLOOKUP(' in the first cell where the values will be located. Click on the cell with the value you are looking up (The city code in this example). Type ',' after you click in the cell.
6. Highlight the array you created in the other area of your spreadsheet with what you are looking up and their values. Type ','.
7. Type the number of the column in the array the information you want to display is in. In this example, you will have two columns of data in your array, the city code on the left and the city name on the right. For this array, you would type '2' after the comma in Step 6 to tell Excel that the information to display is in column 2 of the array. Type another comma.
8. Type 'false' after the comma in Step 7. This will tell Excel that you only want to return the data if it has an exact match. Placing 'true' in this spot will return the closest value to what you are looking up and you don't want that. Type a ')' and press 'Enter.' The cell will now contain the city name for that city code.
9. Click in the cell with the VLOOKUP equation. Click into the formula bar and place the cursor before the first cell in the array portion. Type '$' in front of all the column letters and row numbers in the array. (For example, if the array is G1:H125 you would type '$' before G, 1, H, and 125 to look like $G$1:$H$125). Press 'Enter.'
10. Right click in the VLOOKUP cell and click on 'Copy.' Highlight all the other cells in the column, right-click, and click on 'Paste' to paste the equation into all the other cells.

Read more ►

How to Find the Percentage of Two Cells in Microsoft Excel

1. Open Microsoft Excel.
2. Create your first data point. For the purpose of this example, assume you want to find out what percentage of your total business income was earned in the months of March and April. Enter 'March' (without quotation marks) in cell A1, then press the 'Tab' key and enter '8,500' (again without quotation marks) in cell B1.
3. Create your second data point. Press the 'Enter' key to move your cursor to cell A2. Enter 'April,' press the 'Tab' key, then press '8,750' in cell B2.
4. Add your data points. Move your cursor to cell B3, directly below your two number values. Enter the following formula into this cell to sum the two values:=SUM(B1:B2)
5. Calculate the percentage of the total. Double-click cell B3 to edit its contents, then add a '/' character and the total value by which the preceding cells should be divided. For this example, assume your annual business revenue is $115,000. Your formula should now look like this:=SUM(B1:B2)/115000The formula tells Excel to take the sum of the values in cells B1 and B2 and divide them by the total yearly revenue value of $115,000. Press 'Enter' after inputting the revised formula to return your final percentage (0.15, or 15 percent, in this example).

Read more ►