How to Make a Map Using Excel

1. Create the information you want to appear on the map, such as statistics for a region. Enter the name of the place (such as city, state, or country) in column A and the number or statistic in column B.
2. Once you have your data for your map, click File and Save. Highlight and select all your data rows and columns by holding the left button down and dragging it until all your data is selected.
3. Go to the top of the screen and use your mouse to click Insert, followed by Map. Move your cursor (which will no longer flash now) and move it the part of the Excel page where you want your map to be.
4. Holding down the left mouse button, drag your cursor across the Excel page to show how large you want the map. You will need to start from the top left corner of your desired map area and drag it down to the bottom right.
5. Wait for Microsoft Excel to display a pop-up menu, listing the maps available. Read through the list, select the one you want, and click OK.
6. Excel will then find boundary names to match the data you entered (such as the region, city or state). If it cannot automatically find the information you are looking for, a pop-up menu labeled Resolve Unknown Geographic Data will appear with other name options; choose one and click Change or select Discard if you cannot find a match.
7. Microsoft Excel will then generate a map based on statistics and data you entered. Look it over to make sure all the statistics and names are correct; if not, go back to your saved data, fix it and re-save.

Read more ►

How to Remove Scroll Bars in Pages

1. Open the document or spreadsheet from which you would like to remove the scroll bars.
2.
Select 'Tools' from the toolbar menu. Next, choose 'Options,' then click on the 'View' tab.
3. Uncheck the boxes next to 'Horizontal Scroll Bar' and 'Vertical Scroll Bar.' Then click 'OK.' (These options are in the 'Show' area under the 'View' tab in Microsoft Word; they are located in the 'Window Options' area under the 'View' tab in Microsoft Excel.)
4. To show the scroll bars again, repeat steps 1 and 2. Next, place checks in the boxes next to 'Horizontal Scroll Bar' and 'Vertical Scroll Bar.' Click 'OK' when finished.

Read more ►

How to Calculate Percentages in Excel

1. Type the amount in a cell. For example, a student received a score of 48 out of 60 on a test and wants to find his percentage. The student would type 48 in cell A1.
2. Type the total in an adjacent cell. In the example, the student would type 60 in cell B1.
3. Type '=amount cell/total cell' in an adjacent cell to the total cell. Replace amount cell and total cell with the cell addresses of the amount and total. In our example, the student would type, '=A1/B1' in cell C1. The total will display 0.8 in cell C1.
4. Highlight the cell with the decimal, and click '%' button under the 'Home' tab to convert the decimal to a percentage.

Read more ►

How to Create Macros in Excel 2003

1. Open the Microsoft Excel 2003 application on your computer. Click on the 'Tools' option from the top toolbar menu and then click on the 'Options' button.
2. Click on the 'Security' tab and then click on the 'Marco Security' option. Click on the 'Security Level' tab and choose either the 'Medium' or 'Low' option.
3. Click on the 'Tools' option and then scroll over the 'Marco' option. Click on the 'Record New Macro' button.
4. Enter a name for your macro in the 'Macro name' box. The first character in the name must be a letter and not a number.
5. Choose where you want to store the macro on your computer from the 'Store macro in' box. Enter a description for your macro in the 'Description' box. Click on the 'OK' button.
6. Perform the task that you want for your macro within the Excel spreadsheet, and then click on the 'Stop Recording' button when you're finished. For example, you can select a cell, press the 'Enter' key and then select the 'yellow' highlight color from the toolbar. Now, every time you press the 'Enter' key in a cell it will be highlighted in yellow.

Read more ►

How to Merge Sheets in Excel 2007

1. Click the Windows 'Start' button. Type 'excel' in the search text box. Press 'Enter' to open the Excel software.
2. Press 'Ctrl' and 'O.' In the dialog window that opens, double-click the XLSX file that contains multiple sheets you want to combine.
3. Click the 'DigDB' menu item at the top, then click the 'Table' sub-menu item, then click 'Append Tables in this File.' A configuration window opens.
4. Click each sheet you want to combine, and click the 'Add' button. If you want to combine all sheets together, select 'All Sheets,' and then click 'Add.'
5. Click 'OK' to combine the spreadsheets. Notice the original, first spreadsheet has all the combined data. Press 'Ctrl' and 'S' to save the changes.

Read more ►

How to Convert Date to Day of Week in Excel

Convert Replace Date with Day of Week
1. Open the Excel workbook in which you want to convert and replace the date data with day-of-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format.
2. Left click with the mouse to select the column or row that contains the range of date data.
3. Select 'Format' from the menu.
4. Select 'Cells' from the drop-down list.
5. Select the 'Number' tab on the 'Format Cells' pop-up window.
6. Select 'Custom' in the 'Category' list.
7. Enter 'dddd' in the 'Type' field to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).Enter 'ddd' in the 'Type' field to display the date as day-of-week, with the day abbreviated to standard three-letter abbreviation (Mon, for instance).
Convert Date to Day of Week, Without Replacing Initial Data
8. Open the Excel workbook in which you want to convert and replace the date data with day-of-the-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format. This article will presume that the date data is located in column 'A,' with the header 'Date' in cell 'A1.'
9. Determine where you want the day-of-week data located. Label that column header with DOW (cell 'B1,' for example).
10. In 'B2,' enter the formula '=TEXT(A2,'dddd')' to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).
Or, you can enter the formula '=TEXT(A2,'ddd')' to display the date as day-of-week, with the day abbreviated to standard three-letter abbreviation (Mon, for instance).
Press 'Enter' to move the cell cursor out of cell 'B2'.
This is with the understanding that cell 'A1' contains the label 'Date' and cell 'B1' contains the label 'Day of Week' and that the first date is located in cell 'A2' and the first cell to be populated with the day of the week is cell 'B2'. If your workbook is set up differently, adjust the location of the formula to the desired location of the day-of-week data and adjust the formula accordingly to refer to the correct cell.
11. Return the cell cursor to cell 'B2'. Select 'Edit' from the menu.
Select 'Copy' from the drop-down list.
12. With the cell cursor, select the entire range where you want the day-of-week data. Select 'Edit' from the menu, and select 'Paste' from the drop-down list; this will paste the formula that you entered in cell 'B2' through the entire range of cells.

Read more ►

How to Import Excel Spreadsheets Into Access

1. Save your spreadsheet data in Excel using the 'File,' 'Save' menu. Although Access recognizes a range of alternate formats, including dBASE and text delimited, it is best to use the native Excel format for your saved file. Access is configured to import Excel files directly and provides options for continuing to work with the data in both products after the import process.
2. Start Microsoft Access and open the database into which you want to import your Excel data. You can create a new, empty database in Access by clicking 'File,' 'New' and selecting the 'Blank Database' option. Provide a name for the file when prompted. Click 'File,' 'Get External Data,' 'Import' and select the Excel format. Locate your spreadsheet subdirectory and import your spreadsheet into Microsoft Access. If you are using Microsoft Access version 7, you can also click the 'External Data' tab at the top of your screen and select 'Excel' as the format to import the spreadsheet data. You can append the data to an existing table or create a new table for your Excel spreadsheet data.
3. Select the worksheet or range you want to import from the Import wizard and click 'Next.' Select the Excel column headings you want to import as field names and verify that you want to create a new table or append data to an existing table. Click 'Next' to continue. You can change field names, skip fields that you do not want to import and select indexing options, including a primary key for sorting your spreadsheet data in Access. Click 'Finish' when you are satisfied with your selections.
4. Review the Table Analyzer wizard results to verify the structure of the database tables that Access has created from your Excel worksheet. The wizard will divide tables containing duplicate information into separate tables so that each category of data is stored only once. Verify field placement within tables, select the primary key used for sorting and correct any typographical errors prior to completing the import process.

Read more ►

How to Add Multiple Sheets to a Workbook in Excel 2007

