How to Make a Drop

1. Open Excel and select a range of cells to which you want to apply the data validation. For example, select cells A1 through A10. With the cells selected, click the “Data” menu and choose “Validation.”
2. Change the Allow criteria to “List” in the validation criteria. In the Source field, add the values you want to see in the drop-down list. Enter the values separated by a comma only. Do not include spaces. For example, enter “Yes,No,Maybe” to see those three options in the drop-down list.
3. Modify the input message so other users see a friendly reminder telling them what you are looking for in these cells. Click on the “Input Message” tab. Make sure the “Show Input Message When Cell is Selected” option is checked. In the title field, add a title for your custom message. Type your custom message in the “Input Message” field.
4. Modify the error alert message so users see a error alert if they input the wrong type of information. Click on the “Error Alert” tab. Place a check mark in the “Show Error Alert After Invalid Data Is Entered” option. Change the style to “Stop” if you want the message to stop users from inputting invalid data. In the title field, add a title for your custom message. Type your custom message in the “Error Message” field. Click “OK.”
5. Test your data validation by clicking on cell A1. You will see a drop-down list appear with the options Yes, No and Maybe. You should see the input message you specified. Try typing in the word “PDF.” The custom error message will appear. The invalid value will not be retained and you will get the prompt to “Retry” and enter a correct value.

Read more ►

How to Wrap Text in Microsoft Excel 2003

1. Highlight the cells in which you want to wrap the text. If you want more than one cell, drag your cursor to the cells.
2. Find the 'Format' tab on the menu bar and click 'Cells.' Then click the 'Alignment' tab.
3. Click on 'Text control' and choose the 'Wrap text' option. Click 'OK.'

Read more ►

How to Print Two Excel Worksheets on Opposite Sides of a Paper

1. Click the 'Start' button in the bottom-left of the desktop. Click 'All Programs' and then click 'Microsoft Excel 2007' to launch the program.
2. Click the 'Microsoft Office Button' from the upper-left of the Excel program and then click 'Open.' Click the Excel workbook of choice and then click 'Open.'
3. Click the 'Microsoft Office Button,' and then select 'Print' from the drop-down menu. Then click 'Print' again, and click 'Properties.'
4. Click the 'Layout' tab and click 'Duplex' or turn on whatever two-sided printing option is available.
5. Click 'OK' to print.
6. Repeat the steps for each worksheet that you wish to print duplex in Excel.

Read more ►

How to Insert Functions in Excel 2007

1. Insert a function into an Excel cell manually by clicking in the cell, typing it in and pressing the 'Enter' key. The AutoComplete menu will pop up as you begin to type the formula. If you do not need it, ignore it. If you are unsure how to enter a function without assistance, use the 'Formulas' menu.
2. Insert a function into an Excel cell by selecting the 'Formulas' tab on the Top menu. This is especially helpful if you are unsure what function to use.
3. Click the button that most closely describes the action you are trying to perform with the function. A pull-down list of functions opens. Hover over each function with your mouse and a box describing the function appears.
4. Left-click the function to automatically insert it in the cell. A box opens that tells you exactly what the function does and how to properly structure the variables. Enter the variables and select the 'OK' button.
5. Click in the cell to make any changes to the function. A structure guide for the formula will automatically appear below the cell as long as you have the Formula AutoComplete tool turned on.
6. Turn on the Formula AutoComplete option if it is not already on. Click the 'Microsoft Office Button,' click 'Excel Options' and then click the 'Formulas' category. Under 'Working with formulas,' check the box next to 'Formula Auto Complete' and click the 'OK' button. You can enter a formula with the assistance of the Formula AutoComplete tool.
7. Start typing the formula. When you reach the first letter of the function you would like to use, it triggers Formula AutoComplete. Using the SUM function, for the example, when you type in '=s,' Excel filters the Formula AutoComplete list by all the functions and active cell references that begin with 's.' Typing in the next letter ('=su') filters the list to all options that begin with 'su' and the filtering continues as you type in more letters.
8. Navigate the AutoComplete menu using the arrows on your keyboard or selecting each one by left-clicking with your mouse. As you land on each option, Excel displays a Screen Tip designed to help you choose the best option.
9. Double-click your selection and the function will be entered with the left parenthesis in place ('=sum(') and a description of what the formula should look like underneath. For the SUM function, this looks like 'SUM(number 1, [number 2], [number 3], ...).' Review the description if necessary to ensure that your formula is correctly constructed.
10. Now enter your data and close the formula with the right parenthesis: ').' Using the SUM function, your function could look like this: '=sum(5, B1:B25, c23, c1, b1 b2).' You have inserted a function into Excel.
11. Understand your formula.This formula states, 'five (5) plus the sum value of the range b1 to b25 (B1:B25) plus the value in cell C23 (C23) plus the value of cell B1 plus the value of cell B25 (B1 B25).This formula demonstrates some of the structures defined as numbers by Excel.

