How to Create a Break Even Chart in Excel

Label Data
1. Type 'Fixed Costs' into cell A1.
2. Type 'Unit Expense' into cell A2.
3. Type 'Unit Revenue' into cell A3.
4. Type 'Units' into cell C1.
5. Type 'Revenue' into cell D1.
6. Type 'Expense' into cell E1.
Input Data
7. Input your fixed costs of a production into cell B1. For example, if rent for the factory is $25 per month, then type '25.'
8. Input the cost of each unit you produce into cell B2. For example, if each widget costs you $5 to manufacture, type '5.'
9. Input the revenue you make for selling each unit into cell B3. For example, if each widget sells for $10, type '10.'
10. Input the number of units sold in column C, under the words 'Units.' For example, you might write '1' in cell C2, '2' in cell C3, and so on until you write '10' in cell C11.
11. Input the following formula into cell D2:
=$B$3*C2
12. Copy the formula from the previous step by left clicking on D2 and pressing 'Ctrl' and 'C' at the same time.
13. Highlight cells D3 through D11 by clicking D3 and dragging down to D11.
14. Paste the formula by pressing 'Ctrl' and 'V' at the same time.
15. Input the following formula into cell E2:
=$B$1 $B$2*C2
16. Copy the formula from the previous step into the cells in column E by following the same steps as copying and pasting the previous formula.
Create the Chart
17. Click 'Insert' > 'Scatter' > 'Scatter with smooth lines and markers' to insert a blank chart into your spreadsheet.
18. Right click on the chart and click 'Select Data' to display the Select Data Source dialog.
19. Click 'Add' under Legend Entries to display the Edit Series dialog.
20. Type 'Revenue' into the 'Series Name' text box.
21. Click the 'Series X Values' text box, then highlight cells D2 through D11, the entire Revenue column.
22. Click the 'Series Y Values' text box, then highlight cells C2 through C11, the entire Units column.
23. Click 'OK' to exit the Edit Series dialog.
24. Click 'Add' under Legend Entries to display the Edit Series dialog.
25. Type 'Expense' into the 'Series Name' text box.
26. Click the 'Series X Values' text box, then highlight cells E2 through E11, the entire Expense column.
27. Click the 'Series Y Values' text box, then highlight cells C2 through C11, the entire Units column.
28. Click 'OK' to exit the Edit Series dialog.
29. Click 'OK' to exit the Select Data Source dialog.

Read more ►

How to Extract Data Onto a Table Using Excel 2007

Create a Table
1. Open the spreadsheet and highlight the data you want to put into a table.
2. On the 'Insert' tab, click 'Table.' If you want to include the headers in your data, choose the box next to 'My Table Has Headers.' Click 'OK.'
3. Your data will appear as a table. If you've excluded any data, it will still appear. If this isn't what you had in mind, try creating the table again, this time including all your data. Then, when your table is created, choose in the next step to exclude and hide any additional data.
4. Format the table by using the drop-down boxes that appear next to the headers or in the 'Table Tools Design' tab, which appears at the top of the screen when you make a table. Here you can change the color of the table, sort the data and choose to exclude data.
Convert Table Data Back to a Range of Data
5. If you decide to revert back to a range of data, select a cell in your table.
6. Click on the 'Design' tab, then choose 'Convert to Range.' This action removes the table but leaves the data and color formatting intact. The 'Design' tab will disappear.
7. Your data is now in a simple Excel format. You can manipulate it as you would a traditional spreadsheet.

Read more ►

How to Change the Color of a Cursor in Excel

1. Click the 'Start' button in Microsoft Windows.
2. Click 'Control Panel.'
3. Click the 'Ease of Access' category.
4. Click 'Ease of Access Center.'
5. Click the 'Make the Mouse Easier to Use' link under 'Explore All Settings.'
6. Select the color scheme you want to use for the mouse cursor. Choose from a selection of white, black or inverting, in different sizes.
7. Click 'Apply.' Click 'OK.'
8. Open Microsoft Excel.

Read more ►

How to Make Excel Calculate Division With Remainders

1. Launch Microsoft Excel and open a new, blank worksheet.
2. Enter the top number of a fraction or the number to the left of the division sign (the dividend) into cell A1. For the division problem '9/4,' type '9' (no quotes) and press 'Enter.'
3. Enter the bottom number of a fraction or the number to the right of the division sign (the divisor) into cell B1. If you are solving '9/4,' type '4' (no quotes) and press 'Enter.'
4. Use the Quotient function to find the integer portion of the result. Type the following into cell C1:=QUOTIENT(A1,B1)Press 'Enter.' For the problem '9/4,' you see a result of '2.'
5. Use the Mod function to find the remainder. Type the following into cell D1:=MOD(A1,B1)Press 'Enter.' If you are dividing 9 by 4, there is 1 left over, and you see a result of '1.'
6. Show the quotient and remainder in a single cell with the letter 'R' designating the remainder. Type the following into cell E1:=C1' R 'D1Press 'Enter.' For the division problem '9/4', you see '2 R 1.'

Read more ►

How to Compare Two Excel Spreadsheets

