Excel

Browse » Home » All posts

Saturday, September 21, 2013

How to Add up Multiplications in Excel

1. Launch Excel 2010 and open the spreadsheet that contains the products that you want to add together.
2. Click once in the cell where you want the sum of the products to appear. Click the 'Insert Function' button on the Formula bar, which opens a list of available functions. Click the 'SUMPRODUCT' option, which opens a separate window.
3. Click the 'Array1' field in the Function Arguments box. Select the range of numbers for which you want to find the sum of the products, for example 'A1:B4.' Repeat this step for each range of numbers, using a different Array field for each group. For example, select 'C1:D4' for the Array2 field. Click the 'OK' button to accept the formula.

How to Use Microsoft Excel 2010

1. Start Microsoft Excel 2010.
2. Activate Excel over the Internet if prompted.
3. Click the top-left cell on the new blank spreadsheet, and type the number '25.'
4. Press 'Enter' to save the cell contents, and move to the next cell down the column.
5. Type the number '5' and press 'Enter.'
6. Press the 'Equal' sign ('='), and immediately click once on the cell that contains the number '25.'
7. Press the 'Plus' sign (' ') on the keyboard, and immediately click on the cell that contains the number '5' and press 'Enter.' Notice that the third cell now displays the sum of 25 5, or 30. The cell also contains a formula that is shown in the formula bar, which should look like '=A1 A2.'
8. Click in a blank cell.
9. Press the 'Equal' sign ('='), and immediately click once on the cell that contains the number '25.'
10. Press the 'Asterisk' symbol ('*') on the keyboard, and click on the cell that contains the number '5' and then press 'Enter.' Notice that the third cell now displays the answer of 25 x 5, or 125. The cell also contains a formula that is shown in the formula bar, which should look like '=A1*A2.'
11. Click on the cell that contains the number '25.'
12. Type the number '50,' and press 'Enter.' Notice that the two calculated cells automatically updated to reflect the new number.
13. Click on the first cell, and hold down the mouse button.
14. Drag the mouse across the remaining cells until all four cells are highlighted, and release the mouse button.
15. Click the 'Insert' tab on the toolbar, click 'Column' in the 'Charts' group, and to create a column chart of the data, click on the first chart in the context menu that pops up.
16. Click either the cell that contains the '50' or the cell that contains the '5,' and change it to another number and then press 'Enter.' Notice that the cells containing the totals and the chart all update to reflect the new values.

How to Allow the Selection of Multiple Items in Page Area of a Pivot Table

1. Open Excel 2007 and select a workbook. Click the 'Office' button and click 'Open.' Browse your computer and locate the workbook. Double-click the workbook. The workbook opens.
2. Highlight the data you want displayed in the pivot table. Select the 'Insert' tab. Click 'Pivot Table' twice. Click 'OK.' The blank pivot table appears in a new worksheet.
3. Add fields to the pivot table by checking the fields in the right 'Pivot Table Field List.' The fields appear in the column by default. Move some of the fields to the row by dragging the field name to the 'Row Labels' in the right 'Pivot Table Field List.' Add a field to the report filter by dragging the field name into the 'Report Filter' in the right 'Pivot Table Field List.'
4. Look above your pivot table to find the report filter. Click the drop-down list and select the option for 'Select Multiple Items.' This options allows for the selection of multiple items in your report filter page area.

How to Add a Yes or No Box to an Excel Spreadsheet

Display the Developer Tab
1. Click the 'File' tab and click 'Options.'
2. Click 'Customize Ribbon' and click 'Main Tabs.'
3. Check the box labeled 'Developer' and click 'OK.' The Developer tab appears in the Excel ribbon.
Yes or No Option Button
4. Open the Excel spreadsheet that you want to add an options button.
5. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
6. Click 'Option Button' under the 'Form Controls' heading.
7. Click the cell in which you want to insert the options button.
8. Highlight the words 'Options Button 1' on the options box. Type 'Yes' and click on an empty cell in your worksheet.
9. Click 'Option Button' under the 'Form Controls' heading.
10. Click the cell in which you want to insert the second options button.
11. Highlight the words 'Options Button 2' on the options box. Type 'No' and click on an empty cell in your worksheet.
12. Right-click an options button and click 'Format Control' to edit the button's color, format, size or any other properties. Click 'OK.'
Yes or No Check Box
13. Open the Excel spreadsheet that you want to add a check box.
14. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
15. Click 'Check Box' under the 'ActiveX Controls' heading.
16. Click the cell that you want to insert the check box. This cell will include the upper left corner of the check box.
17. Highlight the words 'Check Box 1' on the check box. Type 'Yes' and click on an empty cell in your worksheet.
18. Click 'Check Box' under the 'ActiveX Controls' heading.
19. Click the cell that you want to insert the second check box.
20. Highlight the words 'Check Box 2' on the check box. Type 'No' and click on an empty cell in your worksheet.
21. Right-click a checkbox and click 'Format Control' to edit the check box's color, format, size or any other properties. Click 'OK.'

