Excel

Browse » Home » All posts

Tuesday, September 24, 2013

How to Convert Read

1. Open the 'Read-Only' spreadsheet in Microsoft Excel 2007. You can open the file by double-clicking on it from it's current location or in Excel 2007 by clicking on the 'Office' button and then 'Open.' When the 'Open' dialog box open, click on the file and then click 'Open.'
2. Click on the 'Office' button and then 'Save As' to begin the process of saving the file in a earlier, editable version. The 'Save As' dialog box appears.
3. Click the drop-down arrow next to 'File type as' and choose 'Excel 97-2003' Workbook. Choose a file location for the file and then click 'Save.' Close the workbook.
4. Open the workbook in Excel 2003. Click on 'File > Open.' Locate the workbook and click 'Open.' You can now edit the document all you want. It is no longer locked for editing.

Monday, September 23, 2013

How to Make Three Dimensional Graphs in Excel

1. Open the Excel worksheet that contains the source data.
2. Click and drag to select the worksheet's categories and data to appear in the chart.
3. Click the “Insert” tab on the command ribbon.
4. Click the arrow under the preferred chart type in the “Charts” group -- for example, click “Pie,” “Bar” or “Area.” A gallery of thumbnail charts displays, including “3-D” for three-dimensional options.
5. Click a thumbnail chart in the “3-D” section -- for example, click “3-D Bar.” The source data converts to a three-dimensional chart.

How to Use Decimal Numbers in Excel

1. Launch Excel and open the worksheet where you want the number formatting changed to decimals.
2. Highlight the cells you want to change. If you want to use a whole column for decimals, click in the shaded box with the column's identification letter, or use the identification number box for rows to highlight the whole row.
3. Select multiple cells, rows or columns by holding down the 'Ctrl' key as you click on other cells, rows or columns after your first selection.
4. Click the 'Home' tab, and then click in the bottom right corner of the 'Number' section, where you will see a small box with an arrow. This launches a window with the full set of number options.
5. Click on 'Number' in the left menu column, and then select your preferred number of decimal places using the arrows in the field next to 'Decimal places' in the middle of the dialog box. You can also manually enter a number into the decimal places selector.
6. Choose any other options you want to modify, such as using a comma to separate digits at every 1,000 and how negative numbers are formatted, and then click 'OK.' Your numbers will appear as decimals.
7. Enter new decimal amounts as you would on any computer keypad, using the period on your keyboard to indicate a decimal point. If you have a numeric keypad on your keyboard, the decimal key there will also work.
8. Choose special decimal formatting, such as how many decimal places to show in dollar amounts, by highlighting the appropriate cells and accessing the same dialog box. Choose 'Currency' instead of 'Numbers,' and then refine your settings from there. Click 'OK' to reset the format.
9. Increase or decrease the number of decimal places that appear in your cells by highlighting your cells again, and then tapping the decimal increase or decrease buttons on the number's pane of the Home menu, just above the box and arrow icon.

How to Share Modify Excel Documents

Sharing
1. Open your workbook.
2. Click 'Review,' then 'Share Workbook.'
3. Check the ' Allow changes by more than one user at the same time' and 'This also allows workbook merging' check boxes.
4. Click 'Advanced,' then change the settings for tracking changes. Click 'OK.'
5. Save the document in a network directory.
Modifying
6. Open the workbook on the network drive by double-clicking on the workbook.
7. Click the 'Office' button, then 'Excel Options.'
8. Click 'Popular,' then 'Personalize your copy of Office.' Enter your name in the user name section.
9. Edit the data as you normally edit a workbook.
10. Save the file.

Sunday, September 22, 2013

How to Use Microsoft Excel for Payroll