1. Open Excel and use 'File,' and then 'Open' to open the first file to use for comparison.
2. While the first document is still open, use 'File,' and then 'Open' to open the second file.
3. Select the 'View' tab, and then select 'New Window.'
4. Select 'View Side by Side.'

Read more ►

How to Make a Timeline in Excel

1. Plan your timeline ahead of time. Sketch it out on a piece of paper so you can refer to it as you design the timeline in Excel.
2. Open a new worksheet in Excel.
3. Go to 'File' then 'Page Setup' and change the page orientation to landscape. This will turn your worksheet horizontal.
4. Drag your cursor from the first cell to the length you expect your timeline to take up. Click 'Format,' 'Cells,' 'Alignment' and then check the box next to 'Merge Cells' and click 'OK.'
5. Place your cursor in the merged cell you just created and type the title of your timeline. Click the center alignment button under alignment options to center the title. You can format the size and font for the title under the 'Home' tab and the 'Font' group.
6. Skip down two or three rows and then enter the dates you want included on your timeline, skipping a cell between each one. Skipping a cell will help space out the dates so you can type in the information for the event and add graphics.
7. Drag your cursor over the row of cells above the dates you entered to highlight them. Start at the first date and drag it over to the last date. Click the paint can under the 'Font' option and choose a color for your timeline.
8. Type short descriptions of the events that coincide with each date in the row below that date. You can change the size and font of the descriptions under 'Home' and then 'Font.'
9. Drag your cursor over the rows with the dates and descriptions to highlight them. Go to the 'Orientation' button under the alignment option to rotate the dates and descriptions. The button is a slanted arrow with the letters 'a' and 'b' over it. Choose one of the options shown or click 'Format Cell Alignment' to customize the angle. You can type in the angle you want or drag the line in the 'Orientation' box to the angle you want.
10. Go to 'Insert,' 'Illustrations' and 'Pictures' to add a graphic to your timeline. You can choose a file you have saved or go to 'Clip Art' instead of 'Pictures' and choose a Microsoft graphic. Once you have selected the file or clip art, click on it to insert it on your timeline worksheet.
11. Click on and manipulate the corner controls around the graphic to resize the image.
12. Click on the image and drag it to the location you want it on your timeline.
13. Save your timeline when you finish.

Read more ►

How to Use a Subroutine in Excel Visual Basic

1. Open the Visual Basic Editor. In Excel 2003, from the menu, select 'View,' 'Toolbars,' 'Visual Basic.' From the new toolbar, click the Visual Basic Editor icon. In Excel 2007, on the Developer tab, in the Code group, click 'Visual Basic.'
2. Insert a new module. From the menu, click 'Insert,' 'Module.' You will see the new module listed in the Project Explorer with the name 'Module1.'
3. Insert a new procedure. From the menu, click 'Insert,' 'Procedure.'
4. In the 'Add Procedure' pop-up box, type a name for your new subroutine. Use underscores, rather than spaces, to separate words.
5. Under 'Type,' select 'Sub.'
6. Under 'Scope,' choose 'Public' or 'Private.' A public subroutine is accessible to other procedures; a private subroutine only works within the same procedure.
7. If desired, check the 'All Local variables as Static' checkbox. Static variables retain their values when you exit a procedure. The default behavior clears all variables.
8. Click 'OK.' In the code window, you will see the Sub and End Sub statements.
9. Add code. Between 'Sub' and 'End Sub,' insert the VBA instructions for your subroutine. For instance, use the following code to display a message box with a greeting:Sub Test()
MsgBox 'Hello World'
End Sub
10. Run the subroutine. From the menu, select 'Run.' In Excel 2003, the subroutine will execute. In Excel 2007, select the subroutine in the pop-up box, and click 'Run' to start execution.

Read more ►

How to Use the Excel Monthly Household Budget Template

1.
Open Excel. If you are using Excel 2007, click the 'Office Button' and then click 'New.' In Excel 2003, click 'File' and then 'New.' Type 'Family Monthly Budget' in the 'Search Online For' box and click 'Go.'
2.
Click 'Family Monthly Budget Planner' to select that template. Click the 'Download' button. The template will open as a new Excel worksheet.
3.
Start in the 'Projected Cost' column. Type in what you expect the costs to be for each item listed that month. You can round off your costs to whole numbers, or put in exact numbers and the template will round them off for you. If you are uncertain how much you might spend on an item, look at past bills or receipts and make an estimate.
4.
Enter the information into the 'Total Actual Cost' column as the month goes along. Every time you spend money or pay a bill, enter the amount into the correct cell. If you already have a number in that cell, add the 2 numbers together and enter the new number into the cell. The template will automatically calculate the difference between your projected and actual spending.
5.
Enter your projected monthly income at the beginning of the month. As you earn money throughout the month, enter this data into the cells in 'Actual Monthly Income.' Your household budget template will help you see what you actually have coming in and where it is going.

Read more ►

How to Darken the Selection in Microsoft Excel

