How to Create a Form for the Web in Excel

1. Go into Microsoft Excel and open the worksheet you want to make into a Web form.
2. Open the Tools menu and select Wizard, then select Web Form.
3. Follow the instructions in the Web Form wizard.
4. In the second set of instructions, select which cell or range of cells you need data for.
5. Click Next.
6. Select your server type.
7. Click Next.
8. Enter the location and file name of the Web page you're adding the form to.
9. Click Next.
10. Enter your closing message. This message will appear after the Web user enters the required data.
11. Follow the instructions in the Web Form wizard.
12. Click Next.
13. Click Finish.

Read more ►

How to Set Up Monte Carlo in Excel 2003

1. Access the Monte Carlo program by either placing the CD in your computer's CD drive or by connecting to the program's network drive if the program is located on a network server. Wait a minute or two while the program loads onto your system.
2. Click 'Start' and go to 'All Programs.' Click on 'Microsoft Excel' to launch the program.
3. Click the 'Tools' tab on the main menu bar and choose 'Add-Ins.' The add-in manager will open. Check 'Monte Carlo' in the add-in list. If the program is not in the list, click 'Browse' and locate the 'MCSim.xla' file under 'Computer' in the removable storage section. Click 'OK.'
4. Click 'Yes' to copy the program to the Excel add-in folder. Monte Carlo is now set up in the Excel program. Click 'OK' to launch the add-in.

Read more ►

How to Convert New Excel From R1C1 to A1

1. Double-click the Excel file you want to work with to open it using Excel 2010.
2. Click the 'File' tab at the top of the Excel ribbon, then click 'Options' from the bottom of the list on the left side of the screen. The Excel Options window appears.
3. Select 'Formulas' from the list on the left side of the window, then look for the 'Working with formulas' area on the right side of the window. Click the check next to 'R1C1 reference style' to remove the check.
4. Click 'OK' to close the Excel Options window. Your spreadsheet now uses the A1 reference style and Excel automatically changes all the current references to the A1 style.

Read more ►

How to Create a Pivot Table in Excel 2010

Use Excel Data
1. Open the Excel 2010 file that holds the data you want to make into a PivotTable.
2. Check the top of each column to ensure that your data has headers. If any columns do not have headers, type a word into the cell atop the column that briefly describes the data. If you don't have any headers at all, right-click on the row number for the top row in the data field and choose 'Insert' from the pop-up menu. You can then enter headers into the new row.
3. Click any cell within the data field. Select the 'Insert' tab at the top of the screen, and then click the 'PivotTable' button on the left end of the ribbon.
4. Choose whether to place the PivotTable on a new worksheet or an existing worksheet at the bottom of the small window that appears. If you choose an existing sheet, you will then need to click on the worksheet and cell where you want the PivotTable to go. Click 'OK' to continue and a blank PivotTable will appear.
5. Place a check mark next to any fields you want to include on the PivotTable on the 'Field List' that appears on the right side of the Excel window. As you add fields, Excel will automatically place them into one of the four PivotTable areas, represented by the four small boxes at the bottom of the 'Field List.' You can then drag and drop the fields from one box to another to change how they appear on the PivotTable.
Use External Data
6. Open the Excel 2010 spreadsheet where you want to create the PivotTable.
7. Select the cell where you want to insert the PivotTable. Select the 'Insert' tab and click the 'PivotTable' button.
8. Select the radio button next to 'Use an external data source.' Click 'Choose Connection,' located in the middle of the window.
9. Select your desired connection from the list of existing connections that appears. Click 'Open' to continue. If you don't see your desired connection, click the 'Browse for More' button and then locate the data source you want to use. Click 'Open' once you have selected the data file, and then 'Open' again.
10. Click 'OK' to create the blank PivotTable. Place check marks next to the fields that you want to add to the PivotTable and Excel will automatically assign them to one of the four PivotTable categories, which are located in four boxes beneath the field list. You can drag and drop fields from one box to another to alter how the PivotTable looks.

