Excel

Browse » Home » All posts

Saturday, September 21, 2013

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.'

How to Create a Clustered

1. Click 'Start,' 'All Programs,' 'Microsoft Office' and open 'Microsoft Excel.'
2. Enter your column headings as you would normally into the spreadsheet. Add your row headings, but leave one blank cell for each graph cluster column that you want.
3. Enter the data into the spreadsheet. When entering the data, use a separate row for each cluster that you want to create.
4. Click in the upper-right corner of the spreadsheet and drag the cursor so that all the cells containing data are selected. Click the 'Insert' menu at the top of the window.
5. Click 'Column' and the second option in the '2-D Column' section, which is 'Stacked Column.' Right click one of the columns in the chart, and select 'Format data series.'
6. Use the slider in the 'Gap Width' section and slide it all the way to the left to eliminate the gap between the clusters. Click 'Close.'

How to Use VBA to Delete Columns in Excel 2007

1. Click the 'Developer' tab, click 'Visual Basic' and click the 'Insert' menu. Click 'Module' to insert a new VBA code module.
2. Type the following to create a new sub procedure:Private Sub removeColumns()
3. Type the following to remove column B from the active sheet:Columns('B:B').SelectSelection.Delete Shift:=xlToLeft
4. Edit 'B:B' in the previous step, and type the column letter you want to remove. Type 'End Sub' to end the procedure, and press 'F5' to run the procedure and remove the column.

How to Create a Distribution Chart

1. Open Microsoft Excel.
2. Type the number 3.0 in the A1 cell and then type the number 2.75 in the A2 cell just below it. Highlight both cells.
3. Drag the bottom-right of the selected cells to the 25th row. Use the tiny black square as a handle. Each number will be decremented by .25, giving the successive number below it.
4. Select cell B1and enter the equation as: '=normdist (a1,0,1,0)' and hit the 'Enter' key. The Normal Distribution for the number (3) in cell A1 with a mean of zero and a Standard Deviation of 1 appears in the B1 cell.
5. Click the bottom-right of the B1 cell and drag the cursor to the 25th cell as you did in step 3. The Normal Distribution appears in each corresponding B cell for its 'A' cell number. Keep the cells from B1 to B 25 selected.
6. Click 'Insert' from the main menu and then select 'Line' and choose the first line graph from the 2D sub-selection. The chart for the Normal Distribution appears a bell-shaped line graph.
7. Select 'Column' from the main menu and choose the first column graph in the 2D sub-selection. The chart now is represented as a column graph. Experiment accordingly with each variation and ultimately decide which style best suits your presentation.

Tuesday, September 17, 2013

How to View an Excel 2007 Spreadsheet in Excel 2003

1. Learn how to exchange files between Excel 2007 and Excel 2003. The programs in the Microsoft Office suite have been upgraded to use an open XML file format in the 2007 version in order to make the files smaller, more secure and easier to use with other programs. Microsoft has created a compatibility pack which ensures that in spite of the new file format you can use newer releases with the older programs.
2. Learn to use the compatibility pack. If you have the Office 2003 version of Excel or older, you can use the compatibility pack to view, edit and save an Excel 2007 spreadsheet. You can also convert the 2007 Excel spreadsheet from the open XML format to the older binary file format.
3. Prepare to download the compatibility pack. Make sure you have one of the following operating systems on your computer: Windows 2000 Service Pack 4, Windows Server 2003, Windows Vista, Windows XP Service Pack 1 or Windows XP Service Pack 2. The compatibility pack file requires 25MB on your hard disk, so be sure you have enough space.
4. Install updates. If you have Microsoft Office 2000, Office 2003 and Office XP you will need to install high-priority updates from Microsoft.com before you download the compatibility pack (see Resources below).
5. Download and install the compatibility pack. Visit Microsoft.com to download the compatibility pack (see Resources below). You can save the executable file to your hard disk and run it at a later time or you can run it directly from Microsoft's website.
6. Open your Excel program. Click the 'File' button on the toolbar, select 'Open' and search for your Excel 2007 spreadsheet using the dialog box that pops up. Click the file to select it and then click the 'Open' button to view it in your Excel 2003 program.

Subscribe to: Posts (Atom)