1. Open Excel 2007. At the bottom of the page, there are tabs you can use to select which sheet you want to view. These tabs are labeled 'Sheet 1,' 'Sheet 2,' and so on.
2. Right-click over one of the sheet tabs to insert a sheet. Click 'Insert,' select 'Worksheet,' and click 'OK.' Look down at the tabs to verify that a sheet was inserted. Repeat this to insert multiple sheets.
3. Hold down the 'Shift' key. Drag your mouse over the spreadsheet tabs to highlight the number of sheets you want to insert. You can insert multiple sheets by highlighting more than one tab.
4. Click 'Insert,' select 'Worksheet,' and click 'OK.' Look down at the tabs to verify that the extra sheets were inserted.

Read more ►

How to Create Forms in Excel 2007

1. Open an Excel 2007 spreadsheet. Type in column headers in the top row for each column you will enter data under.
2. Click and drag the mouse over the column headers only to select them. Do not highlight any other columns or select the entire row, including the blank cells to the right of the column headers or else Excel will not know which columns to use to create the form.
3. Click on the arrow button beside the Quick Access Toolbar at the top of the screen, then click 'More Commands' from the drop-down list that appears.
4. Click the 'Choose commands from' drop-down menu and click 'All Commands.' Scroll down the list and then double-click the 'Form' button. Click 'OK' to close the Excel Options dialog box.
5. Click the 'Form' button that you just added to the Quick Access Toolbar. Excel creates a form with the column headers that you may use to add new records to the spreadsheet.

Read more ►

How to Show Formulas in Excel 2003

1. Click on the square in the upper-left corner of the Excel 2003 spreadsheet to select all cells.
2. Press and hold the 'Ctrl' key on your keyboard after all the spreadsheet cells are selected.
3. Press '`,' the grave accent key, located in the upper-left hand corner of your keyboard below 'Esc.'
4. Release both buttons.

Read more ►

Step

1. Start Microsoft Excel. Double click on the Desktop icon and Excel will open with a blank spreadsheet. If you cannot locate the Desktop icon, click on the 'Start' or 'Microsoft' button, and then click on 'All Programs.' Click on 'Microsoft Office,' and then click on 'Microsoft Excel.'
2. Click on cell 'A1' and enter a name for the column. For example, if you are making a budget, then you might type 'Income.' Data in Excel are entered in columns for easy formatting and insertion of formulas and functions.
3. Enter your other column headings in row 1. In the budget sheet example, click on cell 'B1' and then type 'Amount,' then click on cell 'C1' and type 'Expenses' and then click on cell 'D1' and type 'Amount.' If you have other column heading you want to enter, click on cell 'E1' and continue working right through the worksheet.
4. Enter the data into your columns. Click on cell 'A2' and type your first item. In the budget example, this could be 'Mortgage.' Press the 'Right arrow' key to move the cursor to cell 'B2' and then type the corresponding amount. If your mortgage is '$1,000' then type '$1,000' into cell 'B2.'
5. Add up the numbers in a column by using 'Autosum.' Highlight all of the data in a column that you want to add, click the 'Home' tab and then 'Autosum' to add up the entire column. To highlight data in a column, left click on the top left of the first item and then drag the cursor to the bottom right of the column. You can also click on the column identifier to highlight the entire column. The column identifier is the number or letter at the very top of the column.
6. Format your cells by highlighting them, and then clicking on 'Format Cells.' The pop up window gives you a myriad of choices to align cells, choose borders and fill, and select the type of data you want in cells. In the budget example, you may want to make sure all of your entries are in dollars. Click on the 'Number' tab, click on 'Currency' and then choose the type of currency you would like to have by clicking on the down arrow next to 'Currency.'

Read more ►

How to Fix Broken Links in Excel 2007

1. Close all the workbooks that are open.
2. Open the workbook with the broken link.
3. Click 'Options' on the trust bar, and then click 'Enable this Content.'

Read more ►

How to Convert Date to String in Excel

1. Open Microsoft Excel, then open the document where you want to convert a date to string.
2. Type '=TEXT(' in the cell where you want to place the string.
3. Click on the cell with the date. This should put the cell reference (like A1) in the initial cell.
4. Type 0, 'MM/DD/YYYY') after the cell reference. Exchange 'MM/DD/YYYY' with the format of your date. In this case MM is the month, DD is the date, and YYYY is the year. Press enter and the cell should have a text (string) version of the date.

Read more ►

How to Convert Text to Upper Case in Excel 2007

1. Convert text in a cell, say cell A1, into fully capitalized text in another cell, say B1, with the UPPER function. For example, type in B1 the following: =UPPER(A1). You can even copy the contents of B1 into A1 by right-clicking the mouse on B1, selecting Copy, then right-clicking A1, and selecting “Paste Special” and “Value.” (If you insert the formula instead, the reference will be circular and you’ll get an error.)
2. Convert the returned value of a formula using the following syntax: =UPPER(function). For example, if the original function in a cell is =IF(A1>B1,C1D1,A1), then change the cell contents to =UPPER(IF(A1>B1,C1D1,A1)).
3. Perform this capitalization conversion on more than one cell at once as follows. For a row of text, say A1 to A100, type in an empty cell, say B1, the following: =UPPER(A1). Highlight B2 to B100. Click 'Home.' Click 'Fill.' Click 'Down.' The B-column cells now range in contents from =UPPER(A1) to =UPPER(A100). Now just perform a special paste of the column as for the single cell in Step 1.

Read more ►

How to Make a Cell Reference Absolute in Excel

1. Start Microsoft Excel 2007 and open an existing workbook from your files or start a new blank workbook.
2. Click to select the cell where you would like to enter a formula where the cell reference is absolute.
3. Begin typing the formula into the cell. Type a dollar sign right before the column reference in the formula to make it absolute. For instance, you may type '$A1' into a formula. This means that wherever you copy and paste this formula, Excel will always keep the reference in column A.
4. Type a dollar sign right before the row number to make the row number absolute. For example, you can type 'A$2' inside of a formula. This means that the cell reference will always refer to the second row, no matter where you copy and paste the formula.
5. Use dollar signs before both the column letter and cell number to make the entire cell reference absolute. If you type, '$A$2' inside of a formula than that formula will always refer to cell 'A2.'
6. Continue typing the rest of your formula. You can include dollar signs into other cell references inside your formula if you need to.
7. Press the 'Enter' key on your keyboard to enter the formula into Microsoft Excel and view your calculation result.

Read more ►

How to Nest Subtotals in Excel 2003

1. Click and drag your mouse to highlight the three columns---two columns of headings and the third of values.
2. Click the toolbar's 'sort' button. The Sort window will open.
3. Sort the table first by the first column and then by the second column.
4. Click 'Subtotals' from the Data menu.
5. Click the down-arrow in the 'At each change in' drop-down box. Click the first column's heading.
6. Click 'Sum' in the 'Use function' box.
7. Click the down-arrow in the 'Add subtotal to:' drop-down box. Click the third column's heading.
8. Click 'OK.'
9. Click and drag your mouse to highlight the three columns again. Click 'Subtotals' from the Data menu.
10. Click the down-arrow in the 'At each change in' drop-down box. Click the second column's heading.
11. Click 'Sum' in the 'Use function' box.
12. Click the down-arrow in the 'Add subtotal to:' drop-down box. Click the third column's heading.
13. Uncheck the 'Replace current subtotals' box.
14. Click 'OK.'

Read more ►

How to Change the X

1. Open Excel and navigate to the chart for which you wish to change the x-axis range.
2. Move your cursor so that it is directly over the x-axis of the chart, and right-click with your mouse.
3. Select 'Format Axis...' from the menu that appears. The Format Axis dialog box will open.
4. Click so that 'Fixed' is selected instead of 'Automatic' for both the Minimum and Maximum under Axis Options.
5. Select the values you wish to use as the minimum and maximum for the x-axis and enter them in the appropriate boxes. Click 'Close' to finish.