Read more ►

How do I Recover an Overwritten Excel File?

1. Locate the document that overwrote the old one you want to retrieve. Right-click the file name, then select 'Rename.' A box will pop up with the file name. Add the word 'new' or today's date to make it easier to tell it apart from the file you want to recover. Once you've edited the name, click 'OK' or 'Continue.'
2. Download XLS Regenerator (see 'Resources'). This program is made specifically to recover Excel files. Start with the free trial by clicking 'Free trial' on the main page, then following the instructions to download for your operating system. Once it's downloaded, double-click on the icon to install.
3. Run the program XLS Regenerator by double-clicking on the icon once it's installed. When the main screen pops up, select 'Recover files,' then 'Recover overwritten files.' The program will now ask for information on the file to be recovered. If you remember the date it was last open, select the date. Then type in whatever you remember from the file name. Click 'Start.'
4. Wait for the program to search your computer for overwritten files. Usually this is in the form of an automatic save--often a hidden file--made before the first overwrite. When it finishes searching, look at the list for the document or documents you want to retrieve. Select all of the documents you want (avoiding the newly named file from step 1) and click 'Retrieve' in the lower right corner.
5. Move the retrieved files to a new folder once they've finished. Open each file to see if it has the correct information you need. If not, repeat steps 3 and 4 with different search parameters until you locate the overwritten documents.

Read more ►

How to Name a Column in Excel 2007

Using Name Box
1. Click the column heading, such as 'A,' 'B' or 'C,' to select the column you want to name in Excel 2007.
2. Go to the 'Name Box' just above column A.
3. Type the column's name into the box and press 'Enter.' The column's name displays when you select the column header.
Using R1C1 Reference Style
4. Click the Customize Quick Access Toolbar button and select 'More Commands' in Excel 2007.
5. Select the 'Formulas' tab in the Excel Options window.
6. Check 'R1C1 reference style' under the 'Working with formulas' section. Click 'OK.'

Read more ►

How to Split Excel Data Into Two Columns

Prepare Your Columns
1. Open the Excel file with data that you want to split.
2. Insert a blank column to the right of the column with data you want to split. For example, if the column you want to split is column F and data exist in column G that you do not want overwritten, insert a new column between columns F and G. To insert a column, click any cell in the column to the right of your data column.
3. Click the 'Home' tab on the ribbon.
4. Click the 'Insert' button in the Cells group.
5. Select 'Insert Sheet Columns' to create a blank column to the left of your cursor, that is, to the right of your data column. For example, if your data reside in column F, you would click on any cell in column G before inserting the column. Excel creates a new column to the left of column G.
Split Your Column
6. Click the 'Data' tab in the ribbon.
7. Select the column you want to split by highlighting the column. To highlight the column, click on the column letter.
8. Click the 'Data' tab on the ribbon. Click 'Text to Columns.' You will see a wizard pop up that will guide you through the process.
9. Select either 'Delimited' or 'Fixed Width,' and click 'Next.'
10. Select the kind of delimiters you want if you selected 'Delimited' in the previous screen. The most common delimiter is a space. Select as many delimiters as apply. Excel will use this information to determine where one piece of data ends and another begins so it can split the data appropriately. If you selected 'Fixed Width,' you will see a ruler above your data. Click on the ruler at the desired points to create a break.
11. Click 'Next.' You will see the third step in the process.
12. Select a format for each of your two columns if you want. However, this is not necessary, and you can always adjust the formats later. Excel will assign the General format for each column as the default.
13. Click 'Finish.'