1. Navigate to the Microsoft Office Payroll Calculator template page (see Resources) and click 'Download,' then 'Accept.' Excel will automatically open to the template when the download is complete.
2. Fill out the columns with the information according to the headings above each column. The date you enter will include employee names and IDs, hourly wages, state tax percentage, insurance deductions and other relevant tax deduction information. As you enter the data, Excel will automatically calculate the figures in the 'Total Taxes Withheld' and 'Total Regular Deductions' fields.
3. Click 'Payroll Calculator,' which is near the bottom of the spreadsheet. Enter the requested information about the employees' hours, including overtime, and the calculator will provide you with the total net pay for each employee.

How to Create a Dashboard in Excel 2007

1. Log on to your computer and open Microsoft Excel 2007. Open the spreadsheet you want to upload.
2. Click on the Office button and choose 'Publish' from the list. Choose 'Excel Services' from the list and choose 'Distribute the document to other people'.
3. Enter the URL of your website, followed by the name of the file, in the 'File name' box For instance, if you are saving a document called spreadsheet1 to the site xyzcorp.com/reports, you would type 'xyzcorp.com/reports/spreadsheet1.xlsx' in the 'File name' box.
4. Click 'OK' and choose 'Save'. In the 'Document type' box choose 'Reports'.
5. Go to the site where you want to add the spreadsheet and choose 'Site actions'. Choose 'Edit Page' and then 'Modify shared web part'.
6. Click 'Workbook' and then choose 'Select a Link'. Navigate to the workbook you want to add to the dashboard and click 'OK'.

How to Import Data With Excel 2007

1. Log on to your computer and open the Microsoft Excel spreadsheet into which you want to export the data. Click the 'Microsoft Office' button.
2. Click 'Open' and choose the type of file you wish to export. Double-click the file you wish to import.
3. Choose whether the file to be imported is a delimited or fixed-width file. With a delimited file, each field is separated by a particular character, generally a comma, a semicolon or a colon. This type of file is most appropriate for files where each record contains data of varying lengths. A fixed-width file is most appropriate for files where each record is the same size.
4. Review the field names Excel assigns to each field. Type a new name if you wish, then click 'Next.' Click 'Finish' to complete the import process.

How to Import an Excel Macro From 2003

Export from Excel 2003
1. Press 'Alt' 'F11' together from within Excel 2003 to open the Visual Basic Editor (VBE).
2. Click on a macro name in the left sidebar to open the module window.
3. Click 'File' > 'Export File.'
4. Click the 'Save In' drop-down folder to choose a location to save the file, then click 'Save.'
Import the Excel 2003 File
5. Open Excel 2007 or Excel 2010.
6. Press 'Alt 'F11' together to open the VBE.
7. Click 'File' and then click 'Import.'
8. Locate the file on your computer and then click 'Open.'

Saturday, September 21, 2013

How to Find the Z

1. Open an Excel worksheet and enter your data in one column. For the purposes of this article, enter sample data into cells A1 through A10, typing a different number in each cell.
2. Click on an empty cell below your data and use the “AVERAGE” function to calculate the mean of the numbers you entered. For the example above, you would type “=AVERAGE(A1:A10)” into the formula bar and press 'Enter.' The “A1:A10” part of the formula indicates the range of cells containing the data you want to analyze. The result of the calculation will appear in the cell you selected.
3. Click on a different empty cell below your data and calculate your standard deviation by typing a formula using the function “STDEV” along with your cell range. Continuing the example above, the formula would be '=STDEV(A1:A10)'.
4. Click on an empty cell beside the cell containing the number you want to find the Z-score for. For instance, click on cell B3 if you want to find the Z-score for the number in cell A3. Click the “fx” button on the formula bar to open the “Function” window. Select 'Statistical' from the category dropdown menu, then choose 'STANDARDIZE' to bring up the Function Arguments window.
5. Enter the number you want to calculate the Z-score for in the “X” box. Enter the number itself or a cell reference where the number is located, such as “A3.”
6. Enter the mean you calculated in Step 2 in the “Mean” box. Again, you can type in the number itself, or the cell where you entered the formula in Step 2.
7. Enter your standard deviation in the “Standard_dev” box. Type the number, or the cell where you entered the formula in Step 3.
8. Press “OK” to display the Z-score for the cell you chose in Step 5.

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

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.

