June 2013 - Excel

Browse » Home » Archives for June 2013

Friday, June 28, 2013

How to Use a Fill Handle for Numbers in Excel 2003

1. Open Excel 2003 and click in cell A1. Type '1' in cell A1 and '2' in cell A2. Highlight these two cells.
2. Notice the fill handle in the lower right hand corner of these highlighted cells. Point to the fill handle. Your mouse will become a thin black ' .' This is your indicator that the fill handle can be dragged to fill in the sequence.
3. Drag the fill handle down a few cells. Notice the rest of the sequence is automatically populated in the cells.

Thursday, June 27, 2013

How to Delete a Named Range in Excel 2007

1. Open your Excel file.
2. Click 'Formulas.'
3. Select 'Name Manager.'
4. Highlight the named range.
5. Press 'Delete.'

How to Make a Decision Tree in Excel

1. Draw a square in a blank Excel worksheet. In Excel 2007, click 'Insert,' then 'Shapes' and then click on a square. In Excel 2003, click on the 'Rectangle' autoshape button on the drawing toolbar and drag the square to your worksheet.
2. Right-click on the square and click 'Add text.' Type the major decision into the box. For example, you might be deciding whether to save or spend a recent windfall, so write 'Save or Spend' in the box.
3. Select a 'Line' shape from the toolbar. Click on the right edge of the square and drag the line to a length of an inch or two (you can change the lengths of the lines at a later time by clicking and dragging on them). In the above example, you would draw two lines of equal length (one for 'Spend' and one for 'Save').
4. Draw two more squares at the end of the lines, repeating the process for inserting a square from Steps 1 and 2 to draw the square and insert text. In the given example, write 'Spend' in one text box and 'Save' in the other. This creates your first set of branches.
5. Repeat the above process to as many branches as you need to your tree.

How to Upgrade From Excel 2003 to 2010

Office 2010 Installation
1. Place the Office 2010 installation disc into your computer or download Office 2010 from the Microsoft website.
2. Follow the prompts to initiate the installation of Office 2010. If you have downloaded a version of the software, you may be prompted to unzip the files. For CDs, you will have to enter a product key, which can be found on your CD case.
3. Click 'Customize' in the 'Choose the installation you want' dialog box.
4. Right click and then click 'Not available' for all programs except for Excel 2010.
5. Click 'Install now' to install Excel 2010.
Excel 2010 Download
6. Visit the Microsoft Excel 2010 website.
7. Purchase a copy of Excel 2010. Click 'Download' to download a copy of Excel to your computer or click 'Ship it' to receive a disc.
8. Follow the instructions for installation. For CDs, place the CD into your CD drive and follow the on screen prompts. For downloads, follow the on-screen prompts after downloading the file from the Microsoft website.

How to Transfer Excel VB Data From Range to Array

1. Click the “View” tab on the 'Ribbon' menu. Click the “Macros” icon to display the “Macro” dialog.
2. Select an existing macro name in the list box if you want to add the ability to copy data from a range of cells to an array. Click “Edit” to open the Visual Basic editor. If you want to write a new macro, type the name into the “Macro name” text box and click “Create.'
3. Declare an array variable as a 'Variant' data type. The following example declares the array 'RangeArray':Dim RangeArray As Variant
4. Assign the data cell range to the array. Continuing the example, assign the cells A1 through A10:RangeArray = Range('A1', 'A10')
5. Access the data in the array. To confirm that the example has worked, display the third value in the range with a message dialog:MsgBox RangeArray(3, 1)

How to Format Drop

1. Open Excel 2010 and select a workbook that will contain the drop-down list. Click the 'File' tab and select 'Open.' Browse your files and select the workbook. Click the 'Open' button. The workbook opens.
2. Highlight the range of cells where the drop-down list will be displayed. Select the 'Data' tab, then select 'Data Validation.' The Data Validation dialog box appears.
3. Click the 'Settings' tab. Change the 'Allow' drop-down list to 'List.' In the 'Source' drop-down box, type 'Yes,No,Maybe.'
4. Click the 'Input Message' tab. The input message will appear as the user uses the drop-down list. Use it to add helpful information. In the 'Title' field, type the title of your Input message. Add additional details in the 'Input Message' field.
5. Click the 'Error Alert' tab. The error alert tab informs the user of data input mistakes when the user manually types the wrong information, instead of using the options from the drop-down list. Select the 'Stop' icon in the 'Style section. In the 'Title' field, type the title of your Error message. Add additional details in the 'Error Message' field. Click 'OK.'
6. Click in one of the fields of the highlighted cells. Notice the drop-down list and input messages that appear.

Wednesday, June 26, 2013

How to Hide Worksheets in Excel 2007

1. Select the worksheets you want to hide. Click the worksheet tab, such as Sheet1 or Sheet2 to select one sheet. To select at two or more adjacent sheets, click the first tab then hold the 'Shift' key and click the last tab. To select more than one nonadjacent sheets, click the first tab then hold the 'Control' key and click the other tabs you want to hide. To select all sheets, right-click any sheet tab then choose 'Select All Sheets' on the shortcut menu.
2. Click 'Format' in the Cells group located on the Home tab above the worksheets.
3. Select 'Hide and Unhide' under Visibility and click 'Hide Sheet.'

How to Make an Invoice in Excel 2007

1. Click the round 'Office' button in the top left of the Excel worksheet. A drop down will appear with a list of icons on the left and a list of 'Recent Documents' on the right.
2. Click 'New,' the first icon at the top of the list on the left of the drop down. A new screen will pop up automatically defaulting to 'Blank and recent' in the box on the left of the screen and 'Blank Workbook' at the top of the middle box.
3. Click 'Invoices' in the box on the left of the screen under the header 'Microsoft Office Online.' The middle screen will change to show thumbnails of various invoice designs.
4. Click through the various invoices to view the different options available. If this is your first time using a template, information to the right of the middle box will appear asking you to accept using these forms. Click 'I Accept' if you accept the terms of the templates. Once you've clicked that, you will never need to click it again and instead will see larger thumbnail views of the template you currently have selected.
5. Click 'Download' once you have found a template that fits your business needs. The template will download and open. Modify appropriate areas to show your company name and information.
6. Click the 'Office' button in the top left. In the drop down, click 'Save' to name and save your template where you will remember it.

How to Calculate the Percentage Change From the Previous Year in Excel

