How to Create a Mirrored Image in Excel

1. Select or create the image you wish to manipulate in just about any software program you want. The image can be simple or intricate, homemade or an object that you found in an obscure photo album.
2. Click the image so that the entire object is highlighted. This will ensure that whole image will be copied to Excel.
3. Right click and select 'Copy.' Open or go to the Excel window and click on the cell where you want the image to be.
4. Pick 'View' from the top of the Excel window. If there is not a check mark next to the Drawing toolbar option, click 'View,' 'Toolbars' and 'Drawing.' A separate toolbar along the side, bottom or top of your window will appear.
5. Choose the 'Draw' option on the Drawing toolbar. In most versions of Excel it has an arrow pointing up or down next to it. A pop up window will appear next to it. Click 'Rotate or Flip' and then 'Flip Horizontal' or 'Flip Vertical' depending on how you want or image to look.
6. Drag the outer corner of your image inward or out to change the size.

Read more ►

How to Construct a Categorical Frequency Table in Excel 2007

1. Enter your data into one column. For example, if you have a list of 10 house prices that you want to categorize, enter them into cells A1 to A10.
2. Enter your bin values into the next column. In our example, you would enter your bin values (for example, 100,000, 200,000, 300,000) into column 2. Bin values are optional: If you do not enter values for bins, Excel will choose bins for you.
3. Click on 'Data Analysis' from the 'Data' tab.
4. Click on 'histogram' from the list box.
5. Enter the range for your chart data into the 'Input Range' box. For example, enter 'A1:A10.'
6. Enter the range for the bin values into the 'Bin Range' box. For example, if you have four bins in cells B1 to B4, enter 'B1:B4.' If you also want to display a histogram, check the 'Chart Output' box.
7. Press 'OK.' Excel will enter the frequency table into the Excel worksheet.

Read more ►

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 ►