Read more ►

How to Use Excel's AveDev Function

1. Learn the syntax of AVEDEV. It is (number_1,number_2,...,number_n) where number_1,number_2,...,number_n are 1 to 30 arguments for which the average of the absolute deviations using the arithmetic mean will be determined.
2. Study the valid arguments for AVEDEV. They must be names, numbers or arrays and references that contain numbers. Logical values and text representations of numbers that are entered directly into the argument list also will be included. FALSE is implicitly converted to 0 and TRUE is implicitly converted to 1.
3. Examine the way AVEDEV uses arguments that are arrays or references to arrays. Empty cells, logical values and text will be ignored. Cells with the value zero will be included, however.
4. Determine the result for AVEDEV. It is determined by first calculating the arithmetic mean m where m = t/n such that t is the population total and n is the population size. The average deviation is then given by the sum of |x-m|/n for all members x of the population.
5. Look at simple example of AVEDEV.=AVEDEV(4,5,6,7,5,4,3) returns 1.020408. The arithmetic mean m is (4 5 6 7 5 4 3)/7 = 34/7 = 4.857143, so the average deviation is (|4-m| |5-m| |6-m| |7-m| |5-m| |4-m| |3-m|)/7 = 7.142857/7 = 1.020408.

Read more ►

How to Delete Redundant Cells in Excel

1. Right-click the Excel file you want to edit, and click 'Open With.' Click 'Microsoft Excel' in the list of programs.
2. Highlight all the cells you want to check for redundancy. You can highlight all cells at once by clicking the corner square in the upper-left corner of the spreadsheet.
3. Click the 'Data' tab and click the 'Remove Duplicates' button. A window opens prompting you for a list of columns on which you want to run the duplication utility.
4. Click the 'Select All' button if you want to check all columns. Otherwise, check each column you want to check.
5. Click 'OK' to remove the redundant cells. Excel returns a report of how many cells were deleted from the spreadsheet.

Read more ►

How to Convert Lotus to Excel 2007

1. Open the file you need to convert in Lotus. Click on the 'File' menu button in the top bar then click on 'Save As.' This will open up a save dialog box.
2. In the dialog box, browse folders until you find the location you want to save the file in and rename the file. Click on the drop down menu next to 'Save as Type' and save the file as a .wk1, .wks, or .123 file. These are earlier versions of Lotus files.
3. Download the OpenOffice suite at openoffice.org. OpenOffice is a free set of office productivity programs which includes a word processor, spreadsheet, presentation, database, drawing program and a calculator.
4. Double click on the OpenOffice icon to bring up the launch screen. Click on the button 'Spreadsheet' to open the spreadsheet program.
5. Click on the 'Open' button in the top menu bar (it looks like an file folder). Search for the file you saved, click on it to select it, and then click 'Open' to open the file in OpenOffice.
6. Click on the 'File' menu in the top bar to open a drop down menu. Click on 'Save As' to open the 'Save File' dialog box.
7. Browse though the folders to determine where you would like this file to be saved and rename the file. Click on the drop down menu next to 'Save as type' to bring up a list of acceptable file types. Click on 'Microsoft Excel (.xls).' Click on 'Save.'
8. Open Excel 2007. Click on the Windows icon at the top left of the screen and click on 'Open.' Browse through the folders until you find the file you saved in OpenOffice, click on it to select it, and click on 'Open' to open it in Excel.

Read more ►

How to Calculate Discount Levels on an Excel Spreadsheet

