How to Calculate a Year

1. Click on a blank cell where you want to enter your formula calculation and display the year-to-date average.
2. Begin your formula calculation by clicking the 'Average' function. You can find this formula in one of several places in different versions of Excel. In Excel 2010, it is easily accessible on the 'Auto Sum' menu, available on both the 'Home' tab and the 'Formulas' tab. Or you can always manually type in '=AVERAGE().'
3. Select the cells you want to use in the average. If they are all in the same row or column, click and drag your cursor across all of the cells you want. If they are not in the same row or column, click on them one by one while holding the 'CTRL' key on your keyboard. Also select the cells that you want to include as the year progresses and that do not have data in them yet. As soon as you enter the data, the program automatically includes it in the average. Be sure that these cells are blank, and do not display zeros or dashes; otherwise, the program calculates zeros into the average. Excel indicates which cells you have highlighted with colored lines as well as by displaying the cell numbers (such as 'A1, A2') in the cell where you are creating the formula.
4. Press the 'Enter' key on your keyboard to complete the formula. Excel calculates and displays the average in the cell where you put the formula calculation. Each time you enter data into the blank cells that you included in the formula, the running average changes accordingly.

Read more ►

How to Tab Over in Excel 2003

1. Launch Microsoft Excel 2003.
2. Click 'Tools' at the top of the window.
3. Click 'Options.'
4. Click the 'Transitions' tab at the top of the window.
5. Click the box to the left of 'Transition Navigation Keys' to remove the check mark, and then click 'OK.'

Read more ►

How to Change the Display of Axes in Excel

1. Start Microsoft Excel 2007 and open an existing workbook that contains a chart that you would like to change the display of the axes.
2. Click the chart that you want to change the display of the axes so it is selected. The chart will have a light blue border surrounding it, indicating that it is selected.
3. Choose the 'Axes' button in the 'Axes' section of the 'Layout' ribbon. A drop-down menu will appear.
4. Point to 'Primary Horizontal Axis' to view the options for changing the display of the primary horizontal axes. The display options for your primary horizontal axis will be displayed in a menu. Click on the title and description that you want to apply to your primary horizontal axis.
5. Select the 'Axes' button in the 'Layout' ribbon once again and point to 'Primary Vertical Axis.' Click on a title and description for the display option you want to apply your primary vertical access.

Read more ►

How to Import a Web Page to Excel 2007

Import Data from Web Page
1. Open both your Internet browser and Microsoft Excel 2007 program. Go to the web page that you want to import into Excel.
2. Go the Data tab on the user interface in Excel. In the 'Get External Data' command set, click 'From Web.' A 'New Web Query' box will appear on the screen.
3. Type in the web page that you want to import into Excel, if it does not automatically appear in the 'New Web Query' box. Select the 'Import' button. Excel will then give you the option to select the cell in which you want the web page information to be placed.
4. Select either Existing Worksheet or New Worksheet. If using an Existing Worksheet, choose the cell column and row. Press 'Okay' when complete. The information from the web page will then appear in the worksheet.
Copying and Pasting web page into Excel 2007
5. Copy the web page information by highlighting the information you want on the web page and pressing 'Control' and 'C' (Ctrl C) at the same time.
6. Go to Excel, select the cell in which you want the information to appear. Press 'Control' and 'V' (Ctrl V) at the same time to paste the information. You can also select 'Paste Special' in Excel by single right-clicking on your mouse. 'Paste Special' will give you the option to add the hyperlink or web page text to the Excel cell.
7. Click 'Okay.' The information will appear in the selected cell.
Export web page into Excel
8. Go to the web page that you want to import into Excel.
9. Right-click with your mouse and choose 'Export to Excel.' This will take you directly to Excel.
10. Select 'Import' on the 'New Web Query' box. The 'Import Data' box will appear.
11. Select the 'New or Existing Worksheet' and cell in which you want the data to appear. Click 'Okay.' The web page data will appear in Excel.

Read more ►

How to Create a Visio Network Diagram From Excel Data

1. Start Visio and click on the 'File' tab. Click 'New' in the left pane and select 'Network.' Choose 'Detailed Network Diagram' and click 'Create.'
2. Drag a ring network shape or Ethernet shape from the 'Network and Peripherals' group onto the page. Drag the appropriate devices from the 'Computers and Monitors' or 'Network and Peripherals' section onto the page.
3. Connect the device shapes to the network shape. Click on the network or Ethernet shape and hover the mouse pointer over the yellow, diamond-shaped control handle. When the pointer changes to a four-way arrow, drag toward the first device to draw a connector. Repeat with the remaining devices.
4. Import Excel data into the diagram. Go to the 'Data' tab. Click on 'Link Data to Shapes' in the 'External Data' group. The Data Selector Wizard opens.
5. Select 'Microsoft Excel Workbook' as the type of data source you want to use and click 'Next.' Browse to the workbook you want to use and click 'Insert.' Complete the wizard.
6. Drag a row of the imported Excel data to a shape in the diagram to add it to the shape data. Repeat for the remaining shapes and information.