1. Enter previous and current year information in columns side by side. Give the column headings to indicate the first is the current year information and the second is the prior year information.
2. Name the next column 'Percent Change,' and click on the first row in that column that has data in the fields for current and previous years.
3. Enter the following formula into the cell; '=(cell with current year info)-(cell with prior year info)/(cell with prior year info).' For example, if the first row to have data in it is Row 6, the current year information is in Column C, the prior year information is in Column D, and the column being used to show the percentage of change is Column E, the formula '=(c6-d6)/c6' would be entered into Cell E6.
4. Format the result to a percentage. Right-click on the cell and choose 'Format cells.' Choose 'Percentage' from the list along the left of the next window. Choose the number of decimals to show.
5. Copy the formula for the cell down to the rows below. To quickly do so, place the cursor in the lower right corner of the cell until a ' ' appears. Press the left mouse button and hold it down as you drag the mouse down to the final row for which the formula is needed. Notice that the formula adjusts itself to each row; therefore, the cell below e6 shows the formula as '(c7-d7)/d7.'

How to Create Labels in Microsoft Office 2007 From Excel 2007

1. Open a new Excel spreadsheet. Type 'Last Name' into Cell A1. Move right across the first row of cells labeling each new cell with the information you need in order, such as 'First Name,' 'Title,' 'Address,' 'City' and 'Zip Code.'
2. Type in the relevant information for each mailing label you need on the column below the row heading. For example, type in the last name for each contact underneath the 'Last Name' column.
3. Highlight the entire section of columns and rows with the mailing label information, including the top row headers. Navigate to the 'Formulas' tab and click 'Define Name.' Type in a name for the address list, such as 'Invitations' or 'Mailing List.'
4. Save the spreadsheet and close the Excel 2007 program. Open a new Word 2007 document. Navigate to the 'Mailings' tab and click 'Start Mail Merge.' Select 'Labels.'
5. Use the drop-down menus to select the specific type of labels you are using. Check the code on the back of the label's packaging if you aren't sure what type you have.
6. Click 'OK' and then click the Microsoft Office button at the upper-left end of the window. Select 'Word Options.' Choose 'Advanced' and then click the check box labeled 'Confirm file format conversion on open.'
7. Click 'OK.' Navigate back to the 'Mailings' tab and click 'Select Recipients.' Choose 'Use Existing List.' Click the name of the Excel spreadsheet you saved earlier and choose 'OK.'
8. Click 'Match Fields.' Use the drop-down menus to match the headers you created in the Excel spreadsheet with the various fields that will appear on the mailing label, such as 'First Name' and 'Last Name.'
9. Select 'Preview Results' to ensure the mailing labels look correct. Click the 'Finish Merge' link and then select 'Print Documents' to print the labels.

Tuesday, June 25, 2013

How Can I Overlay Graphs?

1. Open the Excel worksheet that contains the data series.
2. Click and drag the cursor on the range of cells that will convert to a chart.
3. Select the “Insert” tab on the command ribbon.
4. Click the arrow in the lower-right corner of the “Charts” group to launch the dialog box. The “Insert Chart” dialog box opens with a gallery of sample charts.
5. Select a 2-D chart. For example, choose the bar, column or line.
6. Click “OK.” The worksheet data converts to an embedded chart. The “Chart Tools” ribbon appears.
7. Click one data series in the chart’s plot area.
8. Click the “Design” tab on the “Chart Tools” ribbon.
9. Click the “Change Chart Type” button in the “Type” group. A gallery of sample chart types appears.
10. Click the preferred 2-D chart type. The data series converts. Two different chart types display in the same chart area.
11. Edit the chart with the commands in the “Design,” “Format” or “Layout” tabs. The “Design” tab contains “Chart Styles” and “Chart Layouts.” The “Format” tab contains “Shape Styles” and “Shape Effects.” The “Layout” tab contains “Labels” and “Axes.”

How to Fix a Faded MS Office Menu

1. Click 'Save' on your document. Sometimes this is all that is needed to move your cursor from a place (such as editing a cell in Excel) that blocks certain menu commands. If 'Save' is one of the faded items, click anywhere in the document to move your cursor from its current position.
2. Examine the title bar at the top of your Office program window. This will tell you the title of the document as well as the program and other information. If the title bar contains the words 'Read Only,' you can save the file under a different name to allow access to the toolbar items. In this case, click 'File,' 'Save as,' change the file name, and then click 'Save.' Close the document, and then re-open your renamed document. Your menus should be available as normal.
3. Click 'File,' then 'Info' if your grayed out menus are in Word. Select 'Protect document' under 'Permissions.' Click 'Restrict editing,' and then click 'Stop protection' and deselect any boxes that restrict specific types of editing. Close the menu by clicking the 'X' in the upper-right corner. This also works for read-only files. You may need to type in a password if one was set to protect the document.
4. Click 'Format' on the 'Home' tab if your grayed menus are in Excel. If 'Unprotect sheet' is an option, click this. Enter the password to unprotect the sheet, if required.
5. Check your menu tab to ensure you are in the correct menu. One common misconception in Word is that the alignment features are grayed out, making it impossible to center or make other changes to your text alignment. This is only true if you are trying to use the 'Align' function under 'Arrange' in the 'Page Layout' tab. Click the 'Home' tab instead and position your cursor on the line you want to center. Click the center paragraph icon in the 'Paragraph' section, or click the popout arrow next to 'Paragraph' and select 'Center' from the drop-down menu next to 'Alignment.'

How to Add Borders to Multiple Cells in Microsoft Excel 2003

1. Select the cells to which you wish to add a border. Left-click on one cell, then hold and drag the cursor across the entire group of cells to highlight them. When you release the mouse button, all the cells will stay highlighted.
2. Right-click on the group of highlighted cells to access the Cell Properties menu. Select “Format Cells” to access the border menu. Then scroll to the “Border” tab to change the border.
3. Under the “Border” tab, you can select a premade border under “Presets.” Or you can devise a custom shape and size for the border by clicking on the different border shapes that will appear inside of the “Preview” box when you select these elements.
4. Under the “Line Style” drop-down menu, you can scroll to and select the line style of your choice.
5. Activate the “Color” drop-down menu and choose the desired border color from a proffered palette.
6. Once you have made the desired border selections, click on the 'OK' button to implement the changes.

Monday, June 24, 2013