How to Specify the Templates Folder in Excel

1. Open Microsoft Excel, then click 'Tools,' followed by 'Options.'
2. Type the folder path into the box labeled 'Alternate startup file location.' To find the folder path, right-click on the folder, then click 'Properties.' The path is listed beside the label 'Location.'
3. Click 'OK' to save your settings, then restart Excel. The new settings will take effect.

How to Determine Percentages Using Division

1. Start by determining what needs to be divided by what. For example, if you want to know what percentage score you achieved on a test, you will be dividing the number of questions you got right by the number of total questions.
2. Use a calculator or a pencil and paper to do your division. Let's say you got 35 questions correct out of 50. You will enter 35 divided by 50 into your calculator. The answer is .7.
3. Multiply the number by 100 to get it in a percentage. In this case .7 multiplied by 100 equals 70, or 70 percent.

Friday, September 20, 2013

How to Make a Work Vs. Time Graph on Excel

1. Click and drag to select all of the cells containing your work and time data, open the 'Insert' tab, and then click the 'Create Chart' button and double-click on the desired type of chart from the list. The graph will now appear onscreen if you are using one of the latest versions of Excel (2007 or 2010). If you are using an earlier version of Excel, follow the onscreen prompts to finish formatting the graph.
2. Right-click on the graph, choose 'Select Data' from the context menu, click on one of the 'Legend Entries,' such as 'Series1' or 'Series2,' click 'Edit,' and then type the desired name (for example, 'Work') into the 'Series name' box and click 'OK.'
3. Change the graph type by right-clicking on the graph and selecting 'Change Chart Type.' To move the graph to a new Excel sheet, right-click on the graph, select 'Move Chart,' and then click 'New sheet.'

How to Create a Receipt in Excel

1. Open Excel. Click 'File,' 'New.' Type 'Receipt' in the search box. Click a template image to see a preview in the right task pane. Choose the template you want. Click 'Download.'
2. Highlight the default contact information on the receipt. Type your information on the receipt.
3. Type the details of the transaction on the receipt lines. Save the receipt by clicking the 'Save' icon on the 'Quick Access' toolbar.

How to Make a Graph in Microsoft Excel 2003

1. Create a spreadsheet with at least two data sets. One set will be the X-axis (independent axis) of the graph, and the other set will be the Y-axis (dependent axis) of the graph.
2. From the 'Insert' menu, click 'Chart.' The Chart Wizard will appear.
3. Select the type of chart you wish to create. A list of sub-types will appear. Select the sub-type and click 'Next.'
4. In the Chart Source dialogue box, click 'Data Range.' Select the data you wish to include for the independent and dependent axes, select 'Rows' or 'Columns' in the dialogue box, and click 'Next.'
5. From the Chart Options dialogue box, use the 'Titles' and 'Legends' tabs to define the graph axes and title. Click 'Next.'
6. Finally, use the 'Chart Location' dialogue box to determine where to place the graph in the Excel worksheet. The graph can be placed as an item in the current worksheet, or on a separate page. Click 'Finish' when you are done.
7. If, after viewing the completed graph, you wish to make changes, right-click on the graph. This will allow you to modify the various parts of the graph, including colors and fonts for the legends.

How to Use the Pivot Table Wizard in Excel 2003

1. Open your Excel worksheet. Under 'Data' on the toolbar, select 'PivotTable' in the dropdown menu.
2. In the 'PivotTable Wizard' box, select the data you want to analyze by clicking on the corresponding radio buttons. Click 'Next.'
3. In the next screen, select the 'range' you want to analyze. To define the range, drag your left mouse over the cells you want sorted. Click 'Next.'
4. In the final screen, select whether you want the pivot table to appear in a new worksheet or an existing one.
5. Click 'Finish' to insert the pivot table. Save your work.