Read more ►

How to Do a Comparative Chart in Excel

1. Open the Excel worksheet that contains the data for the chart.
2. Click and drag to select the data values and categories.
3. Click the “Insert” tab on the command ribbon.
4.
A column chart can display different categories or data seriesin different colors.
Click the arrow for a preferred chart type in the 'Charts' group. For example, a “2-D Column” chart type can display two or more data series as different colors or shades. A gallery of chart sub-types appears.
5. Click the preferred chart sample. The worksheet data converts to the selected chart type. The chart appears with the worksheet. Each data series displays a different color. The “Chart Tools” ribbon displays.
6. Customize the chart with the “Chart Tools” command. For example, click the “Design” tab to display the “Chart Layouts” and “Chart Styles.” Click the “Chart Styles” to convert the colors of the data series.

Read more ►

Excel Graph Tutorials

1. Open the Excel 2010 spreadsheet that contains the data that you want to use in a graph.
2. Organize your data so that you have headers at the top of each column, and, if necessary, at the left end of each row. If you need to create a header row or column, just right-click the top or leftmost row or column and click 'Insert' from the pop-up menu. A blank row or column will appear, and you can use that to enter your column or row headers.
3. Click on any cell in the data field, and then click the 'Insert' tab at the top of the screen. Click the small button in the lower-right corner of the 'Charts' area of the ribbon to open up a new window will all of the Excel chart options.
4. Click the type of chart that you want to use from the list on the left side of the window. Excel offers 11 different chart types, ranging from a standard column or bar chart to more exotic doughnut and radar charts.
5. Click the exact chart that you want from the list on the right side of the screen. Each chart type has several different variants that have slightly different appearances and may handle information in a slightly different way. Next, click 'OK.' Excel automatically expands your selection on the spreadsheet to include all the cells adjacent to your selected cell and the chart will appear on the spreadsheet.
6. Click the chart and then click the 'Design' tab at the right side of the ribbon. This tab gives you access to buttons that will change the color and shape of the chart. You can also click the 'Select Data' button to reselect the data that will make up the chart.
7. Click the 'Layout' tab at the top of the screen to change the chart's appearance. The buttons in the 'Labels' section will change how the axis titles, chart title and data labels appear. The 'Axes' area holds buttons that will add or remove axes labels and grid lines on the chart. You can also click the 'Insert' button to add a picture, text box or shape to your chart.
8. Click the 'Format' tab at the top of the screen to get access to the controls that change the overall look of the chart. The 'Shape' area contains buttons that change the appearance of the chart window, including the color of the border and any effects on the border. The 'WordArt Styles' area contains buttons that change the color and effects for the text within the chart. Finally, the 'Size' area contains the length and width of the chart, which you can change to your liking.

Read more ►

How to Highlight Minimum Maximum Values in Excel

1. Click the cell at one end of the range that you are analyzing.
2. Drag the mouse to the cell at the other end, selecting the entire range.
3. Click 'Conditional Formatting' from the 'Styles' tab of the 'Home' ribbon. A drop-down menu will open.
4. Click 'New Rule...' from the bottom of the list.
5. Click 'Format only top or bottom ranked values' from the 'Select a Rule Type' pane.
6. Select 'Top' from the first of the drop-down boxes in the 'Edit the Rule Description' pane.
7. Type '1' into the text box in the 'Edit the Rule Description' pane.
8. Click the 'Format' button, which opens the 'Format Cells' dialog box.
9. Click the color that you want to use to highlight the maximum value. Click 'OK.'
10. Click 'OK' in the 'New Formatting Rule' dialog box. The range's maximum value will now appear highlighted in the color you chose.
11. Repeat Steps 1 through 5.
12. Select 'Bottom' from the first of the drop-down boxes in the 'Edit the Rule Description' pane.
13. Repeat Steps 7 and 8.
14. Choose a different color to represent the range's minimum value. Click 'OK.'
15. Click 'OK' in the 'New Formatting Rule' dialog box. The range's minimum value will now appear highlighted as well.

Read more ►

How to Make a Coordinate Plane in MS Excel