1. Open Microsoft Excel. Click the 'A1' cell in the blank document. Type 'OP' in the cell to label the original price column.Click the 'B1' cell. Type 'SP' in the cell to label the sale price column.Click the 'C1' cell. Type 'Discount' in the cell to label the column that will show the discount level of the sale.
2. Click the 'A2' cell, and type in the original price of your item---the price the item sells for before tax. For example, type '50' if the original price of the item is $50.Click the 'B2' cell, and type in the sale price of your item---the price the customer actually pays, or the advertised price of the item. For example, type '20' if the sale price is $20.
3. Click the 'C2' cell. Type '=(B2-A2)/ABS(A2)'---the formula that will automatically calculate the discount level---into the cell.Click the 'D1' cell to apply the formula.Click the '%' sign under the 'Number' category in the top toolbar to change the number displayed to a percentage. Your percentage discount will now display. For example, if you entered '50' for the original price and '20' for the sale price, the discount column will show '-60%' to reflect a 60 percent discount on the item.

Read more ►

How to Repeat Header Rows in Excel

1. Launch Microsoft Excel and open the worksheet that you want to use.
2. Click the 'Page Layout' tab, and then click 'Print Titles' in the Page Setup group. The Page Setup window opens displaying the Sheet tab.
3. Click the 'Collapse Dialog' button in the 'Rows to Repeat at Top' box.
4. Select the row that contains the header you want to repeat, and then click the 'Collapse Dialog' button. The row reference automatically populates the 'Rows to Repeat at Top' box. Click 'OK' to save the settings.

Read more ►

How to Insert Drop

1. Open your workbook to a blank page or add a new worksheet. To keep your active worksheet tidy, you will want your list stored on a separate sheet from where the drop-down box is.
2. Enter your items for the drop-down list in a single column on the blank worksheet. For easy identification, add a title to your list in the row before your list begins, such as 'Colors' for a list of colors. Your list will appear in the order you have it in this column.
3. Highlight your list of items with your mouse, not including the title. With these highlighted, click in the box to the left of the formula bar and type in a name for your list. Use a name that matches your list title exactly, including case, to make the name easier to find if you have multiple lists. This process creates a name for your range of cells for the computer to identify. For example, if your list is titled 'Colors' in cell A1 and your list includes 'Red,' 'Blue' and 'Green' in cells A2 through A4, respectively, highlight A2 through A4 and type 'Colors' in the upper left box to name that cell range.
4. Press 'Enter' to set the name for your list. If you do not press 'Enter,' the name does not apply.
5. Select the worksheet where you want the drop-down list to appear, and click inside the cell where you want the list. Single-click in the cell only; you cannot add data validation while editing a cell, which is accessed by double-clicking.
6. Select the 'Data' tab on the Office ribbon, and then choose 'Data validation' in the 'Data tools' area.
7. Click 'List' from the drop-down menu under 'Allow' in the dialog box, and make sure 'In-cell drop-down' is selected.
8. Click inside the 'Source' box. Type an equal sign, and then type the list name exactly as you entered it on the other worksheet. For example, type '=Colors' (without quotation marks) for the list in the previous example.
9. Select 'OK.' The drop-down list is now a part of the cell, and when users click inside the cell, they will see the arrow indicating a drop-down list. Click the arrow to see the list, and then select an option to populate the cell.

Read more ►

How to Use a VLookup in Excel 2003 to Reference the Data in a CSV

1. Start Excel with a blank worksheet.
2. Click on the 'Data' menu, and select 'Import.' Select 'Text File' and navigate to where your CSV file is located.
3. Select 'Delimited' to identify the type of file. Select which row to start the import from; by default, Excel will import from the first row and work its way down. Click the 'Next' button.
4. Check the box for 'Commas' as the delimiter type on the next screen, then select 'Next.'
5. Select 'General' for the import rules; this is the most flexible option. You also have the choice of telling the import to exclude specific columns. When you've specified the import rules for each column, click 'Finish.' You'll be prompted to choose the upper left-most cell for the placed data; this defaults to cell A1. Excel will link to the CSV file, and every time Excel is re-opened, the imported file will update.
6. Press 'Ctrl End' to find the lower right most cell containing datat. If your imported file had 6 columns and 1000 rows, 'Ctrl End' would take you to cell F1000.
7. Enter the following formula in the blank cell of your choice:
=VLOOKUP(T1,A1:F1000,2,FALSE)
8. Enter the value you want to match in cell T1, and change the range of A1:F1000 to match the actual first and last cells in the VLOOKUP table range. The part of the formula with a '2' in it indicates it will return the value found in column 2; change this to reflect the column of the data you're interested in. FALSE indicates that it's looking for an exact match on the lookup value