How to Monitor Stock Prices in Microsoft Excel

1. Open a blank Microsoft Excel spreadsheet.
2. Click on a cell where you want to show a stock price.
3. Click on 'Data' in the top menu bar.
4. Scroll down to 'Import External Data,' then over to 'New Web Query.'
5. In the window that pops up, type the URL http://finance.yahoo.com in the address.
6. Enter the stock symbol you wish to track. Be sure to double check that you entered the correct stock symbol by checking the company name that shows.
7. Scroll down to 'Last trade:' and click on the arrow to the left. The arrow will change to a check mark. The data highlighted will be shown on your spreadsheet.
8. Choose the data you wish to be in your spreadsheet, then click on the 'Import' button at the bottom of the window. You may choose to add any data with an arrow next to it into your spreadsheet by clicking on the arrow to the left of the data.
9. Verify the cell where you want the data to appear when prompted. You can click on any cell in the spreadsheet if you wish to change the location. Click on 'OK' after choosing the cell.
10. Save the spreadsheet. You can update the stock price(s) any time by clicking on 'Data' in the top menu bar. Then scroll down to 'Refresh Data' and click on it.
11. Know that you can also update the stock prices in the 'External Data' toolbar. Just click on the red exclamation point in that toolbar.

How to Copy Excel Formulas Through Multiple Rows

1. Click the cell that contains the formula you wish to copy with your mouse. Check the cell address bar at the top of your worksheet to ensure that the formula is correct.
2. Click 'Edit,' 'Copy' from the edit menu to select the formula. You may also right-click within the cell, and click 'Copy.'
3. Select the destination cells with your mouse by clicking the first cell and dragging through any contiguous rows to be included. Click 'Paste' from the edit menu to copy the formula and any formatting associated with the formula. You may also 'right-click' your mouse within the destination cell range and click 'Paste.'

How to Make a Pie Chart With Microsoft Excel

1. Open a blank worksheet in Microsoft Excel.
2. Enter all of the data into one column starting with cell 'A1.' Using monthly expenses as an example, type 'groceries' in cell 'A1.' Navigate to the cell below ('A2') and type 'gas.' Continue moving down column 'A' typing 'utilities,' 'daycare,' 'car payment' and 'mortgage' in each cell.
3. Insert the total dollar amount you spend on each item. Move the cursor to cell 'B1.' Type the total monthly cost of gas in the cell. Continue down the 'B' column, inputting the monthly costs for each of your other expenses.
4. Put your cursor in the cell directly under the last dollar amount -- cell 'B7' in our example. Holding down the left mouse button, select the cells in this column above 'B7' all the way up to 'B1.' All of the dollar amounts should now be highlighted.
5. With the column still highlighted, click on the 'AutoSum' button along the top of your Excel worksheet. This will total the dollar amount column, displaying your total monthly expenses.
6. Put your cursor in cell 'A1' and highlight down to 'A6,' and then from 'B1' to 'B6.'
7. While the 'A' and 'B' columns are simultaneously highlighted, select the 'Charts' tab at the top of your worksheet. Then choose 'Pie' from the list of chart types.
8. You can select several types of pie charts. For Microsoft Excel's most basic pie chart, select the first option. Your chart will be instantly created in your worksheet.
9. To add percentages or other labels automatically, double-click on the pie chart. A dialog appears.
10. Click 'labels,' then select the desired type of labels, such as percentages or dollar values. Microsoft Excel will place percentages and names on the pie chart.

How to Create a Line Chart in Excel 2007