How to View or Hide the Toolbar in Excel 2007

View or Hide
1. Open Excel 2007. You will notice three distinct parts of the Excel 2007 window: The spreadsheet, the formula bar and the toolbar. The toolbar includes seven different tabs: Home, insert, page layout, formulas, data, review and view. The traditional functions such as file, save, save as and print are under the Office button in the upper left corner.
2. View the toolbar located near the top of the screen. This will be your default option. On Excel 2007, the toolbar is referred to as the ribbon. Rather than just a list of headings with drop down menus, Excel 2007 toolbar has a visual ribbon which is separated by tabs but clearly visualizes each of the options to better help you recognize the features you want to select. An added bonus to this layout is that you can see icons for each of the features without having to select a drop down menu, as they are located in your sight at all times.
3. Right click on the toolbar if you would like to hide it from your screen. If you are conducting data entry and do not plan on using the multiple features the toolbar provides, you may wish to maximize your spreadsheet working area and hide the toolbar. Each tab is broken into categories that are each labeled at the base of the toolbar. Right-click any base labels. Select the option to minimize the ribbon. This will hide the toolbar.
4. View the tab headings. With the toolbar minimized, these headings appear to be drop down menus, but they still function as tabs. Click on the tab headings and the toolbar will temporarily appear again. If you move your cursor away from the temporary toolbar it will return to hiding. This feature is especially useful if you need to maximize your available workspace and know where to find icons on the toolbar.
5. Right-click on the tab headings and remove the check mark next to hide ribbon, by selecting it. This will return to the toolbar to its default setting so it stays active and visible while you are working. New users might prefer to see the toolbar at all times, as they may be unfamiliar with many features or shortcut keys experienced users may know.

How to Disable the Security Warning in Excel 2007 Macro

1. Open Excel 2007 and click the 'Office' button. Click 'Open.' Browse the files and locate the workbook. Click the workbook and the 'Open' button.
2. Notice the security warning across the top of the Excel workbook. Click the right 'Options' button on the security button.
3. Select 'Enable Content.' Click 'Ok.' The security warning is disabled and the macros are functional in the workbook.

How to Create Excel Spreadsheet of Folder Subfolder Directories

In Excel 2003 and Older
1. Log in to your computer as an administrative user.
2. Open a command window by holding down the 'Shift' key on your keyboard while you right-click anywhere on your desktop. A menu will appear. Click 'Open a Command Window Here.' A command window will open on your desktop. You will see a few words followed by a blinking cursor.
3. Type “CD\' after the blinking cursor (do not type the quotation marks). Press the “Enter” key on your keyboard. A new command line will appear below. It should read 'C:\>' followed by the blinking cursor.
4. Type 'dir /ad /s >c:/directories.txt” (do not type the quotation marks). Press the “Enter” key on your keyboard.
5. Type “Exit” and hit the “Enter” key.
6. Open Excel. Click “File” and “Open.” Choose “Text Files (*.prn; *.txt; *.csv)” from the “Files of type” drop-down box. Select the file “c:\directories.txt.” Click the “Open” button.
7. Click the “Delimited” button. Click the “Next” button.
8. Check “Tab” in the “Delimiters” area of the “Text Import Wizard” window. Click the “Next” button.
9. Click the “Finish” button. Save the file.
In Excel 2007
10. Log in to the computer as an administrative user. Open a command window by holding down the 'Shift' key on your keyboard while you right-click anywhere on your desktop. A menu will appear. Click 'Open a Command Window Here.' A command window will open on your desktop. You will see a few words followed by a blinking cursor.
11. Type “CD\' after the blinking cursor (do not type the quotation marks). Press the “Enter” key on your keyboard. A new command line will appear below. It should read 'C:\>' followed by the blinking cursor.
12. Type 'dir /ad /s >c:/directories.txt” (do not type the quotation marks) . Press the “Enter” key on your keyboard.
13. Open Excel. Click the “Office” button and “Open.” Choose “Text Files (*.prn; *.txt; *.csv)” from the “Files of type” drop-down box. Select the file “c:\directories.txt.” Click the “Open” button.
14. Click the button for “Delimited.” Click the “Next” button.
15. Check “Tab” in the “Delimiters” area of the “Text Import Wizard” window. Click the “Next” button.
16. Click the “Finish” button. Save the file.

How to Share a Workbook

Setting Up Workbook Sharing in Excel 2003 or Earlier
1. Open Microsoft Excel and then open the workbook that you want multiple people to be able to edit at once.
2. Click 'Tools' and then click 'Share Workbook' to load the Share Workbook options window.
3. Check the 'Allow Changes by More Than One User at the Same Time. This Allows Workbook Merging' option under the 'Editing' tab. Click 'OK.'
4. Click 'File' and then 'Save' to update the interactivity to the workbook.
5. Tell other users that they can open the workbook. At any time, click 'Tools' and then 'Share Workbook' to see who has accessed the workbook and who is accessing it at any given time.
Setting Up Workbook Sharing in Excel 2007 or Later
6. Open Microsoft Excel and then open the workbook that you want multiple users to edit simultaneously.
7. Click the 'Review' tab in the right-side window pane. Click 'Share Workbook' under the 'Changes' section.
8. Check the 'Allow Changes by More Than One User at the Same Time. This Allows Workbook Merging' option under the 'Editing' tab. Click 'OK.'
9. Click 'File' and then click 'Save' to update the document for workbook sharing. Other users can now access the selected workbook simultaneously.

How to Calculate Excel Formulas With Multiple Pages