1. Select the column or columns of data with your mouse. If you want to select all of the columns, position your mouse on top of the first column letter (usually column A), click, and drag it across to the last column. This will select everything in your spreadsheet.
2. Click the 'Home' tab and select the 'Cell Styles' option. You will see a listing of various cell themes.
3. Choose one of the cell themes that are listed at 20 percent opacity from the list of cell style options. From now on, when you select the cells in your worksheet, the selection box will be darker and easier to distinguish. Leave this formatting on your worksheet until you are finished working with the file and ready to save the document.
4. Use the scroll bar at the bottom of your worksheet to scroll over until you find a column that has the default Excel background and text formatting. Select the column (as you did in step one) and double-click the 'Format Painter' button on the 'Home' tab. Then click and drag your mouse across the column letters in your spreadsheet--this will return your worksheet to the default formatting.

Read more ►

How to Merge Excel Spreadsheets to Find Duplicates

Complete the Merge
1. Identify the duplicate values you are searching for. Identify the data type and make sure it is the same in both spreadsheets. For example, if you would like to identify duplicate dates, make sure the dates are recorded in the same format (9/12/2034) across spreadsheets.
2. Confirm the spreadsheets have the same number of columns. To merge spreadsheets, make sure they have the same number of columns. If not, make sure the column headers match until it no longer matters. For example, if spreadsheet A has four columns and spreadsheet B has six columns, make sure the first four columns of both spreadsheets are First Name, Last Name, Phone and Email. Make sure there are no empty columns or rows.
3. Copy and paste or consolidate. Copy the information from spreadsheet A and paste the information into spreadsheet B below the last record on spreadsheet B. Each column in spreadsheet B should line up with the appropriate column in spreadsheet A.
Identify Duplicate Records
4. Insert a new column. Insert the new column next to the column with the duplicate value. For example, if you are looking for duplicate phone numbers in column C, insert a new column to the left of column C. Column C should be come before column D. Type the title 'Duplicate?' into the column's first row to name the column.
5. Enter the formula for identifying one column duplicates. Select the second row of your 'Duplicate?' column and enter the duplicate formula: =IF(D2=D3, 'Duplicate',''). This formula will identify duplicate records in column D only. For duplicates in multiple columns, enter =IF(D2E2=D3E3, 'Duplicate',''). This formula will identify duplicate records in columns D and E.
6. Copy and paste the formula. Copy the formula down the length of the appropriate column. Right-click the cell with the formula and select 'Copy' from the short-cut menu. Select each cell down the length of the appropriate column and right-click and select 'Paste Special...', then click 'Values' and 'OK.'
7. Sort the 'Duplicates?' column. Sort your spreadsheet based on the duplicate column. Sort the duplicate column in descending order to move each duplicate to the top of the list.

Read more ►

How to Open Excel 2007 Files With Office 2003

1. Visit Microsoft's online Download Center. Search for 'Microsoft Office Compatibility Pack for Word.'
2. Click on the 'Microsoft Office Compatibility Pack for Word, Excel and PowerPoint File Formats' link.
3. Click on the 'Download' link. Click on 'Save' to save the file to your computer.
4. Click on the downloaded FileFormatConverters.exe file. It should be located in the downloads file on your computer.
5. Click on 'Run' to start the installation process. Once the installation is complete, you will be able to open and edit files made in Excel 2007 in your Office 2003 suite of applications.

Read more ►

How to Enter a Series of Dates in an Excel 2007 Spreadsheet

1. Open the worksheet.
2. Click on a cell that will contain the first date. Enter this date. For example, 5/12/11.
3. Point on this selected cell's lower right corner. A black ' ' symbol will appear over the tiny square in this corner. This symbol is the fill handle.
4. Drag the fill handle across the adjacent cells to fill them with the sequence of dates.

Read more ►

How to Use Excel to Subtract From the Total

1. Open the document you would like to make the changes to in Microsoft Excel.
2. Locate the cell that contains the total and make note of the cell number --- for example, 'E2' or 'F15.'
3. Select the empty cell in which you would like to have the subtracted result shown.
4. Type '=X - Y' where X is the number of the cell containing the total and Y is the number or cell you would to subtract from the total.
5. Press 'Enter' and the cell will now show the subtracted total.

Read more ►

How to Make the Cursor Stop Highlighting

1. Launch the Microsoft Excel project in which you wish to stop the cursor highlighting.
2. Select a single cell with your cursor. The cursor highlights a range of cells only if extended-selection mode is turned on.
3. Turn extended-selection mode off by pressing the 'F8' key.
4. Select a single cell with your cursor. The cursor will highlight only the individual cell. Extended-selection mode has been turned off.
5. Restart your system if the problem persists.

Read more ►

How to Combine Worksheets Inside an Excel Spreadsheet

1. In Excel 2007, click on the 'Data' tab and choose 'Consolidate' from the Data Tools group. For earlier versions of Excel (97-2003), click on 'Data->Consolidate.'
2. Define the reference ranges using the Reference box. Specify the first range you want to consolidate.
3. Click 'Add.' The reference you defined in Step 2 will appear in All References.
4. Repeat Steps 2 and 3 until you have defined everything you want to consolidate. Then click 'OK.'

Read more ►