1. Enter in headers for all of your data columns. Just type in the name for each data field in the cell directly above the data. If your data doesn't have an available row of cells above it, right-click on the row number that corresponds to the top row of data, and choose 'Insert.' These headers will be the labels for your data on the line chart.
2. Type in the row labels in the column directly to the left of your data. If you don't have any empty cells to enter in information, right-click on the letter at the top of the leftmost data column and select 'Insert.' These labels will appear at the bottom of the line chart to show the progression of your data. If you don't enter these labels, the chart will default to numerical labels, staring at one and counting upward.
3. Select the cell above the row labels and to the left of the column headers. Hold the shift key and select the bottom-right cell in your data field.
4. Click on the 'Insert' tab at the top of the Excel ribbon. Locate the 'Charts' area, and select 'Line.' Choose between the seven different types of line charts. You can choose between a standard line chart, stacked line chart and 100 percent stacked line chart. A stacked line will add all the data fields together, instead of comparing them like a regular line chart. A 100 percent stacked line will add all the items together, and then display each item's percentage of that total. Each of these options is also available with markers along the data lines. You can also choose to create a 3D lines chart. Once you select the line chart type, your line chart appears on the screen.

How to Copy Paste Macro for Excel

1. Open the workbook that has the macro you want to copy.
2. Press the 'Alt' and 'F11' keys together to access the VBE.
3. Click on the name of the macro you want to copy in the left-hand column. This opens up a window with the Visual Basic for Applications (VBA) code.
4. Click on the 'Edit' tab and then click on 'Select All.'
5. Click on 'Ctrl' and 'C' to copy the macro to the Office Clipboard.
6. Open the workbook where you want to copy the macro to.
7. Press the 'Alt' and 'F11' keys together to open the VBE.
8. Click on 'Insert' and then click on 'Module' to open a blank window.
9. Click inside the blank window, then press the 'Ctrl' and 'V' keys to paste the code into the open window. Your macro is now copied and ready for use.
10. Press the 'Alt' and 'F11' keys together to exit the VBE and return to your workbook.

Monday, September 16, 2013

How to Export File Search Results to Excel

1. Click on the 'Windows' button in the lower left-hand corner of your computer screen. Type in the file name or phrase you want to find. The results will appear in a separate window.
2. Press 'Control' and 'A' at the same time. This selects the results. Next, press 'Shift' and right-click your mouse at the same time. This will open a dialog box.
3. Select 'Copy as Path' from the dialog box. Open Microsoft Excel. Press 'Control' and 'V' at the same time to paste your results in the spreadsheet.
4. Try one of several dozen software options, such as Power File Search, Agent Ransack or SysExporter. Many of these have limited, trial versions that will let you test the software before buying it.

How to Remove the Date From Excel When Printing

1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click 'Microsoft Excel' to open the file.
2. Press the 'Ctrl' and 'O' keys to open the 'Open' dialog window. Double-click the Excel file you want to open. The file opens in the spreadsheet software.
3. Click the 'Page Layout' ribbon. In the right side of the ribbon in the 'Headings' section, remove the check mark next to the 'Print' option.
4. Press the 'Ctrl' and 'S' keys to save the settings to the document. Print the document to your printer to create a hard copy without headers.

How to Synchronize Scrolling in Excel

1. Open Microsoft Excel.
2. Open your two worksheets, or split your workbook into multi-view.
3. Click 'View' on the toolbar.
4. Click 'Synchronized Scrolling' on the right.

How to Convert CSV Files to Excel 2007

1. Open Excel 2007.
2. Click the circular Microsoft Office button in the upper left corner. Select Open.
3. Click the 'All files (*.*)' drop-down box and select Text Files.
4. Locate your CSV file and click 'Open.'
5. Click the Delimited radio button if the CSV file's data is separated by commas, semicolons or similar characters. Click the 'Fixed width' radio button if the CSV data is arranged in columns. Click 'Next.'
6. Check the check box indicating the delimiter that separates the values, such as commas, semicolons or tabs. Click 'Next.'
7. Click a column to select it. Click the General, Text or Date radio buttons to select a text type for the column. Repeat for the remaining columns and click 'Finish.'
8. Click the circular Microsoft Office button and select 'Save as.'
9. Click the 'Save as type' drop-down box and select 'Excel workbook (*.xlsx).' Type in a file name and click 'Save.'