Thursday, September 19, 2013

How to Make Cells Print in Excel 2007

1. Go to Microsoft Excel 2007.
2. Open or create the Excel worksheet you want to print. To open your file, select the Microsoft Office and click on 'Open' (shortcut: Ctrl O). To create a worksheet, click on a blank cell, type in a value and press the 'Enter' or 'Tab' key. Add additional entries you need.
3. Select the 'Page Layout' tab from the Ribbon and go to 'Gridlines' under the 'Sheet Options' group.
4. Place a check mark by 'Print' in the 'Gridlines' group to make the gridlines for your cells print. To make your gridlines visible on your page, place a check mark by 'View' as well.
5. Click on the Microsoft Office button, choose 'Print,' then select 'Print' once again. Pick the printer you'd like to use from the 'Print' dialog box, then click on 'Print.'
6. Save your work by clicking on the 'Save' button in the 'Quick Access Toolbar' (shortcut: Ctrl S). Then, name your file and click on 'Save.'

How to Use Excel's Range Finder

1. View the Excel spreadsheet for which you wish to use range finder.
2. Double-click a cell. Range finder activates.
3. View the other cells associated with your selected cell's formula range. Notice that the cells in the equation and the actual cells are color-coded.

Wednesday, September 18, 2013

How to Use Microsoft Excel 3

1. Enter two columns of data in an Excel spreadsheet. The first column contains data labels for the pie chart; the second column contains the proportions, which can be expressed as percentages or real numbers. In the latter case, Excel will calculate the percentages to build the pie chart.
2. Highlight the cells, excluding the total row, if you have one.
3. Click the 'Insert' tab.
4. Choose 'Pie' and select one of the 3-D pie charts from the drop-down menu to insert a chart based on that model into your spreadsheet.
5. Adjust the size of the pie chart and use the 'Chart Tools Design' tab options to change its look.
6. Click the drop-down menu labeled 'Chart Area' in the 'Chart Tools Layout' tab, and use the 'Chart Title' and 'Legend' items to add or edit a title or legend for the 3-D pie chart.
7. Right-click on the pie chart and choose '3D Rotation' to adjust the depth, perspective and other visual elements having to do with the 3-D look of the chart.

How to Use the AutoFill Function in Excel

Creating a Custom Fill List
1. Enter the list in a spreadsheet.
2. Open the Tools menu and choose Options.
3. Click the Custom List tab.
4. Click Import.
Using the Basic AutoFill
5. Open an Excel document.
6. Enter the first value in the cell you want to begin the series (such as 'January').
7. If your series is numerical, click the next cell you want in the series and type the next value. The difference between the two initial cells determines how the series is incremented. For example, if you entered 1 in the first cell and 3 in the next cell, the increment would be 2.
8. Select the two cells you just entered (or the first one, for a non-numerical series, such as days of the week).
9. Locate the 'fill handle,' or the dark square in the lower right corner of the cell. The pointer should change to a small dark square when it's over the fill handle.
10. Drag the pointer to cover all the cells you want in the series.

How to Replace Duplicates With Blanks in Excel

1. Launch Microsoft Excel and open your workbook.
2. Right-click the header of the column that contains the duplicates you wish to erase. Choose 'Insert' from the context menu to create a blank column.
3. Double-click the first cell in the blank column. Enter the following formula:=IF(A1='', '', IF(COUNTIF($A1:A1,A1)>1,'',A1))
4. Replace all instances of 'A' in the formula with the letter label of the column that contains duplicate cells. Highlight this cell again, and then double-click its fill handle -- the small black square at the bottom right corner of the cell -- to copy the formula to the blank cells below it.
5. Highlight all of the cells that contain the 'COUNTIF' formula. Hover your mouse cursor over the right border of the selection until it turns into a cross. Hold down your right mouse button, and then drag the arrow one column to the right. Choose 'Copy Here as Values Only' from the context menu that appears.
6. Delete the column that contains the 'COUNTIF' formula by right-clicking on its header and selecting 'Delete.'

Subscribe to: Posts (Atom)