Read more ►

How to Add Headers and Footers to Excel Documents

1. Start Microsoft Excel and open the file you want to change.
2. Open the View menu and select Header and Footer option.
3. In the Page Setup dialog box, click the Custom Header or Custom Footer button.
4. Click inside the Left section, Center section or the Right section.
5. Click the A icon to change the font styles.
6. Click the # icon to insert page numbers.
7. Click the icon to insert the number of pages in the document (so if you wanted the header or footer to read 'Page X of 25,' you'd click the # icon followed by the icon).
8. Click the date, time, file name or tab icon to insert those items.
9. Click OK to accept the changes.

Read more ►

How To Copy Name Ranges in Excel 2003 to Another Workbook

1. Open the workbook you will be copying the range names from. If you do not already have the names set, define the range names. To do so, click on the 'Insert' menu, select 'Name' and then choose 'Define' from the list. Find the names in the workbook box.
2. Locate the name of the second workbook. Make sure that workbook is saved as a Macro-enabled Workbook. The file extension should be .xlsm or .lsm.
3. Hold down the 'Alt' and 'F11' keys. The Visual Basic Editor will open. Click 'Module' in the 'Insert' menu. The module window will pop-up.
4. Type the following code into the window. Change the reference to book2.lsm or book2.xls to the name of the book you are pasting too.Sub Copy_All_Defined_Names()' Loop through all of the defined names in the active' workbook.For Each x In ActiveWorkbook.Names' Add each defined name from the active workbook to' the target workbook ('Book2.xls' or 'Book2.xlsm').' 'x.value' refers to the cell references the' defined name points to.Workbooks('Book2.xls').Names.Add Name:=x.Name, _RefersTo:=x.ValueNext xEnd Sub
5. Close the Visual Basic Editor Window. Click on the 'X' button or hit 'ATL' and 'F11' again to close the window. The macro will automatically save. When you are back at book 1 or the workbook you are copying from, save the workbook. Click on 'File' and choose 'Save' or 'Save as.' Do not change the name of the file in any way.
6. Open the workbook you are pasting to or start a new workbook. Save the workbook as an Excel Macro-Enabled Workbook. Make sure the name is the same as the name for book2.lsm in the code. The spelling capitalization and extension have to be exact.
7. Open the first workbook again and click on the 'tools' menu. Select 'Macro' and click 'Macros.'
8. Click on 'Copy_All_Defined_Names' from the list of macros. Select the 'Run' option. The names will be automatically pasted in the second workbook.
9. Open Book2 or the paste destination workbook. All the names should be pasted.

Read more ►

How to Merge Information From Several Excel Spreadsheets

1. Open the workbook with the spreadsheets you want to consolidate. If you the spreadsheet is not in the workbook, create a duplicate spreadsheet within the workbook by doing a copy and paste.
.
2. Chose Data and then Consolidate. Excel displays the Consolidate dialog box.
3. Specify the range with the Reference box. After you specify the range (or the area of cells on the spreadsheet you want to consolidate) click Add. The reference will appear in the All References list. Continue to add ranges until you have added them all.
4. Click on the Create Links to Source Data box if you want the consolidated data to contain links to the original data. If not, leave blank and click OK to do the consolidation.

Read more ►

How to Create a Bar Graph in Excel 2003

1. Log on to your computer and pull up your Excel worksheet. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Arrange desired data in columns or rows. You only need to enter the numbers at this time.
3. Highlight the cells containing desired data. To select separated data, highlight the first piece. Then hold 'Ctrl' while clicking on the remaining data.
4. Select 'Chart' from the 'Insert' menu or click on the 'Chart Wizard' icon on the toolbar.
5. Choose your chart type. Click 'Next'. Confirm the data range mentally and select if you want your series in rows or columns. Select 'Next'.
6. Enter titles for your chart and axis. Choose the location of your chart. Select 'Finish'.

Read more ►

How to Hide Unhide Cells in Excel

Hide Cells
1. Select the cells you want to hide.
2. Click the 'Format' tab and then click 'Format Cells.'
3. Click the 'Number' tab.
4. Click 'Custom' from the Category List.
5. Type three semicolons ';;;' into the 'Type' text box and then click 'OK.' The cell data will be hidden.
Unhide Cells
6. Reopen the Format Cells dialog box.
7. Delete the semicolons in the Type box.
8. Click 'OK.'

Read more ►

How do I Convert Exel 2007 to 2003?

1. Open Excel 2007 and open an existing Excel 2007 spreadsheet. Select the 'Office' button then 'Open.' Browse your computer for files saved in the .xlsx format. Select the file and click 'Open.' The file with the new Excel 2007 naming format will open.
2. Select the 'Office' button then select 'Save As.' Select the format of 'Excel 97-2003.' If any conflicts appear, the Compatibility Checker will display any losses of fidelity and how many times this has occurred. Click 'Find' to see where these instances occur or 'Help' to see how to work around these issues.
3. Click 'Continue' once you are satisfied with the adjustment made from the Compatibility Checker. Excel 2007 will save this workbook in the older .xls format. Upon successfully saving this workbook, you have converted it to the 2003 format.

Read more ►

How to Remove Lines Around Each Cell in Excel 2003

1. Open Microsoft Excel 2003.
2. Click 'Tools' in the top menu bar and select 'Options.'
3. Click the 'View' tab in the Options window.
4. Uncheck 'Gridlines' in the Windows Options section.
5. Click 'OK' to apply your changes and remove the gridlines.

Read more ►

How to Print Multiple Excel Spreadsheets on One Page

Changing the Printer Settings
1. Select 'Print' from your Microsoft Excel's File menu to display the Print dialog box.
2. Choose the option for 'Entire Workbook' in the dialog box's 'Print What' area.
3. Click the 'Properties' button for Excel to display its 'Properties' dialog box for your printer, along with the selected 'Layout' tab.
4. Set the 'Pages Per Sheet' control depending on how many spreadsheets you want printed on the page. Click 'OK' to close the 'Properties' dialog box.
Changing the Page Setup
5. Select the 'Page Layout' tab in the File menu and locate the 'Page Setup' group.
6. Click on the small square with an arrow pointing out of the square at the bottom right-hand corner.
7. Look inside the 'Scaling' section in your 'Page Setup' box and choose 'Fit to:' By default, you should see the file set to one page wide and by one page tall.
8. Select how many pages tall or wide you want for your spreadsheet when printed.
9. Click on 'Print Preview' to check the up or scaled version of the spreadsheet when printed. Click on 'Print' if you are satisfied with the preview. Select 'OK' to close your Page Setup box.

Read more ►

How to Convert a Julian Date in Excel

1. Click on the cell were you would like your output data to start. For example, if you have a list of Julian dates in column A, you might want your regular dates to start on column B, so click on cell B1.
2. Enter the following formula into the cell. =('1/1/'(IF(LEFT(A2,2)*1
3. Copy the formula into as many cells as you want dates outputted. For example, if you have Julian dates in cells A1 through A50, copy the formula 50 times from cells B1 to B50.
4. Highlight the cells you created in Step 3.
5. Right-click on the highlighted cells and choose 'Format Cells.'
6. Click on 'Date' from the 'Number' tab. This tells Excel to convert the formula's output into regular calendar dates. Choose the date type (for example, 14 December, 2009 or 14 Dec) and click 'OK.'

Read more ►

How to Code a VBA Excel 2007 Macro Consolidation

1. Go to the 'View' tab in the Microsoft Excel toolbar. Click on the 'Macros' button and select 'View Macros' Enter a new macro name for the master macro such as 'All' and click on the 'Create' button to launch the Visual Basic editor.
2. Go to the second line in between the macro name and end of the macro. In our example, it would appear as 'Sub All()' and 'End Sub'. Type 'Call' followed by each of the individual macro names you would like to run in preferential order. For example, we could have a macro named 'Links' that goes through a range of links and copies and pastes data from linked excel spreadsheets into a master sheet. Another macro called 'Format' sorts the data and adjusts the font type and size.Sub All()Call LinksCall FormatEnd Sub
3. Save the code and go back to the Excel template. Click on the 'View' tab and go to the 'Macros' button. Select the name of the master macro and click on 'Run.' This will run the individual macros that were created in order. In our example, it will run 'Links' first. Once it's completed, it will automatically run 'Format' next.

Read more ►

How to Create a Summary Chart in Excel

1. Open Microsoft Excel and create a new workbook by pressing 'CNTL-N.' Press 'CNTL-S' to save the workbook, and name it 'SummaryChartOfBookSalesByPublisher.xls' when prompted.
2. Create several rows of sample data representing the book sales of different publishers, across different types of books. Type or paste the following data into any worksheet in the workbook. Wherever you see a comma in this data, press the 'Tab' key, which will place each item in its own cell.Publisher,Genre,Distributor,Sales
Dolphin Pub. pub,romance,Amazon,$456
Dolphin Pub. pub,romance,Dynamic Dist. dist,$65
Dolphin Pub. pub,how-to,Ma and Mo dist.,$87
Dolphin Pub. pub,how-to,Keemer and Son dist.,$654
Sanford Pub. pub,romance,Ma and Mo dist.,$123
Sanford Pub. pub,romance,Keemer and Son dist.,$789
Sanford Pub. pub,how-to,Ma and Mo dist.,$432
Sanford Pub. pub,how-to,Aunt May dist.,$767
3. Select the data table, then click the 'Data' tab on Excel's main toolbar. Click the 'Sort' icon to display the dialog box with sorting options.
4. Choose the following parameters for the 'Sort' dialog box, to sort the publishing data by publisher names.Select 'Publisher' in the 'Sort by' drop-down list.
Select 'Values' in the 'Sort on' list.
Select 'A to Z' in the 'Order' list.Press 'OK' to exit the dialog box and sort the data by publisher.
5. Press the 'Data' tab on the Excel toolbar again, then click the 'Subtotal' icon to display the dialog box for configuring summary totals for the publisher data. Choose the following options in the 'Subtotal' dialog box:Select 'Publisher' for the drop-down list next to the text 'At each change in:'
Select 'Sum' for the 'Use function' drop-down list.
Check the 'Sales' checkbox for the 'Add subtotal to' list.
6. Check the following checkboxes at the bottom of the 'Subtotal' dialog box:'Replace current subtotals'
'Summary below data'Press 'OK' to create the summary rows for the publisher data. Notice that Excel has inserted boldface rows into the data, to add up the separate sales figures for each publisher.
7. Notice the small numbered buttons ('1,' '2,' '3') in the left pane of the main Excel window. Press the '2' button to hide the detail sales rows of the publisher data, and display only the summary sale data for the two publishers.
8. Select the publisher data, then press 'Insert' on Excel's toolbar to display an array of icons for graphical objects that can be inserted in a worksheet.
9. Click the 'Pie' icon in the 'Charts' group of icons, then select the first 3D-chart icon under the '3D Pie' heading. Drag the resulting blank chart carefully away from the publisher data, then drag the new blue frame so it surrounds just the two sales data rows for 'Dolphin' and 'Sanford' publishing. Don't include the rows with the 'Grand total' or the 'Sales' column header.
10. Notice the slices of the finished pie chart, which now display the summary sales data for both publishers.

Read more ►

How to Import Export Excel Text Into Access

Import in Microsoft Access
1. Open the Microsoft Access application on your computer. Click on the 'File' option for Access 2003 or the 'Microsoft Office' button for Access 2007.
2. Click on the 'Open' option and locate the Access database that you want to import and export data into. Select the database and then click on the 'Open' button.
3. Click on the 'File' or 'Microsoft Office' button again. Move your mouse over the 'Get External Data' option and then click on the 'Import' option. The Import dialog box will then appear on the screen.
4. Select the 'Microsoft Excel' option from the 'Files of type' box. Click on the arrow next to the 'Look in' field and locate the Excel file that contains the text you want to import.
5. Double click the Excel file. Click on the 'Import Data into a New Table' option and then allow time for the importing process to complete. The Excel text will then be imported into your Access database.
Export in Microsoft Access
6. Open the Microsoft Access program on your computer. Click on the 'File' option for Access 2003 or the 'Microsoft Office' button for Access 2007.
7. Click on the 'Open' option and locate the Access database that you want use for import or exporting Excel text. Click on the database file and then click on the 'Open' button.
8. Click on the 'File' or 'Microsoft Office' button again. Click on the 'Export' option and then the Export dialog box will then appear on the screen.
9. Click on the 'Microsoft Excel 97-2003' option from the 'Save as type' box. Click on the arrow next to the 'Save in' box and select the folder you want to save your exported data to.
10. Enter a name for your file into the 'File name' box and the click on the box next to the 'Save formatted' field so that it's selected. Click on the 'Export' button and the data will be exported.

Read more ►

How to Make a Timeline on Microsoft PowerPoint

1. Click on the 'Insert' tab, then click on the 'SmartArt' button in the 'Illustrations' area. Click on 'Process' and select one of the 'Timeline' layouts available. Add text to your timeline by clicking any place that says '[Text]' and typing or pasting in the text you want.
2. Add items to your timeline. Click on the timeline to select it, click on the 'Design' tab, click on 'SmartArt Tools' and click on 'Add Shape.' Delete an unwanted entry by clicking on it and pressing the 'Delete' key on your keyboard. Move an entry by clicking and dragging it to the location you desire.
3. Change the layout of your timeline by right-clicking on the timeline and selecting 'Change Layout.' Any of the SmartArt layouts are available. Your text will remain the same from layout to layout so you can adjust the design without changing the content.
4. Animate the presentation of your timeline to highlight individual events. Click on the 'Animations' tab, then click on 'Animate' and select 'One by One' so that each item on your timeline will be revealed separately during the slideshow. You will need to click your mouse for each event listed on your timeline if you choose this animation style.

Read more ►

How do I Add Information on Excel Worksheets to a Summary Sheet?

Excel 2003
1. Open Microsoft Excel and open the sheet you wish to summarize. Click on the 'Tools' menu and choose 'Scenario.'
2. Click on the 'Summary' option and choose 'Scenario Summary.' Go to the 'Result cells' tab.
3. Enter the cells you wish to summarize. You can simply place your cursor on the first cell and drag it to the last cell to create your range.
Excel 2007
4. Open Microsoft Excel and find the spreadsheet you want to summarize. Go to the 'Data' tab.
5. Go to the 'Data Tools' group and then the 'What-If Analysis-Scenario Manager.' Click the 'Summary' button.
6. Choose the 'Scenario Summary' option and enter the range of cells you wish to summarize. You can select the cells by highlighting the first one and dragging your cursor to the last one.

Read more ►

How to Protect Sales in Excel 2007

Protect an Entire Worksheet
1. Click on the bottom tab to select the worksheet that you wish to protect.
2. Click on the 'Review' tab on the menu ribbon.
3. Click 'Protect Sheet' in the 'Changes' panel of the 'Review' tab.
4. Enter a password if you want the worksheet to be password-protected. If not, leave the 'Password to unprotect sheet' box blank.
5. Click the 'OK' button and the entire worksheet will be protected from future changes.
Protect Cells Within a Worksheet
6. Click on the bottom tab of the worksheet you want to protect.
7. Click-and-drag the cursor to select the cells containing the data you wish leave unprotected. You can also hold the 'Ctrl' key down and click on multiple individual cells. Entire rows or columns can also be selected.
8. Click the 'Home' tab and then click 'Format Cells.' You can also right-click on a selected cell and select 'Format Cells' from the contextual menu.
9. Click 'Protection' and then uncheck the 'Locked' option.
10. Click 'OK' to apply the changes and close the dialog window.
11. Click on the 'Review' tab and then click on 'Protect Sheet.'
12. Enter a password for the worksheet if desired. Leave the input box blank if you don't want to password-protect the worksheet cells.
13. Click the 'OK' button. All cells except the ones selected in Step One will be protected from future changes.

Read more ►

How to Use Freeze Panes in Excel

Freeze the Top Row or Left Column
1. Open the Excel spreadsheet.
2. Navigate to the 'View' tab on the top menu.
3. Click on 'View,' then click on 'Freeze Panes.' A drop-down menu opens.
4. Select the 'Freeze Top Row' option to freeze the top row.
5. Select the 'Freeze Left Column' or 'Freeze First Column' option to freeze the left column.
6. Freeze the top row by using the keyboard and sequentially pressing the keys 'ALT, W, F, R.' Ignore Steps 3 through 7 if using this choice.
7. Freeze the left column using the keyboard by sequentially pressing the keys 'ALT, W, F, C.' Ignore Steps 3 through 7 if using this choice.
8. Unfreeze panes by repeating Steps 3 through 5 and selecting 'Unfreeze Panes' or sequentially press the keys 'ALT, W, F, F.'
Freeze Rows and Columns, Multiple Rows, Multiple Columns, or Multiple Rows and Columns
9. Open the Excel spreadsheet.
10. Freeze column(s) and row(s) at the same time by selecting the cell to the right of and below the location you want to freeze.
11. Freeze multiple rows only by selecting the cell in the left (first) column below the rows you want to freeze.
12. Freeze multiple columns only by selecting the cell in the top row to the right of the columns you want to freeze.
13. Navigate to the 'View' tab on the top menu.
14. Click on 'View,' then click on 'Freeze Panes.' A drop-down menu opens.
15. Select the 'Freeze Panes' option. You have now frozen the columns or rows, or columns and rows you designated.
16. Freeze panes using the keyboard by sequentially pressing the keys, 'ALT, W, F, F.' Ignore Steps 5 through 8 if using this choice.
17. Unfreeze panes by repeating Steps 5 through 7 and selecting 'Unfreeze Panes' or sequentially press the keys, 'ALT, W, F, F.'

Read more ►

How to Do a Paired Two Sample T

1. Enter your first set of data in column A. Enter your second set of data in column B. Make sure you only put one data result in each cell.
2. In cell C1 type '=TTEST(.' Do not hit 'Enter.'
3. Highlight your first data set and type ',.'
4. Highlight your second data set and type ',.'
5. Type '2,1).' Press 'Enter.' You formula in the cell should look like '=TTEST(A1:A5,B1:B5,2,1).' The cell returns the t Test result.

Read more ►

How to Create a Command Button in Microsoft Excel

1. Open the Excel worksheet in which you will be installing the command button.
2. Click the Microsoft Office logo located in the top left-hand corner of the screen and choose 'Excel Options.' Check 'Show Developer Tab' under the Top Options tab, and click 'OK.'
3. Click the 'Developer' tab on the main menu ribbon. Open the 'Insert' drop-down menu, and click 'Command Button.' Select the area of the worksheet where you want to insert the command button, and click on it. The command button will appear.
4. Click the 'Developer' tab on the main menu ribbon and select 'Design Mode.'
5. Right-click on the command button that you just created and select 'Properties.' Select 'Text' to add a caption to the button. Select 'Size' to edit the size and position of the command button. Select 'General' to edit the function of the button. Choose 'AutoLoad' if you want the button activated every time the workbook is opened. Choose 'Placement' to edit the movement of the button on the workbook page. Click 'OK' to save the the command button properties.
6. Right-click on the command button and choose 'Assign Macro.' Select the action that you want the button to perform from the macro list. Click 'OK' to save the command button.

Read more ►

How to Put Error Bars on Points in Excel

Office 2003
1. Right-click the data series (the points on the graph) and click 'Format Data Series.'
2. Click the tabs at the top of the window that read 'X Error Bars' or 'Y Error Bars' as necessary.
3. Select the error bar style under 'Display' and set the error amount as a fixed value, a percentage or a standard deviation.
4. Click 'OK' to apply the error bars.
Office 2007
5. Click on the graph and the 'Chart Tools' toolbar will appear at the top of the screen.
6. Select the 'Layout' tab and click the 'Error Bars' button found on the right side.
7. Click 'More Error Bars Options' and choose the style of error bar, and enter the error amount as a fixed value, percentage or standard deviation.
8. Click 'OK' to apply the error bars.

Read more ►

How to Recover MS Excel Files After Closing

Enable AutoRecover
1. Open the Excel program. A new worksheet appears.
2. Click the 'File' tab. A list of basic commands appears.
3. Click 'Options' under 'Help.'
4. Click 'Save.' A dialog box opens.
5. Select the check box for 'Save AutoRecover Information Every X Minutes.'
6. Type the number of minutes, such as every 10 minutes, for the frequency of saves.
7. Select the check box for 'Keep the Last Autosaved Version if I Close Without Saving.' The Excel files will save at least one version.
View the Document Recovery Pane
8. Open the Excel file. A Document Recovery task pane opens next to the worksheet.
9. Click the arrow next to the recovered file icon in the 'Available Files' text box. A list of options appears: 'Open,' 'Save As' or 'Delete.'
10. Click 'Open' to review the version of the file. Click 'Save As' to rename and create a new copy of the workbook. Click 'Delete' to delete this version.

Read more ►

How to Change Column Width in Microsoft Excel 2003

1. Select the column that you wish to resize. To select a column, left-click on the column letter, located at the top of the column in the spreadsheet and labeled alphabetically from left to right. Once you click on the column letter it will highlight the entire column.
2. Resize the column using the drag arrows on the column head. Hold the cursor over the left or right side of the column header . An arrow will appear. Simply left-click and you can drag the column to the desired width.
3. Set the column width using the column properties menu. Once you have highlighted the column, right-click to access the column properties menu.
4. Implement column width changes using the column properties menu. Enter in the desired width of the column in pixels into the column properties menu width box and click “Okay” to implement.

Read more ►

How to Create Sequential Numbers on an Excel Document With Software

1. Type the first number of your sequence into the cell corresponding to the top position of your list. For example, if you wanted to start your numbering sequence in the top left corner of the spreadsheet, you could type '1' into cell 'A1.'
2. Type the second number of your sequence into the cell corresponding to the second position of your list. For example, you could type '2' into cell 'A2.' Add more numbers to your sequence if the numbering pattern isn't recognizable after two numbers.
3. Click on the top cell in your sequence and drag your mouse to select the rest of your starting values.
4. Click the 'fill handle' in the bottom right corner of the bottom cell, then drag the handle down until all of your required cells are included within the selection. For example, if you entered '1' and '2' into cells 'A1' and 'A2' respectively and wanted to create a 20-number sequence, you would drag the handle to cell 'A20.'
5. Release the left mouse button to fill your selected cells with a list of sequential numbers.

Read more ►

How to Rename Columns in Microsoft Excel Queries

Customize Column Labels
1. Open Microsoft Excel.
2. Click on the column you wish to rename to highlight it.
3. Click 'Insert' on the toolbar at the top of the screen to open a drop-down menu.
4. Click 'Name.'
5. Click 'Define.'
6. Type the name you wish to give the column.
7. Click 'OK' to save the new label. Repeat these steps for each column you wish to rename.
Rename Column Labels With Numbers
8. Open Microsoft Excel.
9. Click 'Tools' to open a drop-down menu.
10. Click 'Options.' A new window will open.
11. Click on the 'General' tab to display a list of options.
12. Uncheck the box next to 'R1C1 reference' to rename the columns with numbers instead of letters. To return to using alphabetical labels, check the box.

Read more ►

How to Delete Commas From Excel 2007

1. Double-click on the cell that you want to edit.
2. Position the cursor directly behind the comma that you want to delete. Click the mouse once in this spot.
3. Click the 'Backspace' button on the keyboard to remove the comma. If there are multiple commas in the cell that you want to delete, use the arrow keys to move over to them and hit 'Backspace' to delete them.
4. Click 'Enter' when you are finished making changes to the cell.
5. Repeat the process for any additional cells you wish to edit.

Read more ►

How to Insert Copy a Formula in Excel 2007

1. Open Excel and the worksheet in question. Position the cursor in the cell in which you wish to insert the formula.
2. Click on the Formulas tab at the top of the Excel window. Choose the proper category for the formula you will be using, and then select the formula that you wish to insert from the list that appears.
3. Enter the cell references as prompted in the Function Arguments dialog box that appears.
4. Copy any of your existing formulas by first selecting the cell that contains the formula you wish to copy.
5. Click on the Home tab at the top of the Excel window, and select the Copy icon. Select Paste if you would like to paste both the formula and the formatting, and click on Paste Special if you want to paste the formula only.
6. Copy your formula into adjacent cells by using the Excel fill handle. This is a small black square that is located at the lower-right corner of the cell or group of cells you have selected. Move your mouse over it, and when the pointer turns to a black cross, left-click and drag the fill handle over the range of cells into which you wish to copy the formula.

Read more ►

How to Enable a Ribbon Customizer

1. Launch the Microsoft Office application for which you want to customize the Ribbon interface.
2. Right click anywhere on the Office Ribbon, then click 'Customize the Ribbon.'
3. Click the 'New Tab' button at the bottom of the right side of the 'Options' screen. The application creates a new section labeled 'My Tab' in the right pane of the window.
4. Drag and drop desired command functions and options for the Ribbon Tab from the drop-down menu on the left side of the 'Options' screen into the 'My Tab' section of the right-side window pane.
5. Click the 'OK' button at the bottom of the screen after you finish dropping desired function and option commands into your new customized Ribbon Tab. Once you return to the main Office application screen, the new tab is immediately available for use within the program.

Read more ►

How to Make a Time Line in Excel 2003

1. Open a blank worksheet in Microsoft Excel. Click 'View' on the toolbar and click 'Header and Footer'. The 'Page Setup' box will open. Click the 'Custom Header' button and enter the header you want printed on your page. Format it using the 'Font' button. Click 'OK' to close the 'Header' box, but don't close the 'Page Setup' box.
2. Click the 'Page' tab of the 'Page Setup' box. Under 'Orientation', select the 'Landscape' button. Click 'OK' to close the 'Page Setup' box.
3. Select all of the cells on your current page in row 7 using your mouse. Click on the 'Fill Color' button on your toolbar (which looks like a pouring paint can) and choose a fill color for those cells.
4. Select all of the cells in row 6 by clicking on the number 6 with your mouse. Right-click and choose 'Format Cells'. Click the 'Alignment' tab of the 'Format Cells' box. Under 'Orientation', move the 'Text' to 90 degrees. If desired, click the 'Font' tab and make changes to the font for those cells. Repeat these steps for row 8, except change the 'Text Orientation' to -90 degrees. This will align your text up and down from the timeline bar.
5. Enter times in the bar itself and enter data in the corresponding cells in rows 6 and 8. If desired, you can add clip art pictures by going to the 'Insert' menu, pointing to 'Pictures' and clicking on 'Clip Art'. Search and enter pictures that suit your timeline. When you are finished, you can print your personalized timeline.

Read more ►

How to Do a Frequency Distribution in Excel 2007

1. Click the 'Data' tab and then click 'Data Analysis.'
2. Click 'Histogram' and then click 'OK.'
3. Type 'A1:A2' into the 'Input Range' box, where A1 and A2 represent the actual range of cells on your worksheet where your input data is. For example, if your range of data is in A1 to A10, type 'A1:A10'
4. Type 'B1:B2' into the 'Bin Range' box where B1 and B2 represent the range of cells where your bin data is located. Bins represent the groups you want your data to fall into. For example, if you want your data to fall into bins of 1-10 and 11-20 and those values are located in cells C1 and C2, then type 'C1:C2' into the Bin Range box.
5. Click 'New Workbook' under 'Output Options,' then click the 'Chart Output' check box. Click 'OK.' Excel will create your frequency distribution table in a new workbook along with an embedded chart.

Read more ►

How to Turn on the Developer Tab in Excel 2007

1. Open the Microsoft Excel 2007 file for which you want to open the Developer tab.
2. Click on the 'Microsoft Office' button in the top-right corner of the application.
3. Click on the 'Excel Options.'
4. Click on the 'Popular' option and then click on the box next to the 'Show Developer Tab in the Ribbon' field so that it's selected.
5. Close the dialog box and then click on the Developer tab in the ribbon at the top of the application. You now have the Developer options available to you.

Read more ►

How to Freeze (Lock) Column Headings in Microsoft Excel

1. Launch Excel and open your spreadsheet.
2. Select the 'View' tab at the top of the Excel window.
3. Click the 'Freeze Panes' button. This will open a drop-down menu.
4. Click 'Freeze Top Row.'

Read more ►

How to Span Columns in MS Excel

1. Highlight the cells you want to merge by holding 'Shift' and moving the arrow keys to highlight the data.
2. Right-click on a highlighted cells and select 'Format Cells.'
3. Click 'Alignment.'
4. Check the box next to 'Merge Cells.'

Read more ►

Location of Excel 2003 Commands in Excel 2007

1. Click the Microsoft Interactive: Excel 2003 to Excel 2007 command reference guide.
2. Click 'Start the Guide.'
3. Click 'Start.'
4. Click the Excel 2003 command that you want to find in Excel 2007. For example, click the 'Autosum' command. The program will open a pop-up window showing you where you can find the command in Excel 2007.

Read more ►

How to Use Excel's YEARFRAC Function

1. Install the Analysis ToolPak, if needed. If YEARFRAC returns the #NAME? error value, select Add-Ins from the Tools menu. Mark the checkbox next to Analysis ToolPak and click on the OK button to install it.
2. Learn the syntax of YEARFRAC. It is YEARFRAC(start_date, end_date[, basis]) where start_date is the first date and end_date is the last date of the desired date range. Basis is an optional argument that specifies the method to use for counting the days.
3. Enter start_date and end_date using the DATE function or some other function that returns a date to prevent the problems that can occur when a date is entered as text. YEARFRAC will return the #VALUE! error value if any of its arguments is not a valid date.
4. Specify basis, if needed. The default is zero and indicates the days in the month are to be counted with the United States' 30-day method and a 360-day year. A 1 uses the actual number of days in the year. A 2 uses the number of days in the month with a 360-day year while a 3 assumes a 365-day year. A 4 is the same as 1 but uses the European 30-day method.
5. Study an example of YEARFRAC. =YEARFRAC(DATE(2007,1,1),DATE(2007,7,30),2) returns 0.5833333333. There are 210 days separating these two dates and with a 360-day year, we have a year fraction of 210/360 = 7/12 or 0.5833333333.

Read more ►

How to Remove the Contribute Toolbar

Disabling the Contribute toolbar in Internet Explorer and Firefox
1. Click the \'Tools\' menu and click \'Manage Add-ons\' (Internet Explorer) or \'Add-ons\' (Firefox). A new window launches.
2. Select \'Adobe Toolbar\' and select \'Disable\' in the settings section if you are using Internet Explorer. Select \'Disable\' in he extensions section if you are using Firefox. Select \'ContributeBHO Class\' if you are using Contribute CS3, and set it to \'Disable\' (Internet Explorer) or click the \'Disable\' button (Firefox). Contribute disables the contributeplugin.dll file.
3. Click \'OK\' to save the changes. You should no longer see the Contribute toolbar when you launch Internet Explorer or Firefox.
4. Click the Windows \'Start\' menu and click the \'Control Panel\' if you want to uninstall the Contribute toolbar completely if you are using Contribute 4. Double-click \'Add or Remove Programs\' to launch the programs window if you are using Windows XP. For Windows Vista and 7 users, click \'Programs\' and click \'Programs and Features\' to launch the programs window.
5. Select \'Adobe Contribute 4\' and click \'Remove/Uninstall.\' The uninstallation wizard launches. Click \'Next.\' Select \'Modify for Contribute 4\' and click \'Next\' again. Deselect the Contribute toolbar and click \'Next.\' Click \'Next\' again and click \'Install\' to remove the Contribute toolbar.
Disabling the Contribute toolbar in Microsoft Office
6. Close Contribute and any Microsoft Office programs.
7. Double-click \'My Computer\' on the desktop. Navigate to the \'Program Files\\Adobe|Contribute 4\' if you are using Contribute 4. Navigate to \'Program Files\\Adobe\\Adobe Contribute CS3\' if you are using Contribute CS3.
8. Right-click the \'OfficePlugin.dll\' file and click \'Rename.\' Rename the file \'OfficePlugin.old\' (without quotes).

Read more ►

How to Insert a Static Date in Excel

1. Open Microsoft Excel 2007, and start a new spreadsheet that you would like to insert a static date into or open an existing spreadsheet from your files into which you want to insert a static date.
2. Click to select the cell to which you want to insert the static date. The cell will then be surrounded by a thick, black outline indicating it is ready for you to insert the static date into it.
3. Use your keyboard to enter the shortcut of 'CTRL' ';' into the selected cell. Both the 'CTRL' key and the semicolon key must be pressed at the same time. This keyboard shortcut will tell Excel to enter a static date into the selected cell.
4. Watch as the current date appears in the selected cell. This date will remain static, not being updated at any time during the life of the spreadsheet.
5. Utilize the procedure that is outlined above to insert the static date into any other cells in the spreadsheet where you would like to display a static date.

Read more ►

How to Group and Outline Excel Data

1. Open your Excel spreadsheet. Organize the data to be outlined. Make sure the fields you want to group are adjacent to each other. Insert summary rows or columns beneath or beside the fields that you will be outlining. Summary rows or columns should contain formulas to average or sum up the fields to be outlined.
2. Highlight the series of columns or rows you want to group. Select rows or columns by clicking on the gray letter or number field at the side or top of the spreadsheet.
3. Choose 'Data' from the menu. Choose 'Group and outline.' Select 'Group.' You'll see a gray box on the left side of the spreadsheet, with a line beside the rows of grouped Excel data. If you grouped columns, you will see the gray box and the line at the top of the spreadsheet.
4. Click the '-' to collapse or hide the grouped fields, and the ' ' to expand or display them.
5. Select the rows and columns that you have grouped. Click 'Data,' 'Group and outline' and 'Ungroup' to remove the grouping. To remove all groups and outlining, click 'Data.' Click 'Group and outline' and choose 'Clear outline.'

Read more ►

How to Make a Graph for Density in Excel With Microsoft

1. Select the two Excel spreadsheet columns that contain density graph data, and then click the 'Insert' tab on the Excel main menu.
2. Set the Excel graph to a “Scatter with only Markers” type by selecting this option from the drop-down menu on the 'Charts: Scatter' section of the Excel Ribbon menu. This is the best option for a density graph, not only because you are comparing pairs of values, but also because each pair represents a separate measurement.
3. Right-click anywhere on the graph, and choose “Select Data” from the resulting pop-up window. The 'Select Data Source' window will open, and 'Series 1' will appear as the only entry in the Legend Series column on the left.
4. Click the Series 1 entry, and then click the Edit button to close the 'Select Data Source' window, open the 'Edit Series' window, and enable Chart Tools on the Excel Ribbon. Verify that mass data is on the y-axis and volume data is on the x-axis.
5. Locate and open the set of Chart Layout types -- the third section from the left on the Ribbon -- by clicking the down-arrow button. Select Layout 9.
6. Double-click on the Chart Title to change the default title to one that is more appropriate. Click on and change the vertical y-axis title to Mass (g) and the horizontal x-axis title to Volume (ml), substituting units of measurement if yours are something other than grams and milliliters.
7. Draw a straight line -- called a trendline -- through the data by right-clicking on any data point and selecting 'Add Trendline' from the resulting pop-up box.
8. Select 'Linear Regression,' check the Set Intercept box, and set the intercept to zero. This action will ensure the trendline is the best fit for your data. By default, Excel will display the equation you are working with on the graph as well as its R2 value. Uncheck these boxes if you do not want to display this information.
9. Make final adjustments to your density graph, using 'Format Axis' and 'Format Size' options. These allow you to change line thickness and color, and increase the size of the graph.

Read more ►

How to Use Multiple Data Sources in One PivotTable

1.
Data with the same headers can be stacked in longer columns.
Prepare the data to consolidate by making sure the source spreadsheets all have the same column headers and row names. This will enable you to place the correct data in the correct column during the consolidation (see Resources).
2.
The Pivot and Chart Wizard can expedite tasks.
Click a blank cell in the Excel spreadsheet where you will create the consolidation.Hold down 'Alt' 'D' 'P' to open the 'PivotTable and PivotChart Wizard.' If you plan to use this wizard often you can add it to the 'QuickAccess' toolbar by clicking the tiny arrow at the very top of Excel to the right of the 'Save' icon. Click 'More Commands,' then under 'Choose Commands From,' select 'All Commands.' Click 'PivotTable and PivotChart Wizard,' then click 'Add,' then click 'OK.'
3. Click 'Multiple Consolidation Ranges' in the 'PivotTable and PivotChart Wizard' then click 'Next.' On 'Step 2a' click 'I will create the page fields,'' then click ''Next.' On ''Step 2b'' first open the first worksheet that contains the data. Click ''Collapse Dialogue,'' if it appears, for each cell range you select in the next step. Select the cell range, then click ''Expand Dialogue,'' then click ''Add.' Where the wizard asks 'How many page fields do you want?' click ''0,'' then click ''Next.''
4. In ''Step 3'' you will be asked to select a location for the PivotTable. Select a location for the first spreadsheet, then click ''Finish.''
5. Repeat this process for each cell range in each of the multiple spreadsheets, appending them by the column names which should be the same on each spreadsheet. When the last spreadsheet is consolidated you are ready to begin using the multiple data sources in the PivotTable.

Read more ►

How to Delete Duplicates in Excel

1. Place your list into Excel. Whether you type it or import it from another location, make sure it is in Excel and each entry is in its own row. This will let the program automatically delete duplicate entries.
2. Click the 'Data' tab on the top of the screen. This will bring up the 'Data' menu. Look for the 'Data' tools section of the toolbar.
3. Select the range of cells containing duplicates. If there are data in adjacent rows or columns, Excel will ask if you want to include that data.
4. Click on the 'Remove Duplicates' button. A dialog box will come up asking which column you want to remove the duplicates. If there are data in adjacent rows or columns, Excel will ask if you want to include that data. Make sure the correct column is chosen. If you are looking to delete a duplicate that corresponds to an entire row, make sure you only choose the one row or column with the data you want to delete.
5. Click 'OK.' The duplicate data will be deleted and a dialogue box will appear informing you of how many duplicate entries were deleted and how many unique values remain. Click 'OK' to return to the spreadsheet to continue working.

Read more ►

How to Add a Trend Line on an Excel Spreadsheet

Excel 2003
1. Create a chart from the data with which you want to make a trend line. Press the 'F11' key to create a chart using your default chart type, or click the 'Chart Wizard' button on the 'Standard' toolbar and follow the steps to create a basic chart.
2. Select the data series in the chart from which you want to create a trend line.
3. Go to the 'Chart' menu and select 'Add Trendline' The 'Add Trendline' dialog box will open.
4. Go to the 'Type' tab. Select the type of trend line you want to use and click 'OK.'
Excel 2007 or 2010
5. Open the Excel spreadsheet with the data from which you want to create a trend line. Select the data and make a basic chart. Press the 'F11' key to create a chart using your default chart type, or go to the 'Insert' tab and select a bar or column chart type in the 'Chart' group.
6. Click on the chart area to activate Excel's 'Chart Tools.'
7. Go to the 'Layout' tab under 'Chart Tools.'
8. Click the 'Trendline' button in the 'Analysis' group. Select the type of trend line you want to add.

Read more ►

How to Master Excel 2007 Pivot Tables

1. Open or create an Excel spreadsheet. The sheet needs to contain 20 or more rows of data plus column headings, which are required to create pivot tables.
2. Select the data you want to include in your pivot table. Either highlight the data before creating the pivot table or select the data during the pivot table creation process (See Step 3.)
3. Select the “Tables” group and choose “Insert.”
4. Select “PivotTable” and select “PivotTable” again.
5. Follow the prompts to create a pivot table. If you want to use data from an external source, choose “Use an external data source.”The default is to use data from the existing spreadsheet. Type in the cell range you want to include (not needed if you highlighted data beforehand) and finish the pivot table wizard to create a pivot table along with a provided field list based upon the range you chose.
6. Drag field names to the corresponding sections in the field list to organize the data you want summarized. The four main areas include Report Filter, Column Label, Row Label and Values.
7. Check each field name you want added to the pivot table. If you did not drag a field name to an area name, right-click the field and choose the area you want to add it to.
8. Practice adding fields to different areas of the pivot table to master the purpose of each area and how a pivot table is laid out.
9. Go to the “View” menu in the pivot table field list to change how the pivot table field list is viewed.
10. Change the way data are summarized by right-clicking a field heading or section in the pivot table and choosing “Field Options.” This includes changing various calculations such as average, count and sum.

Read more ►

How to Save a Workbook With a Password in Excel

Password Protect Workbook
1. Double-click on the Excel document to open it up.
2. Click on the 'File' tab at the top of the screen. Select 'Info' from the menu that covers the left side of the window.
3. Click on the 'Protect Workbook' button in the 'Permissions' area of the screen. Choose 'Encrypt with password' from the list of options. Enter in your desired password and click 'OK.' Enter the password again to confirm it, and press 'OK.'
Protect Document Elements
4. Double-click on the Excel file to open it up. Select the worksheet that you want to protect from the list of worksheets at the bottom of the Excel window.
5. Select the 'Review' tab at the top of the screen. Locate the 'Changes' area of the ribbon and select either 'Protect Sheet' or 'Protect Workbook.' Protecting the sheet will allow you to lock down the particular worksheet you are on from any changes. Protecting the workbook will prevent anyone from making changes to the overall design of the document, like adding new worksheets or altering the name of a current worksheet.
6. Place check marks next to the items that you want to allow people to alter. Anything without a check mark will be locked down. If you chose 'Protect Workbook,' do the opposite, as a check mark will signify to lock down that aspect of the workbook.
7. Type in a password into the field at the top of the window. Press 'OK' to continue. You will have to enter the same password again, and press 'OK' again to lock down the document.

Read more ►

How to Move a Column in Microsoft Excel

1. Open the Excel workbook to the worksheet containing the column you want to move. If you are moving it to a different workbook, open the second workbook to the target worksheet as well.
2. Click in the column header for the column you want to move. Use the built-in Excel column header with the pre-assigned letter, not a user-entered column header. Clicking the built-in header will select the entire column.
3. Press the 'Ctrl' and 'X' keys simultaneously to cut the column from its original location.
4. Click the column header to the right of where you want the column inserted, whether it is in the same worksheet or elsewhere. This will highlight the column you just clicked.
5. Press 'Ctrl' and 'V' simultaneously. This will paste the column to the left of your highlighted area, moving it to your target location.

Read more ►

How to Create a Curved Line Graph in Excel or Word

Microsoft Excel
1. Open Microsoft Excel. Highlight the data you want to insert in a graph.
2. Click the 'Insert' tab from the menu at the top of the page. You can insert pictures, clips and graphs here.
3. Look to the 'Charts' section on the drop-down menu. Select 'Line' and then choose either a 2-D or a 3-D line chart depending on your preferences. Your data will appear in a line graph.
4. Right-click on the line in the graph and select 'Format Data Series.' This is where you will choose options to make the line in the graph curved.
5. Select 'Marker Line Style' from the options at the right of the box that opens. Check the box that says 'Smoothed line' and click 'Close.' You will now have a curved line graph on your spreadsheet.
Microsoft Word
6. Open Microsoft Word. Click 'Insert' from the toolbar at the top.
7. Select 'Chart' and when the charts options box opens, choose 'Line' and click 'OK.'
8. Enter the data for the chart in the spreadsheet that appears. After you enter the data, close out the spreadsheet. The data will be applied to the graph on your document.
9. Right-click on a line in your graph and select 'Format Data Series.' Select 'Marker Line Style' and check the box that says 'Smoothed line.'
10. Click 'Close.' You will now have a curved line graph on your Word document.

Read more ►

How to Zip an Excel File

1. Open 'My Documents' in Microsoft Windows XP or earlier. If you are running Windows Vista, open 'Documents.' Both of these are usually found on the 'Start' menu.
2. Navigate through the folders until you locate the Excel file you want to zip.
3. Right-click on the file icon or name you want to zip and select 'Send To' from the pop up menu that appears.
4. Select 'Compressed (zipped) folder' from the sub-menu. A new zipped file will be created in the same folder. It will keep the same file name, but with a '.zip' extension, as your Excel file. This is the default, but you can change it just like any other file name.

Read more ►

How to Use Excel for Correlation of Data From Three Sensors

1. Arrange the data from the three sensors into columns in the Excel spreadsheet. To illustrate how to find the correlation, dummy numbers will be used as an example.
2. In column A, in cells A1 to A10, enter the following numbers:
0.33
0.05
0.77
0.90
0.25
0.66
0.30
0.12
0.05
0.85
3. In column B, in cells B1 to B10, enter the following numbers:
0.54
0.18
0.29
0.01
0.60
0.05
0.01
0.89
0.02
0.36
4. In column C, in cells C1 to C10, enter these numbers:
0.32
0.95
0.50
0.52
0.91
0.75
0.83
0.01
0.11
0.15
5. Make sure the Data Analysis add-in is switched on. Go to the 'Tools' setting on the menu bar, click 'Add Ins,' then check the box next to 'Analysis ToolPak.'
6. Click on the 'Tools' menu selection again, then scroll down to 'Data Analysis.'
7. Click on 'Data Analysis,' scroll down to 'Regression,' then click 'OK.'
8. Put the cursor in 'Input Y Range' then shade cells A1 to A10.
9. Put the cursor in 'Input X Range' then shade cells B1 to C10.
10. Click on 'OK' and the spreadsheet will produce a regression output data table automatically.
11. Look under regression statistics. The third number down is called 'Adjusted R Square.' This number represents the correlation of these three columns of data, which in this case are slightly negatively correlated with a value of -.16.

Read more ►

How to Copy and Paste Multiple Cell Contents in Microsoft Excel 2003

1. Highlight the cells that you wish to copy. Simply left-click on a cell and hold the mouse button as you drag the cursor over the multiple cells you wish to copy. Then let go of the mouse button and the cells will stay highlighted.
2. Copy the cells. To do this, scroll to the “Edit” tab on the command bar and select “Copy.”
3. Highlight the cells you wish to paste into. Find the group of cells that you wish to paste into; they must number the same as the cells you copied. Highlight the cells that you wish to paste into by following the directions in Step 1.
4. Paste into the cells. To paste your cells into the new cells, scroll to the “Edit” tab on the command bar and select “Paste.”

Read more ►

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 ►

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 ►