How to Move Information Inside an Excel Spreadsheet

Move Cell Content Inside an Excel Spreadsheet
1. Start Microsoft Excel, and open a spreadsheet that contains data that you would like to move to a different area within the spreadsheet.
2. Select a cell or group of cells that you would like to cut and paste to a new location by clicking and dragging with your mouse over the cell(s).
3. Right-click on top of the selected cell(s) and choose 'Cut' from the shortcut menu to delete the content from the cell(s) and add them to the Excel clipboard.
4. Click in the cell(s) where you would like the cell content that you just copied to the clipboard to be moved.
5. Use your mouse to right-click and select 'Paste' from the shortcut menu to finish moving the information to it's new location within your spreadsheet.
Move a Worksheet Inside an Excel Spreadsheet
6. Locate the 'Sheet' tabs at the bottom of your Excel spreadsheet.
7. Click on the sheet tab that refers to the worksheet you would like to move inside the Excel spreadsheet.
8. Drag the sheet tab either to the right or left and release the mouse button when the sheet tab appears in the desired location.

Sunday, September 15, 2013

How to Disable Automatic Hyperlink in Excel 2007

1. Click the Office button after opening Excel.
2. Select 'Excel Options' from the Office menu. An 'Excel Options' window will open.
3. Click 'Proofing' from the list of options. You can access AutoCorrect here to disable automatic hyperlinks.
4. Click 'AutoCorrect Options' near the top of the window. The 'AutoCorrect' window will open.
5. Click the 'AutoFormat As You Type' tab and clear the checked box next to 'Replace as you type Internet and network paths with hyperlinks.'
6. Click 'OK' to disable automatic hyperlinks and click 'OK' again to close the window. When you type a Web address in Excel 2007, it will no longer change to a hyperlink.

How to Insert a Calendar in Excel 2007

1. Display the 'Developer' tab in Excel, if it is not already displayed. Click the 'Microsoft Office Button' and then click 'Excel Options.' In the category labeled 'Popular,' under 'Top options for working with Excel,' check the box labeled 'Show Developer Tab in the Ribbon' and click 'OK.'
2. Click 'Insert' in the 'Controls' group on the 'Developer' tab. Under 'ActiveX Controls' click the 'More Controls' button. This button will look like a hammer and a wrench. The 'More Controls' dialog box will appear.
3. Select 'Calendar Control 12.0' and click 'OK'. Right-click the area into which you wish to insert the calendar. The calendar will then be displayed on your workbook. The current month will be displayed.
4. Edit the calendar by right-clicking on it and selecting 'Calendar Object' and then 'Properties.' This will allow you to change the dates in the calendar. If you wish to edit the font, color or any other properties of the calendar, right-click the calendar and choose 'Properties.'

How to Add Autofill to an Excel Spreadsheet

1. Open Excel 2010 and click the 'File' tab. Select the 'Open' option. Browse the files and locate the workbook. Click the workbook and the 'Open' button. The workbook opens.
2. Click the worksheet where the autofill will be added. Insert a column adjacent to the worksheet data. For example, if your data resides in cells A1 to D20, insert a column before column A. Do this by clicking the A column header. Right-click the column header A and select 'Insert.' A blank column appears to the left of the data.
3. Insert the number '1' in the first cell, A1, adjacent to the data. Insert the number '2' in cell A2. Highlight cells A1 and A2. Notice the black square in the right corner of highlighted cells. Move your cursor over this square and notice it becomes a black ' .' Click and drag the plus down in the spreadsheet. Autofill fills in the remaining numbers of the sequence. When you have reached the desired end result, stop dragging.