1. Open a new, blank Excel document. Click the rectangle in the upper-left corner of the spreadsheet located at the intersection of column A and row 1. This will select the entire spreadsheet. Click the View tab. In the Show/Hide group, deselect 'Gridlines'.
2. Place your cursor on a line between any two column headers. Your cursor will change to a vertical line crossed by a horizontal arrow. Drag the line left until the column width is exactly 20 pixels. When you release the mouse, all cells will be square. Click in cell A1 to remove the highlighting.
3. Click cell C3 and drag and highlight the 400-cell area to cell V22. In the Font group on the Home tab, click the arrow on the border tool. Select the border that resembles a window with four panes.
4. Click the Insert tab. In the Illustrations group, click the arrow on Shapes. Select the line with two arrow heads.
5. Draw the x-axis between row 12 and row 13. To make a straight line, hold the 'Shift' key while you click and drag. Draw the y-axis between columns L and M.

Read more ►

How to Create a Stacked Chart in Excel 2003

1. Enter your data in column 'A,' with the label for the data in cell 'A1,' and each data value put into cells 'A2,' 'A3' and so on, until you've entered all of the data needed for the chart.
2. Select the range of cells from cell 'A1' through the last cell with a data value in it.
3. Click the 'Insert' menu and select 'Chart.' This will start the Chart Wizard.
4. Click 'Column' for the Chart Wizard from the options under 'Chart Type.' A 'Chart sub-type' menu will show up.
5. Click '100% Stacked Column' and click 'Next.' This selects the stacked chart subtype.
6. Click 'Rows' on the next window of the wizard; this tells where the series data will be imported. Click 'Next.'
7. Clear the check box for 'Show Legend' and add your labels for the chart. Click 'Finish.'

Read more ►

How to Use Indirect in Excel to Link to an External Spreadsheet

1. Create two spreadsheets. One will be named PullData.xlsx. The other will be ReviewData.xlsx.
2. Enter the following into cell A1 of Sheet1 of PullData.xlsx: 'Shazam!'
3. Enter the following formula into cell A2 of PullData.xlsx: '=27 54'
4. Switch to ReviewData.xlsx. Enter the following text into the following cells: In cell B1, enter 'PullData.xslx'. In cell B2, enter 'Sheet1'. In cell B3, enter 'A1' and in cell B4, enter 'A2'.
5. Enter the following formula in cell A1 on ReviewData.xlsx: '=INDIRECT(''['B1']'B2''!'B3)'. Note that there are both double quotes (') and single quotes (') in that formula; they're there to ensure that the formula works even if the file name or the sheet name inside the file have spaces.
6. Hit Enter. Cell A1 in ReviewData.xlsx should now say 'Shazam!' If you change the B3 in the last part of the INDIRECT formula to B4, cell A1 ReviewData.xlsx will show '81'.

Read more ►

How to Insert a Title Into a Table in Excel 2007

1. Click on the table.
2. Click 'Design.' This is a tab near the top of Microsoft Excel.
3. Select 'Table Name' in the 'Properties' group.
4. Type your title in the text box that appears.

Read more ►

How to Lock Specific Cells in Excel 2007

1. Highlight the entire sheet by clicking the 'Select All' button in the top-right corner of the sheet. It's just to the left of the A column and immediately above the 1 line.
2. Right-click anywhere on the sheet and select 'Format Cells.' The Format Cells pop-up window will open.
3. Click the 'Protection' tap at the top of the window.
4. Click the block next to 'Locked' to clear the check mark, and click 'OK' to close the window.
5. Click anywhere on the sheet to remove the highlighting. Select all the cells you want to lock. You can drag to select cells that are connected. To select individual cells, you can press the 'Ctrl' key as you click them one by one.
6. Right-click any of the highlighted cells, and select 'Format Cells.' Click the Protection tab and click the box next to 'Locked' to check it. Click 'OK' to exit the window.
7. Click the 'Review' menu at the top of the window. Click the 'Protect Sheet' icon on the right side of the menu options. Make sure the box next to 'Protect worksheet and contents of locked cells' is checked. You can also enter a password so other users cannot unlock the cells. Click 'OK' to finish.

Read more ►