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 ►

How to Find Copy Results in Excel 2003

1. Open the Excel 2003 spreadsheet in which you want to locate and copy data.
2. Type 'Ctrl' 'F' on your keyboard to open the 'Find' dialog box. Alternatively, click the 'Edit' pull-down menu near the top of the spreadsheet and select 'Find' from the list.
3. Type the data you want to find in the 'Find What' box. You can enter numbers or letters or a combination. Use the 'Options' menu to select special features, such as enabling the 'Match case' option to distinguish uppercase and lowercase letters in your search.
4. Click 'Find Next' to locate the next appearance of your search term in the spreadsheet. Alternatively, click 'Find All' to generate a list of all appearances of your search term and click an item in the list to go to that location in the spreadsheet.
5. Type 'Ctrl' 'C' on your keyboard to copy the contents of the cell you are in. Alternatively, click 'Edit' to open the pull-down menu and click 'Copy.'
6. Move your cursor to another cell, a different spreadsheet or document in another program and type 'Ctrl' 'V' to paste the copied data into the new location.

Read more ►

How to Edit the Legend in Microsoft Excel

Microsoft Excel 2007
1. Highlight a chart by clicking on it.
2. Click on the “Design” tab near the middle of the top of the page.
3. Click on “Select Data”, the fourth icon from the top left of the page. A box titled “Select Data Source” will appear.
4. Click on the name of the “Legend entry” that you wish to edit.
5. Press the “Edit” button. The “Edit” button is located directly above the list of the names of the legend entries in your chart. When you press the “Edit” button, a box titled “Edit Series” will appear.
6. Type the name that you wish to title the “Legend entry” into the box titled “Series name”.
7. Click “OK”. This will edit the legend and return you to the “Edit Series” box.
8. Add another “legend entry” to the legend by clicking on the “Add” button. The “Add” button is located directly to the left of the “Edit” button. When you press the “Add” button, another box titled “Edit Series” will appear.
9. Type the name that you wish to title the new “legend entry” into the box titled “Series name.”
10. Insert the values for the x-coordinates in the “Series X values” box.
11. Insert the values for the y-coordinates in the “Series Y values” box.
12. Click “OK”. This will edit the legend and return you to the “Edit series” box.
13. Repeat this process if you wish to add or edit any additional legend entries.
14. Click “OK” when you are finished.
Microsoft Excel 2003
15. Highlight a chart by clicking on it to modify its legend. A menu will appear.
16. Select “Source Data…” from the menu. It is the third option from the top of the menu. A box titled “Source Data” will appear.
17. Click on the tab labeled “Series”. It is one of two tabs displayed at the top of the “Chart Wizard” box.
18. Highlight the “Data series name” you wish to change.
19. Place your cursor in the text box titled “Name.” Type the name that you wish to title the “Legend entry” into the box.
20. Press the “Finish” button when you are done.

Read more ►

How to Change the Source of an Excel Pivot Table Using VBA

1. Create a PivotTable object in VBA so that you can update the source. Copy the code below and put it in your macro or function.Dim ptMyPivotTable As PivotTable
2. Connect your PivotTable object with your actual Pivot Table. Copy the code below.Set ptMyPivotTable = ActiveSheet.PivotTables(1)
3. Update the SourceData property of your PivotTable object to the new values. You can do this by copying the VBA code provided. Substitute your desired data range in place of 'A1' in the example.ptMyPivotTable.SourceData = Range('A1').CurrentRegion.Address(True, True, xlR1C1, True)To reference a range on a different sheet, you can use the following code.ptMyPivotTable.SourceData = Sheets('mySheetName').[A1].CurrentRegion.Address(True, True, xlR1C1, True)
4. Update the data actually displayed in your Pivot Table by using the VBA command below.ptMyPivotTable.RefreshTable
5. Release the resources you used in your PivotTable object by setting it equal to Nothing.Set ptMyPivotTable = Nothing

Read more ►