3-D Reference: Contiguous Worksheets
1. Create an Excel workbook with four worksheets.
2. Enter a numeric value in cell A1 of Sheet2, Sheet3 and Sheet4. Start by making a simple formula to sum up values across these three worksheets.
3. In any cell on Sheet1 enter an equals sign followed by your function and an opening parenthesis. For our example, enter “=SUM(”.
4. Click on the 'Sheet2' tab at the bottom of your workbook and select the cell in Sheet2 that you want to include in your sum. For our example, use cell A1.
5. Hold down the shift key and click on the 'Sheet4' tab. The formula updates to include the same cell on Sheet4 that you selected for Sheet2. Complete the formula by entering a closing parenthesis. Your formula will look like this: =SUM(Sheet2:Sheet4!A1). This formula sums up the values in cell A1 on Sheet2, Sheet3 and Sheet4. Notice the format of the resulting formula. The colon between the sheet names indicates that the formula spans all sheets from Sheet2 through Sheet4.
3-D Reference: Selected Worksheets
6. In another cell on Sheet1 enter “=SUM(”. For this example, sum up cell A1 on Sheet2 with cell A1 on Sheet4. This time rather than clicking on cells to select them for the formula, enter the worksheet and cell references directly.
7. Enter the first argument for your sum. The first argument is “Sheet2!A1”. In this case, “Sheet2!” is the worksheet reference and “A1” is the cell reference.
8. Enter a comma followed by the second argument. The second argument is “Sheet4!A1”, where “Sheet4!” is the worksheet reference and “A1” is the cell reference.
9. Enter a closing parenthesis. Your complete formula should look like this: =SUM(Sheet2!A1,Sheet4!A1). This formula will sum up the A1 cells on Sheet2 and Sheet4 and exclude the value in cell A1 on Sheet3. Notice how the format of this formula differs from the previous formula. The references are separated by a comma, indicating that only the stated references are included in the calculation.

How to Get a Date Using VLOOKUP in MS Excel 2003

1. Apply cell formatting to any cells that will contain dates. Start a sample project by formatting the entire worksheet. Click the 'Select All' box in the upper left-hand corner. Click 'Format > Cells.' On the 'Number' tab under 'Category,' select 'Date.' Under 'Type,' choose a date format. Click 'OK.'
2. Create column headings. In cell A1, type 'Enter Start Date.' In cell B1, type 'End Date.' In cell C1, type 'Start.' In cell D1, type 'End.'
3. Build a lookup table. The VLOOKUP function will look for a certain date in the left-hand column and return the corresponding date in the right-hand column. For the sample table, enter start dates in column C and completion dates in column D, as follows:Cell C2: 1/1/10
Cell C3: 2/12/10
Cell C4: 3/15/10
Cell C5: 4/19/10Cell D2: 1/29/10
Cell D3: 3/1/10
Cell D4: 4/17/10
Cell D5: 5/2/10
4. Enter a value to look for. In cell A2, type one of the starting dates you entered in column C, such as '4/19/10.'
5. Write a formula instructing Excel to look for the start date in cell A2 within the range C2:D5, then return the end date in column D (or 'column 2'). In cell B2, type:=VLOOKUP(A2, C2:D5,2)Press 'Enter.' The date 'May 2, 2010' will appear in your selected date format.

How to Get Percentages in Excel

1. Open a Microsoft Excel spreadsheet.
2. Input a number in one cell, which corresponds to an amount. Input another number in another cell corresponding to the total.
3. Select a blank cell where to calculate the percentage between the two. The percentage formula is equal to the amount divided by the total. The total is usually greater than the amount.
4. Type '=Cell1/Cell2' without the quotation marks in the blank cell, where cell1 corresponds to the location of the first cell and cell2 corresponds to the location of the second cell. Press 'Enter' to get the fractional value. For example, if you placed an amount in cell A1 and the total in cell A2 and you selected cell A3 to calculate the percentage, then A3 should contain the formula: =A1/A2
5. Select the cell you want to format as a percentage to convert the fraction into a percentage. To format a row, click on the row number on the leftmost side of the worksheet to select the entire row. To format a column, click the column letter on the upper side of the worksheet to select the entire column. To format the entire worksheet, select the rectangular box on the upper left side corner between the first row (Row 1) and the first column (column A.) To format nonadjacent cells, click the first cell then press the 'CTRL' key. Continue holding the 'CTRL' key while clicking on other cells you want to format as percentage.
6. Right-click anywhere on the cell you want to format and click 'Format Cells...' to open the Format toolbox.
7. Go to the 'Number' tab and under 'Category' choose 'Percentage.'
8. Choose the decimal places on the 'Decimal places' drop down. The default is set to 2, which means that the percentage will have up to two decimal places like 0.25%.
9. Click 'OK' to close.

Sunday, June 23, 2013

How to Create Excel Templates

1.
Open an existing Excel spreadsheet that has all or most of the features you wish to use. Save it as a workbook with a different name, so that you don't accidentally lose any vital information.
2.
Delete any information that you do not want, such as names and numbers. Leave formulas that you will want to continue to use with the template.
3.
Create a new worksheet if you do not have an existing spreadsheet that contains the formatting you require. Format the cells, such as adding borders, choosing font options and entering formulas.
4.
Save the document by going to the 'File' menu and choosing 'Save As' in Excel 2003 or by clicking the Office button in Excel 2007 and clicking 'Save As.' In the 'Save As Type' dropdown, choose 'Template' and name your new template in the 'File Name' box. The location will default to your template folder. Click 'Save.'
5.
Use the Excel template you created by clicking 'New' in the 'File' menu in Excel 2003 or by clicking the 'Office' button and clicking 'New' in Excel 2007. Choose 'Templates On My Computer' and find the name of the template you created. Click to select it and then click 'OK.'

How to Make Text Over Images With Microsoft Excel

Clip Art and Pictures
1. Add a clip art image or picture to your spreadsheet. Go to 'Insert' and choose 'Picture.' Select 'Clip Art,' 'From File' or 'From Scanner or Camera.' Select your image to insert it into the spreadsheet. Place the image in your desired location.Note that if you want to set transparency options on images from your computer, scanner or camera, they must be bitmap images. These images have a .bmp extension.
2. Right-click the image and select 'Show Picture Toolbar.' If the toolbar is already shown, you'll only see an option to 'Hide Picture Toolbar.'
3. Click the 'Set Transparent Color' button on the picture toolbar.
4. Click the color on your image you wish to make transparent. Only the text behind the color you choose will be transparent.
5. If transparency settings aren't available or you need more than one color to be transparent, add a transparent text box over your image. Select 'Text Box' from the Drawing toolbar. Go to 'View' and select 'Drawing' to view the toolbar. Draw a text box over your image. Type in your desired text.Right-click the text box and select 'Format AutoShape.' Select 'Colors and Lines.' Set the 'Transparency' to 100 percent. Press 'OK.' Your text will be visible over the image, but the text box itself will not be seen.
Drawing Images
6. Draw an image on your spreadsheet. Drawn images are created using tools from the 'Drawing' toolbar. Go to 'View' and select 'Drawing' to view drawing tools.
7. Right-click the image and choose 'Format AutoShape.'
8. Select the 'Color and Lines' tab. Locate the 'Transparency' slider. Slide the transparency bar to at least 50 percent. You can also type the transparency in the box beside the slider. Press 'OK' to set the transparency. If you need the object to be lighter or darker, repeat the process.

Timeline Excel Tutorial

1. Open your Excel worksheet.
2. Click the “Insert” tab on the command ribbon.
3. Click the “SmartArt” button in the Illustrations group. A dialogue window opens prompting you to choose a SmartArt graphic.
4. Click “Process” on the left pane to open the Process gallery, which includes timelines.
5. Click a graphic, such as “Basic Timeline.” A larger, detailed view of the graphic and a description appear in the right pane of the dialogue window.
6. Click “OK.” The selected graphic appears on the worksheet. The “SmartArt Tools” ribbon displays the “Design” and “Format” tabs.
7. Click the “[Text]” area. Type in the bracketed text areas.
8. Customize the graphic by clicking the command buttons in the “Design” and “Format” tabs. The “Design” tab contains “Layouts” and “SmartArt Styles.” The “Format” tab contains “Shape Styles” and “WordArt Styles.”
9. Save this file.

How to Set Up a Budget on Excel

Set Up Income and Expense Columns
1. Open the Excel application to a blank spreadsheet.
2. Click cell 'A1' and enter a title for your budget. Type something like 'Monthly Budget' and maybe a 'Last Updated on...' reference to help you quickly see when you last made changes.
3. Click into cell 'A3' and enter 'Item.'
4. Click into cell 'B3' and enter 'Income.'
5. Click into cell 'C3' and enter 'Expenses.'
Add Budget Line Items and Dollar Amounts
6. Click into cell 'A4.' Start listing your income and expenses, one in each row down the page. Start with your income first. You can double-click the vertical line between Column A and Column B headings at the top of the screen to expand Column A to the width of your characters and avoid cutting off information.
7. Decide how you want to work with the budget before you enter any numbers. Will it be weekly or monthly? Make sure all the numbers you enter are from the same time period. All weekly income/expenses, or all monthly income/expenses. Don't mix and match or your budget will not be correct.
8. Type income amounts into the B column and expense amounts into the C column.
9. Select the columns with numbers and use the 'Decimal .0 -> .00' button on the toolbar to adjust the format if it's not displaying two decimal places. You can also click the '$' button to format the numbers as money if you want to see the '$' in every cell.
Total Income and Expenses and Compare
10. Click in a cell in Column B that is a few rows below your last budget line item.
11. Enter the formula '=SUM(' and then click and drag from the 'B4' cell down to the last cell in Column B that has a budget item listed.
12. Type a closing parenthesis ') and press 'Enter/Return' to complete the calculation. This will show you your total income for the time period you decided on (weekly or monthly).
13. Click into the cell in Column C next to your income total and repeat the formula to total all the rows in Column C. These are your total expenses.
14. Move over to the next cell in Column D and enter the '=' sign. Click the total income cell once. Type a '-' sign, then click the total expenses cell once and press 'Enter/Return.' This shows how much income you have left after expenses. If it's a negative number, Excel automatically formats it red.
15. Review your budget line items if the ending figure is not to your liking, and see where you can make changes in your income or expenses to adjust that figure.

Saturday, June 22, 2013

How to Edit an MS Excel Drop Down Menu

1. Launch Excel.
2. Select the cell with a drop down menu in it.
3. Click on the Data tab of the Ribbon Interface, and select Data Validation. A dialog box will appear. In Excel 2003, you get to this dialog box by clicking on the 'Data' menu item at the top of the application window and selecting Validation.
4. Look at the 'Source' field. If it has values separated by commas, the only references this menu uses are those values. Change the value. If it begins with an '=' and refers to a word like this: '=List_Menu1,' it's referring to the named range of List_Menu1 somewhere else in Excel. Write down the name of the named range.
5. Click 'OK' to save your changes. If the drop down menu had nothing but comma separated values, you are done.
6. Click on the drop down menu immediately to the left of the Formula Bar and enter the named range you wrote down before. This will cause Excel to jump to that named range and select it for you.
7. Change the values in that named range to suit your purposes. The menu will now reflect those changed values.

How to Create a Drop

1. Click the 'Office' button, and then click 'New.' Click the 'Create' button on the dialog box to accept the default values for a new Excel workbook.
2. Click the 'Office' button again, and then click the 'Options' button. Click the 'Show developer tab' check box to display the tab containing the drop-down calendar control.
3. Click the 'Developer' tab, and then click the 'Insert' button of the 'Controls' panel. Excel will display a list of controls you can insert in your workbook. None of the displayed controls is a drop-down calendar, but Excel enables access to more controls than those shown.
4. Click the icon in the lower-right corner of the control gallery that appears, to run the 'More controls' command. Excel will display a dialog box that lists additional controls you can enter in your workbook.
5. Click the item labeled 'Microsoft date and time picker,' and then click 'OK' to insert the calendar in your worksheet.
6. Right-click the calendar, and then click 'Properties' to display a window allowing you to process the date that a user picks with the calendar control.
7. Type the reference for a spreadsheet cell in the row labeled 'Linked cell.' For example, type 'G4' to indicate cell G4 of the current worksheet. The calendar control will insert the date the user picks in this cell.
8. Click the 'Design mode' button of the 'Controls' panel to exit design mode and enter user-input mode.
9. Click the down arrow of the calendar control, and then click any date in the drop-down calendar that appears. The cell whose reference you entered in Step 7 will fill with the date you chose.

How to Get the Ruler to Show Up in MS Excel

1. Open 'Microsoft Excel.'
2. Select the 'View' tab.
3. Click 'Page Layout,' located in the 'Workbook Views' section.
4. Change your measurement options by clicking the 'Microsoft Office' button. Select 'Excel Options' and choose 'Advanced.'
5. Click the units to use from the 'Ruler Units' box located under 'Display.'

How to Make a Normal Distribution Curve in Excel 2007

1. Open a new worksheet in Excel.
2. Fill in your x-values in column 1, starting at cell A1. For example, if you want your x-values to be from -10 to 10, write '-10' in cell A1, '-9.5' in cell A2, and '-9' in cell A3, all without quotes. Continue down the column until you have entered all of your values through to cell A41.
3. Enter '=NORMDIST(A1,0,1,0)' without quotes into cell B2. This formula is for the standard normal distribution, with a mean of 0 and a standard deviation of 1. You can enter any mean or standard deviation into this formula. For example, if you want a mean of 10 and a standard deviation of 2, enter '=NORMDIST(A1,10,2,0)' without quotes .
4. Highlight the two columns by left clicking on cell A1 and dragging your cursor down to cell B41.
5. Choose 'Insert | Scatter| Scatter with Smooth Lines and Markers.'

Friday, June 21, 2013

How to Display Fractions in Excel

1. Start the Microsoft Excel program.
2. Open the document you wish to add the fractions to by clicking 'Open' on the toolbar. Select 'Open' from the drop-down list. Select the file from the list.
3. Select the box, or cell, where you want to display the fraction. If you want to display fractions in multiple cells, highlight all of the cells.
4. Right-click on the cell and select the 'Format Cells' option. Another alternative is to click on 'Format' in the toolbar at the top and then select 'Cells' from the drop-down menu.
5. Pick the 'Numbers' tab. A list of categories is displayed. Select 'Fraction' from the category list.
6. Select the type of fraction you want to display. Click 'OK' to format the cell and close the display.
7. Type the fraction into the cell. For example, type '1/2' without any spaces. Excel automatically reduces fractions; if you enter 4/8, it changes to 1/2.
8. Input a whole number and a fraction in the same cell by typing the whole number, a space and then the fraction.

How to Convert Excel to Access

1. Edit the Excel file. Eliminate any rows or columns that you don't want to import. If you only want some of the data in your Access database, create a new named range that contains only the cells you want to include.
2. Create the new Access database. Click on File, get external data, import. Change the file type to excel (xls) and then select the spreadsheet to import. You will have to do one table within the spreadsheet at a time. If import errors occur, you may need to make corrections to the spreadsheet, then do the import process again.
3. Access will add a primary key by default. Primary keys are helpful if you will be linking tables. If you already have an idea of what tables to link, you can define the field that will be the primary key during the import. For example, if two tables both contain a household inventory and you want these tables to be combined on a form, you can select the name of the inventory item as the primary key.
4. Import spreadsheets manually by selecting the entire spreadsheet in Excel, if you prefer. Click the top-left corner to highlight the whole worksheet. Click ctrl-c to copy the columns. Create a new table in Access, then click the top left corner, and click ctrl-v to paste. Your Excel tables will be pasted into the Access database.
5. Create forms for entering data and reports to generate reports by using the wizards for forms and reports.

How to Make Column Titles in Excel 2007 Appear at the Top of Each New Page

1. Open the Excel 2007 file that contains your worksheet. Select the worksheet that you want to print from the list of worksheets at the bottom of the Excel window.
2. Click the 'Page Layout' tab at the top of the screen. Locate the 'Page Setup' area of the ribbon and click the 'Print Titles' button.
3. Select the 'Sheet' tab in the window that appears. Find the entry that reads 'Rows to repeat at top' and click the small box to the right. The 'Page Setup' window will minimize.
4. Select the row that contains the titles you want to repeat. This is normally the first row, but can be any row you desire. If you choose a row that is not on the first page, the column titles will not appear on any page before the one you selected. Click the small box in the minimized 'Page Setup' window to bring the window back up. You'll see your select row next to the 'Rows to repeat' entry.
5. Select 'OK' to close the window and complete the process, or you can click 'Print Preview' to get an idea of how the pages will look.

How to AutoFit Column Width in Excel

1. Open Microsoft Excel using the 'Start' menu or a shortcut. Open the document with which you want to work.
2. Click the column you want to modify. To select more than one column, press and hold the 'Ctrl' key while clicking on each column.
3. Click the 'Format' drop-down menu in the 'Cells' group of the 'Home' tab. Select the 'AutoFit Column Width' option in the 'Cell Size' section.

Thursday, June 20, 2013

How to Use Excel for a Questionnaire

1. Click the 'Developer' tab, and then click the 'Insert' command on the 'Controls' panel. A gallery of controls for your questionnaire will appear.
2. Click the 'Text box' button, whose icon looks like a rectangle with text. Drag on the current spreadsheet to grow the text box.
3. Right-click the text box, then click 'Properties.' Excel will display a window showing modifiable attributes of the text box control. Type'J5' in the 'LinkedCell' rows of the 'Properties' window. Excel will use this cell reference to insert questionnaire data entered into the text box control.
4. Use the instructions from the Step 2 and Step 3 to create another text box below the first one. However, type 'K5' for the 'LinkedCell' reference this time.
5. Use the instructions from Step 1 through Step 4 to insert a check box control below the second text box. This control looks like a small square. Type 'L5' for the LinkedCell reference.
6. Type the following labels in the cells to the left of your three controls: 'First name,' 'Last name,' and a sample yes or no question, such as 'Check here if you think insects should be given equal rights.'
7. Click in cell 'J4,' and then type the following column labels. Press the 'Tab' key in place of the commas:'First name,' 'Last name,' 'Question.'
8. Run the questionnaire by clicking the 'Design mode' button of the 'Controls' tab. Type your name in the text boxes, and click the check box control. Notice that Excel automatically populates the columns you typed in the previous step with your answers.

How to Convert Excel 2007 to Excel 97

1. Open the workbook in Excel 2007.
2. Click the 'Office' button.
3. Choose 'Save As'.
4. Click on 'Excel 97-2003 Workbook' from the list of 'Save As' options. Excel will save the file in Excel 97 format. The file can now be opened in Excel 97.

How to Make a Field Mandatory in an Excel Spreadsheet

1. Launch Microsoft Excel.
2. Click the 'Office' button at the top of the screen, then select the 'Excel Options' button at the bottom of the window. Choose the 'Show Developer Tab in the Ribbon' option, then click the 'OK' button.
3. Press 'Alt F11,' then double -click the sheet in the left-hand column that will have the mandatory field.
4. Type the following VBA code into the window:Sub Button1_Click()If Range('A2').Value = '' ThenMsgBox ('Please insert value in cell A2')Exit SubEnd IfEnd SubYou can replace each instance of 'A2' to correspond to the cell or range of cells you are making mandatory. Additionally, whatever function this button will perform will be defined between 'End If' and 'End Sub.'
5. Return to your spreadsheet, then click the 'Developer' tab at the top of the screen and click the 'Insert' button.
6. Select the 'Button' option from the 'Forms' toolbar, then select a location on your spreadsheet for the button.
7. Click the 'Button1_Click' option from the list under 'Macro Name,' then click the 'OK' button.

Wednesday, June 19, 2013

How to Remove Conditional Formating in an Entire Sheet in Microsoft Excel 2003

1. Click 'Start,' then 'All Programs,' then 'Microsoft Excel.'
2. Press 'Ctrl' 'O.' Locate and open your workbook that contains conditional formatting.
3. Press 'Ctrl' 'A' twice to highlight your entire worksheet.
4. Click 'Format,' then 'Conditional Formatting.'
5. Click 'Delete,' then check all three of the check boxes. Click 'OK' to remove the formatting.

How to Apply Themes and Styles in Excel 2007

Themes
1. Open a spreadsheet in Excel 2007. A spreadsheet allows you to enter data in the format of rows, columns and individual cells.
2. Type data into rows and columns. Organize your numerical data or details into an organized spreadsheet.
3. Open the 'Page layout' tab. Click on the 'Theme' group. View options to change colors, fonts or effects.
4. Apply a template theme by clicking the theme under the Built-In tab.
5. Create a custom theme. Click the document theme that you want to use under 'Custom.'
6. Look for more by clicking on 'Browse for Themes' to search for more theme ideas.
7. Change fonts, effects and colors to make your theme become more unique and original.
Styles
8. Apply a Quick Style to your spreadsheet. A Quick Style is a collection of formatting alternatives that make designing your documents more convenient.
9. Utilize a cell style. A cell style is a defined set of formatting uniqueness in fonts, font sizes, number formats, shading and borders.
10. Select a built in cell style that is modified immediately after being selected.
11. Create your own cell style by modifying and duplicating a built in cell style.

How to Hide X on the UserForm Title Bar in Excel 2003

1. Open your Microsoft Excel 2003 document and scroll to the right until you get to the “X” column.
2. Right-click on the column header (“X”) in the user form title bar and select the “Hide” option. The “X” column will now be hidden from view in the spreadsheet.
3. Click and drag from the “W” column to the “Y” column to select both columns and then right-click on one of them. Select the “Unhide” option to unhide the “X” column again.

How to Revert Excel 2007 to A1 Format Instead of R1C1

1. Click on the Windows 'Start' button and select 'All Programs.' Open the 'Microsoft Office' folder, and then click on 'Microsoft Office Excel' to launch the application.
2. Click on the 'Office' button in the top-left corner of the screen and then click the 'Excel Options' button at the bottom of the menu.
3. Click on the 'Formulas' tab in the 'Excel Options' window. Under the 'Working with Formulas' section, clear the checkbox labeled 'R1C1 Reference Style.'
4. Click the 'OK' button to apply the setting. The change to the cell referencing scheme happens instantly, and you will see the columns are now labeled with a letter rather than a number.

How to Capitalize the First Letter in Excel

1. Open the Excel 2010 file that contains the text that you want to capitalize.
2. Right-click on the column letter directly above the first cell that contains your text. Choose 'Insert' from the pop-up menu and a new column will appear to the left of your selected column.
3. Click on the cell directly to the left of the first cell that holds your text.
4. Enter the following formula into the cell: =upper(left(XX,1))right(XX,LEN(XX)-1) where 'XX' is the column letter followed by the row number of the cell to the right of the current one. Press 'Enter' when you are finished entering the formula and you should see your text repeated in the formula cell, except with the first letter capitalized.
5. Click the cell where you entered your formula and press 'Ctrl' and 'C' to copy the formula to the clipboard. Paste the formula, using 'Ctrl' and 'V', to an empty cell to the left of any text you want to capitalize this way. You may need to insert additional columns to get an empty cell to the left of your text.
6. Right-click the cell with your formula and choose 'Copy' from the list of options. Even if you already have this cell in the clipboard, it is better to be sure that you are copying the correct cell.
7. Right-click on the cell with the original text. Move your mouse over 'Paste Special' in the pop-up menu and choose 'Paste Values.' Repeat this for any other cells that you are capitalizing.
8. Right-click on the letter above the column you created earlier in the process. Choose 'Delete' from the menu to remove the column, leaving you with just the capitalized text. Repeat this for any other columns you created.

Sunday, June 16, 2013

How to Make Excel 2003 the Default

1. Click 'Start' and type 'regedit' in the Windows search box. Click 'regedit.exe' from the list of files that appears.
2. Locate the 'HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options' key and right-click on the 'Options' folder. Click 'New' and 'DWORD Value' and enter 'NoReReg' for the Dword Value. Right-click on the 'NoReReg' value that you created and click 'Modify.' Type '1' in the 'Value Date' box and click 'OK.'
3. Open Excel 2003 and select 'Detect and Repair' from the 'Help' menu. Click 'Start' and click 'OK' when the process finishes.

How to Delete Cells in Microsoft Excel

1. Select the cell or range of cells you want to delete. To select multiple cells, click in the first cell and drag down or over to the last cell.
2. Go to the 'Edit' menu and choose 'Delete.' A dialog box pops up.
3. Decide how you want your data moved to fill the space of the deleted cells. Choose 'Shift cells left' to have the data in the rows with the deleted cells slide over. Choose 'Shift cells up' to have the data in the columns with the deleted cells move up. All formulas will adjust accordingly.
4. Delete an entire row or column in Excel by choosing 'Entire row' or 'Entire column.' All data under a deleted row shifts up and all data to the right of a deleted column shifts to the left.
5. Click 'OK' to confirm to Excel you want to delete the cells.
6. Clear the contents of a cell without removing the actual cell by selecting the cells you want cleared and pressing 'Delete' on your keyboard. The data is removed from the cell, but all formatting remains. To clear formulas or comments from the cells, choose 'Clear' from the 'Edit' menu and then click what you want cleared.

How to Combine a Graph

1. Open the Excel Program.
2. Click the 'File' tab on the command ribbon. A list of commands appears.
3. Click 'Open.' Select the workbook to open.
4. Click the 'Insert' tab on the command ribbon.
5. Click and drag on the categories and data cells for the chart.
6. Click the arrow next to the chart style in the 'Chart' group.
7. Click to select the preferred chart type, for example, '2-D Column.' The data converts to an embedded chart. The 'Chart Tools' ribbon appears with three tabs: 'Design,' 'Layout' and 'Format.'
8. Click the 'Design' tab on the 'Chart Tools' ribbon.
9. Click one data series on the chart to display as a different chart type. For example, one color column. If no data series is selected, the whole chart will change.
10. Click the 'Change Chart Type' button in the 'Type' group. The 'Change Chart Type' dialog window opens with a gallery of charts.
11. Click a different chart type. For example, 'Line.'
12. Click 'OK.' The selected data series converts to the new chart type. Two different chart types appear on the screen.
13. Edit the combined chart, if preferred. To bring a data series closer to the other data series, right-click one data series. A list of options appears. Click 'Format Data Series.' A 'Format Data Series' dialog window opens. Select the radio button for 'Secondary Axis.' Click 'Close.' The chart converts.

Saturday, June 15, 2013

How to Create a Multiple Stacked Chart in Excel 2003

1. Open your Microsoft Excel spreadsheet filled with your data sets.
2. Click your cursor on the 'A1' cell. Hit the 'Control' ('Ctrl') button and then press the 'A' key without letting go of the 'Control' button. You will see the cells where you have typed your data become highlighted. Keep in mind that you need at least two sets of data to create a stacked column chart.
3. Click 'Insert' on your toolbar.
4. Click Column. Select one of the 'stacked' column styles. You can choose, for example, a cylindrical stacked column or a conical stacked column. Excel will turn your data into a chart, and that chart will appear on your screen.

How to Create a Combination Chart in MS Excel

1. Open a blank worksheet in Microsoft Excel.
2. Create a data table. For instance, enter time values in column A, such as the months of the year. In column B, enter a column heading for your first data series, such as number of items sold, followed by the data values. Enter a second data series in column C, such as average price, with each data value lining up with a value in column A.
3. Highlight all of the data you want to plot in a chart, including both data series.
4. Click the 'Insert' tab. In the 'Charts' group, select the chart type you want to use for the first data series. For instance, to create a basic column chart, click 'Column' and select a '2D Clustered Column Chart.' The chart will appear on the worksheet with the data values you entered.
5. Click the data series on the chart that you want to make into a different chart type. Make sure only one data series is selected.
6. Click the 'Design' tab. In the 'Type' group, click 'Change Chart Type.' Choose the chart type you want. For instance, to change the series to a line graph, select 'Line with Markers' in the 'Line' group. Click 'OK.'
7. Change the second data series to have a secondary vertical axis. To do so, select the data series in the chart. Click the 'Layout' tab. In 'Current Selection,' select 'Format Selection.' Click 'Series Options' in the dialog box. Click 'Secondary Axis' under 'Plot Series On.' Click 'Close.'
8. Add labels to the vertical axes by clicking the 'Layout' tab. In the 'Labels' group, click 'Axis Titles.' Select 'Primary Vertical Axis' and choose a title option. Do the same for 'Secondary Vertical Axis.' Click each axis in the chart and enter a new title.
9. Select the chart by clicking the bounding box around it. Browse through the 'Design, Layout and Format' tabs. You can make changes to the chart style, outline and layout, as well as format specific chart elements.
10. Click the 'Microsoft Office Button' and choose 'Save as' to save the combination chart and data.

How to Make a Line Graph in Microsoft Excel

1. Arrange the data in either rows or columns. Excel makes the decision as to how to best graph the line based on the data input you provide. You need at least two sets of data to create the axes for your chart. For example, build a line graph using one row or column that provides time periods, such as months or quarters, and the other row or column listing gross sales.
2. Select the data in the rows or columns to include in the line chart. Click and drag your mouse across the data in the Excel spreadsheet to include it.
3. Click the Insert tab and review the available types of Chart. Select 'Line' from the options offered.
4. Review the embedded chart in your spreadsheet. To move the chart to its own sheet, select it. The Chart Tools display, which adds several tabs, including Design, Format and Layout. From the Design tab, click 'Move Chart,' which resides in the Location group. Select 'New sheet' from the 'Choose where you want the chart to be placed' options.
5. Modify the title, which defaults to 'Chart1' by selecting the Layout tab. In the Properties group, click the 'Chart Name.' Enter the name and press the 'Enter' key.
6. Modify the layout by using a pre-determined format from Excel. Select the line chart. The Chart Tools become available. Select from among the options available in the Chart Layouts group from the Design tab.
7. Add titles to the axis. Click the chart to active the Chart Tools. From the Layout tab, review the Labels group. Select 'Axis Titles,' and enter the axis name for the primary horizontal and vertical axes.

How to Delete a Macro in Excel

1. Start Microsoft Excel 2007, and open a spreadsheet from your files that contains at least one macro that you want to delete.
2. Select the 'Developer' tab from the top of the Excel screen. The 'Developer' ribbon will be displayed just under the tab.
3. Find the 'Code' group in the 'Developer' ribbon and click the 'Macros' button. The 'Macro' dialog box will open onto your Excel screen.
4. Choose the name of the macro you want to delete from the list that appears in the 'Macro' dialog box. The macro name will be highlighted in black once it is selected.
5. Click the 'Delete' button on the right side of the 'Macro' dialog box to instruct Excel to delete the selected macro. A dialog box will appear asking you if you are sure you want to delete the selected macro.
6. Choose the 'Yes' button to confirm that you want to delete the selected macro. The macro name will be deleted from the list in the 'Macro' dialog box and the dialog box will close.
7. Repeat the steps above to delete any additional macros from the open spreadsheet.

Friday, June 14, 2013

How Do I Exit From the Header in Office 2007 Windows Vista?

1. Look at the top of your Word program window and locate the text 'Header and Footer Tools.'
2. Under 'Header and Footer Tools,' click 'Design.' The 'Design button will be immediately below the words 'Header and Footer Tools.'
3. Click the red 'X' on the far right side of the Design menu that says 'Close Header and Footer,' under it.

How to Make a Second Line in One Cell in MS Office Excel on Mac

1. Position your cursor inside the cell at the exact place you want to create a new line, such as at the end of the first line.
2. Hold down the 'Option' and 'Command' keys on your keyboard.
3. Press 'Enter.' Release the 'Option' and 'Command' keys. You now have a new line.

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.

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

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.

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.

Thursday, June 13, 2013

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.

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.

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.

Wednesday, June 12, 2013

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.

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.

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.

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.

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

Tuesday, June 11, 2013

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

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.

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.

Subscribe to: Posts (Atom)