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

Read more ►

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

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►