Read more ►

How to Open XLSM, XLSX, XLSB, XLTX, XLTM and XLAM Files in Excel 2003 and 2002

1. Go to the Microsoft Download Center (link in Resources) and select 'Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats,' located under the 'Popular downloads' heading. Click the 'Download' button on the right side of the ensuing page.
2. Click 'Run' if you are using Internet Explorer; click 'Open' if you are using Google Chrome; click 'Save File,' then double-click the downloaded file if you are using Mozilla Firefox.
3. Click 'Yes' if you are prompted by the User Account Control window. Read the license terms and select the 'Click Here to Accept the Microsoft Software License Terms' check box if you agree to the terms and wish to continue with the installation. Click the 'Continue' button.
4. Close Excel and any other Microsoft Office programs that are currently running after installation is complete, then open Excel to enable the Compatibility Pack. You can now open, edit and save all Excel 2007 or 2010 file formats in your edition of Excel.

Read more ►

How to Turn Excel Cells Into Mailing Labels

1. Open Excel 2010 and click the 'File' tab. Click 'Open.' Browse the files and locate a workbook. Click the workbook and the 'Open' button. The workbook will open.
2. Review the workbook and make sure the data that will be used in the mailing labels contains column headers. Click the 'Save' icon on the Quick Access Toolbar.
3. Open Word 2010 and click the 'Mailings' tab. Click the 'Start Mail Merge' button. A drop down list appears. Select 'Labels.' The Labels Option dialog box appears. Select the 'Label Vendor' drop down list and select the type of labels you are creating. Select the label product number in the 'Product Number' scroll list. Click 'Ok.'
4. Click the 'Select Recipients' button on the ribbon. Select 'Use Existing List.' The Select Data Source dialog box appears. Browse the files and locate the Excel workbook containing the data for the mail merge. Click the workbook then click the 'Open' button.
5. Add fields to the label by clicking the 'Insert Merge Field' button. Select one of the options in the drop down list. It will appear on the first label. If necessary, add formatting, spacing, or press the enter key. Add another field by clicking the 'Insert Merge Field' button. If necessary, add formatting, spacing, or press 'Enter.'
6. Press the 'Preview Results' button to see a preview of the labels. Click 'Finish And Merge' to finalize the mail merge. Select 'Edit Print Documents.' Click 'All' in the Merge To New Document dialog box. Select 'Ok' to see the labels prior to printing.

Read more ►

How to Move a Chart in Excel to Another Page

1. Launch Microsoft Excel 2010.
2. Click the 'File' tab at the top-left corner of the window, and then click 'Open.'
3. Browse to the file containing the chart that you want to move, and then double-click the file to open it.
4. Click the chart to select it. This will add three tabs to the top of the window in a new 'Chart Tools' section.
5. Click the 'Design' tab in the 'Chart Tools' section.
6. Click the 'Move Chart' button in the 'Location' section of the ribbon at the top of the window.
7. Check the 'New Sheet' option, and then click 'OK.' The chart will be moved to a new tab called 'Chart1,' and will be accessible by clicking the tab at the bottom of the window.

Read more ►

How to Create an XML Map

1. Click the 'developer' tab in Microsoft Excel or if it is not available click the Microsoft Office button. Next click on Excel options.
2. Locate the 'popular' category in Excel. Now you will click the 'show developer tab.'
3. Click 'source' on the developer tab in the group labeled XML.
4. Locate XML maps and click on it. Now click 'add.'
5. Find the 'lookin' list and click on the drive, list and folders that you want to open. Now click 'open' for whatever you want.
6. Click 'OK' and the XML map is created.

Read more ►

How to Make a Histogram in Excel 2010

1. Download the Excel 2010 Analysis ToolPak. Click 'File,' and then 'Options.' From the Add-Ins category, select 'Excel Add-Ins,' which display in the Manage box. Click 'Go.' Check the 'Analysis ToolPak' box from the list of add-ins and click 'OK.'
2. Choose an appropriate bin size for your data set. The bin size refers to the range of values that fall into a specific class. For example, if your data set is ages of people in the United States, set your bin size to 10 years. This makes the bins correspond to ages 0-10, 11-20, 21-30 and so on, with a maximum bin of 101-110. Select a bin width that creates five to 20 groups of data.
3. Type the bin widths in column A of a blank worksheet, beginning with the lowest number. For the age range example, type 0, 10, 20, 30 and so on.
4. Type the data points in column B of the worksheet. Keep the data points as raw numbers, rather than converting them to frequencies. List the entire set of numbers in any order.
5. Look in the Analysis section of the Data tab. Click 'Data Analysis' and highlight the 'Histogram' tool from the Analysis Tools box. Click 'OK.'
6. Enter the Input Range in the box provided, and highlight the contents of column B. In the Bin Range field, highlight the contents of column A. This tells the software what data to use to make the histogram.
7. Select 'Chart Output' in the output options section to generate a histogram graph. Click 'OK.'
8. Modify your histogram to include custom labels. Double-click on the x- and y-axis labels to change the text. Use the 'Chart Tools' section to modify the design, layout and format of your histogram.