How to Calculate the Number of Days between Two Dates in Microsoft Excel

1. Open the Excel workbook with dates you want to work with.
2. Enter the first or starting date in an empty cell. For example, if you want to enter the first day of the billing cycle, you can enter that in cell A1. Make sure you enter the month, day and year. If you do not enter a year, Excel will default to the current year.
3. Enter the second or ending date in another empty cell, for example, cell B1.
4. Highlight both cells and click the number format window in the 'Number' group on the ribbon. Select 'Short Date' or 'Long Date' from the menu, depending on how you want the dates displayed. The number format window typically displays the word 'General' initially, but it can display 'Custom' or any of the other formats. Excel generally recognizes dates when they are entered in date format; however, it is always useful to make sure your cells are formatted as dates.
5. Click an adjacent cell such as C1 to enter your formula. Type the following formula, substituting your own cell numbers for 'B1' and 'A1,' and press 'Enter':=B1-A1Excel will subtract one date from the other and display the number of days between the two dates.

Saturday, September 14, 2013

How to Use Conditional Formatting in Excel

1. Open a new or existing spreadsheet in Excel 2007. Enter any necessary data.
2. Select the cells that require conditional formatting. Select all cells if you don't need any cells to be exempt from the conditions.
3. On the Home tab of the Ribbon, click 'Conditional Formatting.' Choose 'Highlight Cells Rules,' then select the condition that matches your needs: For text, choose 'Text that contains...,' or for numbers, one of the other options. Let's assume you want to highlight cells containing numbers greater than 10: Choose 'Greater than...'
4. In the resulting dialog box, type '10' into the empty field, then to the right, click the drop-down arrow and choose either an existing format or 'Custom Format' to choose your own cell background and border, font color and more.

How to Jump to a Cell in Excel

1. Press the 'F5' key on your keyboard. The 'Go To' dialog box appears on your screen. Alternatively, press the 'Ctrl' and 'G' keys simultaneously to open the 'Go To' dialog box.
2. Enter the row of the cell you want to jump to in the 'Reference' box. If you want to jump to cell C15, for example, enter 'C.'
3. Enter the column of the cell you want to jump to in the 'Reference' box. If you want to jump to cell C15, you would enter '15.' Your reference should look something like 'C15.' Press the 'Enter' key on your keyboard to jump to the cell.

How to Export Pivot Table Data to Excel

1. Load the Excel pivot table spreadsheet.
2. View the table and decide which components of the pivot data you wish to export to a new Excel spreadsheet. The pivot table divides the spreadsheet data into multiple categories, based on the table's formatting. However, the table does not show the original data, only the summation information. You may wish to export all the original spreadsheet data rows that correspond to a particular number that appears on the pivot table.
3. Double-click on the chosen numerical result in the pivot table. A new spreadsheet is immediately created and all the corresponding data from the original data source is exported from the pivot table field into the new spreadsheet.
4. Click on the pivot table spreadsheet tab to return to the pivot table.
5. Double-click on any other table row's numerical result to create an additional, separate data export into another new Excel spreadsheet. Repeat this process as needed until all the separate pivot data you require is exported. Excel conveniently places each export into a new separate spreadsheet to keep the data sets independent of each other for further analysis.

Friday, September 13, 2013

How to Attach a PDF Document to an Excel Spreadsheet

1. Open or create an Excel spreadsheet that has the information you want to attach the PDF document to. Make any changes and save the spreadsheet.
2. Click the area of your Excel spreadsheet where you want the PDF document attached. Click the 'Insert' drop-down menu in Excel 2003 or earlier versions. In later versions of Excel, click 'Insert' tab.
3. Click 'Object.' Make sure you are on the 'Create New' tab.
4. Select 'Adobe Acrobat Document' from the Object Type list. If you want to attach the entire PDF document as a viewable object on your spreadsheet, click 'OK.' If you want to insert an icon that will be a clickable link to the attached PDF document, select the 'Display as Icon' check box and click 'OK.'
5. Navigate to and select the PDF document you want to attach to your Excel spreadsheet. Click 'Open.' The PDF document will open in a separate window. Close it and you will be returned to to the spreadsheet with the PDF document -- or a link to it, depending on which you chose -- attached.