Read more ►

How to Delete Blank Rows in Excel

1. Make a backup copy of your spreadsheet. This is most important during the learning process.
2. Select the entire area that contains the blank rows that you want removed. For example, if you have data in rows 1 through 30, you can click on 'Row 1' then press 'Shift' and click on 'Row 30.' To select the entire spreadsheet, press the 'Ctrl' and 'A' keys simultaneously.
3. From the 'Edit' field, select 'Go To.' Alternatively, you can press the 'Ctrl' and 'G' keys simultaneously.
4. Click 'Special...'
5. Select the 'Blanks' radio button and click 'OK.'
6. From the 'Edit' menu, select 'Delete...'
7. Select 'Entire Row ' and click 'OK.'

Read more ►

How to Hide the Tool Bar Menu in Excel

1. Click the icon consisting of a down-facing arrow with a short horizontal line above it. In Excel 2007, this icon is located near the upper-left corner of the screen. In previous Excel versions, it is located near the upper-right corner.
2. Click 'Minimize the Ribbon' in Excel 2007. This hides the top toolbar. You can bring the toolbar back permanently by repeating Steps 1 and 2, or temporarily by clicking one of the menu options at the top of the screen, e.g. 'Data' or 'View'. If you are using Excel 2007, stop here. If you are using a previous version of Excel, click 'Add or Remove Buttons,' then click 'Customize.'
3. Click the 'Toolbars' tab, then remove the check from each box displayed below, e.g. 'Standard' and 'Formatting.' The toolbars disappear in real time as the checks are removed.
4. Click the 'Close' button to save your changes after removing the desired toolbars. You can bring toolbars back by right-clicking the open gray area at the top of the screen, then clicking 'Customize.'

Read more ►

How to Do Trend Charts

Insert Chart
1. Open the saved Excel worksheet.
2. Click the 'Insert' tab on the command ribbon.
3. Click and drag the cells with the categories and numeric data.
4. Click the lower-right arrow for the Charts dialog box launcher. A chart gallery appears.
5. Select a chart type that best communicates your numeric data: 'Area,' 'Bubble,' 'Bar,' 'Column,' 'Line,' 'Stock' or 'xy (scatter). Select an unstacked, 2D chart type.
6. Click 'OK.' The chart type appears over the worksheet.
Add Trendline to Chart
7. Click in the chart area. The Chart Tools ribbon appears with three tabs: Design, Layout and Format.
8. Click the 'Layout' tab.
9. Click the arrow to the right of the Chart Elements text box in the Current Selection group.
10. Click the preferred chart element to appear in the chart area. For example, click 'Legend' to insert a legend box.
11. Click the 'Trendline' button in the Analysis group. A list of options appears.
12. Click the 'More Trendline Options' link. The 'Format Trendline' dialog window opens.
13. Click the 'Trendline Options' button in the left pane. The right pane displays a trendline gallery.
14. Click one radio button in the Trend/Regression Type section: 'Exponential,' 'Linear,' 'Logarithmic,' 'Polynomial,' 'Power' or 'Moving Average.'
15. Select the options for 'Trendline Name,' 'Forecast' and other trendline details. The trendline appears on the chart.

Read more ►

How to Set the Default Font Size in Excel 2003

1. Open Excel 2003 and select the 'Tool' on the menu bar. Select 'Options.' The Options dialog box appears.
2. Click the 'General' tab. Select a font in the 'Standard Font' drop-down list. Select a size for the font using the 'Size' drop-down list.
3. Click 'OK' to confirm your changes. Excel will prompt you to restart for your changes to take place. Once you restart Excel, your new worksheets will be based on this new font.

Read more ►