Read more ►

How to Convert an Excel 2007 File to Excel 2003

1. Open the Microsoft Excel 2007 file you want to convert; you can do this by clicking the Microsoft Office button in the upper left-hand corner (it looks like a circle with the Microsoft Office logo on the inside) and then 'Open.' From the pop-up window, select the location of your Excel 2007 file. You can recognize 2007 files because they have a file extension of XLSX.
2. Click on the Microsoft Office button again; this time, select the right-facing arrow next to the 'Save As' option. A list of possible formats will appear; from this list, choose 'Excel 97-2003.'
3. Verify that the name of the file is correct; if you wish to change it, simply highlight the title, press 'Delete' on your keyboard and then type in the new name. Also make sure that file extension now reads XLS. Click 'Save.' The file should now be stored on your hard drive as a Microsoft Excel 2003 file.

Read more ►

How Do I Use Dragon SpeakingNaturally 10 With Microsoft Excel?

1. Open Microsoft Excel.
2. Click the 'Office' button in Microsoft Excel. It is in the left, top corner of the program.
3. Click 'Excel Options' from the drop-down menu.
4. Click 'Add-Ins' from the menu along the left navigation pane in 'Excel Options' dialog box.
5. Click the drop-down menu next to 'Manage' and select 'Disabled Items.'
6. Locate and click on 'dgnexcel.dll' from the list of disabled items.
7. Click 'Close' and 'OK.'

Read more ►

How to Search a Cell Range for a Match in Excel VBA

1. Open the Visual Basic Editor (VBE). Click the 'Developer' tab in Excel and then click 'Visual Basic.'
2. Click 'Insert' and then click 'Module.'
3. Cut and paste the following code into the blank window:Sub Find_First()Dim FindString As StringDim Rng As RangeFindString = InputBox('Enter a Search value')If Trim(FindString)
'' ThenWith Sheets('Sheet1').Range('A1:A20')Set Rng = .Find(What:=FindString, _After:=.Cells(.Cells.Count), _LookIn:=xlValues, _LookAt:=xlWhole, _SearchOrder:=xlByRows, _SearchDirection:=xlNext, _MatchCase:=False)If Not Rng Is Nothing ThenApplication.Goto Rng, TrueElseMsgBox 'Nothing found'End IfEnd WithEnd IfEnd Sub
4. Change the range to suit your needs. The range in the code is specified as A1 through A20 (Range('A1:A20')). For example, if you want the range to be B1 through B100, change the range to 'Range('B1:B100').'
5. Press 'F5' to run the macro. A pop-up window will appear on the worksheet. Type the number or phrase you want to search for into the box and then press 'Enter.'

Read more ►

How to Move Rows in Excel

1. Select the row heading (A, B, C and so forth) that you wish to move and highlight it. You can also select several rows by holding down your left mouse button and sliding it across the rows which you would like to select, if you want to move more than one row at a time.
2. Keep your mouse placed on the highlighted row(s) and right click your mouse button. This will bring up a pop up menu with several options to choose from.
3. Choose 'Cut' from the menu that pops up. This will make the row you want to move disappear, but your computer will store it in its temporary memory. As long as you don't cut anything else or close the program, your information will not be lost.
4. Pick the row that is directly below where you want your selected row to be placed, so that it is highlighted. This is how you let your computer know where you want to place the row that you are moving.
5. Locate the Insert menu and click on the 'Cut Cells' option. The row(s) that you chose to cut will be pasted into your selected area.

Read more ►

How to Put a Combo Box in an XLS Cell

1. Open the Excel document to which you want to add a combo box.
2. Click on 'File' in the top-left corner of the Excel window. Choose 'Options' from the list that appears.
3. Select 'Customize Ribbon' on the left side of the new window. Place a check mark next to 'Developer' on the list located to the far right side of the window. Click 'OK.'
4. Click the 'Developer' tab at the top of the screen. Select 'Insert' and choose 'Combo Box (Form Control),' which will be the second option from the left in the top row. Select a cell where you want to enter the combo box.
5. Right-click the combo box and choose 'Format Control' from the drop-down menu. Click the 'Control' tab at the top of the new window. Type in a range of cells into the 'Input Range' field. These cells will contain the data that the user can choose from. Type in a single cell into the 'Cell Link' field. This is the cell where the user's choice will appear. Click 'OK' when you are finished.
6. Click 'Design Mode' at the top of the screen to exit the mode. This will make the combo box unable to be moved or edited. If you need to change the location or data for the box, click 'Design Mode' again, and you will be able to make the changes.

Read more ►