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 ►

How to Reset the Autofilter Area in MS Excel

1. Open the worksheet in which you would like to clear the filter.
2. Click the 'Filter' button on the column heading. It looks like a funnel in Row 1.
3. Click 'Clear Filter from
.' For example, if you have a filter in column A, click 'Clear Filter from A.'

Read more ►

How to Create Multiple Charts in Excel

1. Open up a new spreadsheet in Microsoft's Excel program.
2. Enter your data in the columns provided in the spreadsheet. For example, let us say you have three sets of data. You will use columns A, B, and C to record your data. Simply click on A1, and type your first number. Hit Enter, and your cursor will move to A2. You will follow this process until you have entered all three sets of data.
3. Click cell A1. Then, press Control and the letter A at the same time. Your three sets of data should be highlighted.
4. Click Insert on your toolbar. Experiment with different types of charts. You have the choices of Column, Line, Pie, Bar, Area, Scatter and Other Charts. Simply click the chart style you like, and it will appear on your screen.

Read more ►

How to Add Subtotals in Excel

1. Create the list of numbers to be added. Make sure to label the top of each column with a heading.
2. Highlight the column of numbers to be added by clicking on the number at the top of the column. Once all of the data is outlined and highlighted in blue, the numbers are ready to be added.
3. Go to the 'Data' tab at the top of the Excel toolbar.
4. Select 'Subtotal' from the 'Outline' section, which is on the far right side of the toolbar.
5. Confirm the column being added. Make sure that the function is set to 'Sum.' If it isn't, select 'Sum' from the 'Use Function' drop-down menu. Click 'OK' at the bottom off the confirmation screen to add the subtotals.

Read more ►

How to Restore Previous Excel Files

Files Never Saved
1. Open Excel.
2. Click 'File,' then 'Recent'.
3. Select 'Recover Unsaved Workbooks'.
4. Select your file and click 'Open'.
5. Save your file.
Previously Saved Files
6. Open your file.
7. Click 'File' then 'Info'.
8. Click 'Versions' and select the one labeled 'when I closed without saving.'

Read more ►