How to Remove an Excel 2003 Add

1. Open Excel on your computer. Go to the 'Tools' menu.
2. Select 'Add-Ins' from the 'Tools' menu. Check the box next to the program you want to remove in the 'Add-Ins Available' text box. Click 'OK.'
3. Restart Excel to finalize the removal.

How to Use Excel's SUBSTITUTE Function

1. Choose the cell that you want to enter the formula into. Click it with your mouse. Click inside the function (fx) box, just below the Excel menu.
2. Type in the function in this format: '=SUBSTITUTE(text, 'old_text', 'new_text', instance_num).' 'Text' is the cell that contains the text you want to subsitute. 'Old_text' is the text to be replaced and 'new_text' is the text to replace it with. 'Instance_num' is the instance of the text you want to replace.
3. Create an example to learn how the substitute function works. Click on the top gray cell so that the whole spreadsheet is highlighted. Click 'format,' 'cells' from the menu. Select 'text.'
4. Enter 'November 2, 2008' in A1.
5. Click on cell B1. Go to the function box, and type '=SUBSTITUTE (A1, '2', '4', 1). Hit enter. The text in B1 will now read 'November 4, 2008.' Then edit the formula and take out the '1.' All instances of '2' will be changed to '4.'
6. Go to cell B2. In the function box, type '=SUBSTITUTE (A1, '8', '12'). Hit enter. The year now read '2012.' In this case you don't need the 'instance_num,' since there is only one '8' in the cell.
7. Get help and other examples of the substitute function by clicking 'help' on Excel's menu. In the 'keywords' box, type 'substitute worksheet function.'

How to Wire a Macro to Pull From One Spreadsheet to Another

1. Open Excel 2007 and find a workbook. Click the 'Office' button and select the 'Open' icon. Browse your files for the workbook. Click the workbook and click 'Open.' The workbook opens.
2. Click the Developer tab and select 'Record Macro.' The Record Macro dialog box appears. Enter a name for your macro in the Macro Name field. Add a brief description in the Description field. Click 'OK.' The macro starts recording.
3. Link two spreadsheets together by clicking on one cell in your first worksheet. Type '=.' Click on the second worksheet and select a cell that you want to link. Click that cell. Press the 'Enter' key. These two spreadsheets are now linked and the first worksheet will be populated with data pulled from the second worksheet.
4. Click 'Stop Recording' on the Developer tab. You have completed the recording macro process.

How to Add a Legend to an Excel Chart

1. Start Microsoft Excel 2007 and open a workbook you have already created from your files to which you want to add a legend.
2. Point and click on the chart so it is selected. You can tell the chart is selected because it will be surrounded by a light blue border.
3. Select the 'Layout' tab at the top of the Excel screen to display the 'Layout' ribbon. Locate the 'Labels' group in the 'Layout' ribbon.
4. Click the 'Legend' button in the 'Labels' group of the 'Layout' ribbon. The options for adding and displaying a legend to the chart will be displayed.
5. Choose 'Show Legend at Right,' 'Show Legend at Top,' 'Show Legend at Left' or 'Show Legend at Bottom' to place the legend in the specified location while shrinking the chart to make room for the legend. You can choose 'Overlay Legend at Right' or 'Overlay Legend at Left' to place the chart to the right or the left of the chart overlaying the chart so the chart does not shrink.
6. Watch as the legend is immediately placed inside the chart. You can move the chart manually at this time using the mouse to click and drag it to a new location.

Subscribe to: Posts (Atom)