January 2011 - Excel

Browse » Home » Archives for January 2011

Friday, January 28, 2011

How to Number in Order With Excel 2007

1. Launch Excel to open a blank worksheet or load an existing worksheet.
2. Click on an empty cell where you want the number series to begin.
3. Enter the starting number you want, such as '1.'
4. Click on an adjacent cell. If you want the number series to run across columns, click on the cell to the right of the one you just numbered. If you want the number series to run down, click below the first cell.
5. Type the next number in the series, for example, '2.'
6. Select the two cells by clicking and dragging from the first cell to the second cell. Your selection should have a black outline around it.
7. Hover your mouse over the bottom right corner of the selection until you see a black cross appear. Click and drag, either down or to the right, for as many cells as you need to complete your series. Excel will automatically fill in the remaining numbers in order.

How to Disable Excel 2003 From Automatically Changing Cells to Scientific

1. Click the Windows 'Start' button. Type 'excel' in the search text box and press 'Enter.' This is the shortcut command to open the Excel software. When Excel opens, it automatically loads a default, new spreadsheet. You can use this default spreadsheet to create your book.xlt file.
2. Click the 'Format' menu item. From the menu, click 'Style.' This opens a 'Style' window where you edit the format of your cells. Check the 'Number' format.
3. Select the number format you want to use in the drop-down box. You can also customize the number format by clicking 'Modify.' Most necessary number formats are sufficient for creating a stylized spreadsheet.
4. Click 'OK' to save your changes. Click the 'File' menu item, then click 'Save As.' Select 'xlt' in the file format drop-down box. Type 'book.xlt' as the file name and click 'Save.' Click 'Yes' to confirm that you want to overwrite the existing template file.

How to Have Excel Update Upon Data Entry

1. Open the saved Excel worksheet.
2. Click the 'Formula' tab on the command ribbon.
3. Click the down arrow for the 'Calculation Options' button in the 'Calculation' group. A list appears.
4. Click 'Automatic.' A check mark appears. Excel will calculate as you type the new data.

Thursday, January 27, 2011

How to Save a CSV File in Excel 2007

1. Open in Microsoft Excel 2007 the spreadsheet you want to save in CSV format.
2. Click the 'Office' button located at the top-left corner of the program window and select 'Save As.'
3. Select in the resulting dialog box the folder where you want the file saved.
4. Enter a name for the file in the 'File name' field.
5. Click the 'Save as type' drop-down menu and select the 'CSV (Comma delimited)' option.
6. Click 'Save.'
7. Click 'OK,' followed by 'Yes,' to confirm your choice and save the selected spreadsheet in CSV format.

How to Use Text to Speech in Excel

Adding the Text-to-Speech Button
1. Click the 'Down' arrow to the right of the 'Quick Access' toolbar in the top left of your Excel window, right next to the 'Office' button. Click 'More Commands.'
2. Select 'All Commands' from the 'Choose Commands From' drop-down list.
3. Select 'Speak Cells' in the list box below the drop-down. Click 'Add>>' to add the command to the 'Quick Launch' toolbar.
4. Click 'OK' to finish adding the command.
Using the Command
5. Open the worksheet containing the data you want Excel to read.
6. Select the range of data you want Excel to read to you by left-clicking the top left cell of the data. Drag the mouse to the bottom right cell.
7. Click the 'Speak Text' button you added to the 'Quick Launch' toolbar to hear Excel speak the contents of your selected data.

How to Make an Interactive Chart

Create the Chart
1. Open the saved Excel worksheet.
2. Click and drag the cells for the chart.
3. Click the “Insert” tab on the command ribbon.
4. Click the lower-right arrow for the “Charts” dialog box launcher. The “Insert Chart” dialog box opens with a gallery of chart types.
5. Click the preferred chart style in the left pane. Examples include “Column”, “Pie”, and “Area.”
6. Click the preferred chart sample in the right pane.
7. Click “OK,” then the embedded chart appears on the worksheet with the source data.
8. Click in the chart area to activate the “Chart Tools” ribbon. Customize the chart with the commands in the “Design”, “Layout” and “Format” tabs. The “Design” tab contains “Chart Layouts” and “Chart Styles.” The “Layout” tab contains “Labels” for the chart area. The “Format” tab contains “Shape Styles” with outlines and effects.
Make the Chart Interactive
9. Click the “Review” tab on the command ribbon.
10. Click the “Share Workbook” button or the “Protect and Share Workbook” button in the “Changes” group, then a dialog box opens.
11. Click to check the check box that allows other users to edit this Excel chart.
12. Click “OK.”
13. Save this Excel file. Collaborators can update the worksheet values.

How to Use Page Setup in Microsoft Excel 2003

1. Access the page setup menu. To access the page setup menu scroll to the “File” tab on the command bar and select “Page Setup.”
2. Set the “Page” tab preferences. You can set the printing options to either portrait or landscape under this tab, which will print the sheet either vertically or horizontally. You can also set the scaling so the sheet will shrink to print on a single page under the “Scaling” section. Finally, you can set the paper medium and the print quality using the corresponding drop-down menus.
3. Set the “Margins” tab preferences. Under this tab, you can specify the width of the top, left, right, bottom and header and footer margins by typing in the desired margin widths into their corresponding boxes. You can also determine whether the page prints centered, either horizontally or vertically, by checking the corresponding boxes.
4. Set the “Header/Footer” tab preferences. Under this tab, you can type in the desired header and footer text. To add images and links to the header and footer, click on the “Custom Header” or the “Custom Footer” boxes and add the desired images and links.
5. Set the “Sheet” tab preferences. Under this tab, you can determine the sheet preferences for printing. You can set the print area, rows to repeat at the top and columns to repeat to the left by typing in the desired row and column information into the corresponding boxes. You can also specify the print quality, whether or not the printed sheet contains gridlines, is black and white or color and whether or not it includes any comments by checking the corresponding boxes. Finally, you can determine the page order of the printing by selecting the corresponding radial buttons.
6. Implement changes. To implement the page setup changes, click on the “OK” button.

How to Calculate Coloured Cells in Excel 2003

1. Access Visual Basic Editor by clicking the 'Tools' button on the top of your screen, navigating to the 'Macro' option and then clicking on 'Visual Basic Editor.'
2. Copy and paste the following Visual Basic programming module into Visual Basic Editor, and then save it:Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)Dim rCell As RangeDim lCol As LongDim vResultlCol = rColor.Interior.ColorIndexIf SUM = True ThenFor Each rCell In rRangeIf rCell.Interior.ColorIndex = lCol ThenvResult = WorksheetFunction.SUM(rCell,vResult)End IfNext rCellElseFor Each rCell In rRangeIf rCell.Interior.ColorIndex = lCol ThenvResult = 1 vResultEnd IfNext rCellEnd IfColorFunction = vResultEnd Function
3. Utilize the user-created function ColorFunction to calculate the number of coloured cells. The syntax for this function is as follows:ColorFunction($A$1,$B$1:$B$2,FALSE)wherein $A$1 represents the cell you want to compare to, $B$1 and $B$2 represent a range of cells along a single column and TRUE tells the function to count.Given that you have a purple cell, B1, and you want to calculate the number of purple cells between rows C1 and C12, you would use the function as follows:ColorFunction(B1, C1:C12, FALSE)

Wednesday, January 26, 2011

How to Draw a Hexagon on Excel

1. Open the Excel 2010 spreadsheet where you want to add your hexagon.
2. Click the 'Insert' tab at the top of the screen. Then click the 'Shapes' button under the 'Illustrations' heading. A standard hexagon is the ninth shape listed under the 'Basic Shapes' heading in the menu that appears. Click the hexagon icon.
3. Click anywhere on the spreadsheet and hold down the mouse button. Drag your mouse down and to the right. You will see the hexagon appear on your screen and grow as you move your mouse. Continue to drag your mouse until the hexagon is as large as you desire, then release the mouse button to create the hexagon.
4. Click anywhere on the hexagon to select it. Then click and hold the mouse button to move the shape across the screen. Once the hexagon is in your desired position, release the button.
5. Click the 'Format' tab at the top of the screen to gain access to a host of tools for customizing the look of your hexagon. The 'Shape Fill,' 'Shape Outline' and 'Shape Effects' buttons will change the hexagon's main coloring. The two values in the 'Size' area of the ribbon will let you make small changes to the size of the hexagon. Finally, the 'Bring Forward' and 'Send Backward' buttons will adjust the position of the hexagon in relation to other shapes on the spreadsheet.
6. Right-click on the hexagon and choose 'Edit Text' to add some text to the middle of your hexagon. If you highlight the text you have written, a small box will appear where you can adjust the color, font and alignment of the text.

How to Autofit Columns in Excel 2007

Mouse Method
1. Click on the Excel spreadsheet column that you want to autofit by selecting the letter at the top of the column. If you want to select multiple columns, hold the mouse button down and drag the mouse to the last column, then release it. If you need to select nonsequential columns, select the first column, then hold 'Ctrl' and select additional columns.
2. Move your mouse to the right edge of one of the selected columns. Your mouse still has to be above the columns themselves, up where the column letters are. The mouse pointer changes to a line with two arrows coming off of it in opposite directions.
3. Double-click the mouse button to autofit all of the selected columns according to the largest text in the column.
Menu Method
4. Select the cell that you want the column to autofit to.
5. Click on the 'Home' tab at the top of the screen. Locate the 'Cells' area and select the 'Format' button found there.
6. Choose 'AutoFit Selection' from the drop-down box. The width of the entire column changes based on the text in the cell you selected.

How to Create a List Box in Excel 2003

1. Launch Excel 2003 and access the 'Forms' toolbar. Click the 'View' menu, highlight the 'Toolbars' option and select the 'Forms' listing.
2. Type the responses that you want to appear in the list box into individual, sequential cells. For example, type 'Spring,' 'Summer,' 'Fall' and 'Winter' into separate cells 'A:1' though 'A:4.' Put this information on a separate worksheet within the workbook if you do not want users to see them outside of the list box.
3. Click the 'List Box' button on the Forms toolbar. Click once in the area of the worksheet to which you want to add the list box. Drag to a desired size while holding down the mouse button. Right-click once on the list box and select the 'Format' option, which will open a new window.
4. Click the 'Control' tab on the 'Format Control' window. Click once in the 'Input Range' field. Highlight the responses you entered in Step 2. Click the 'OK' button to complete the process of adding a list box in Excel 2003.

Tuesday, January 25, 2011

How to Create a Clustered Stacked Chart in Excel

1. Start Excel, with the data you want to graph in a set of adjacent cells. For example, if you're comparing production yields of four different types of component over eight calendar quarters, you'd have a grid that had the four types of components listed in cells A2 through A5, and the eight calendar quarters listed from cells B1 through I1. The numerical values of what components were made in which quarter would be entered in cells C2 through I5.
2. Insert two blank rows between each row of data in the table in step one.
3. Select cells F2 through I2. Right-click on the range of selected cells and select 'insert.' When prompted for what to insert, insert cells and select 'Shift contents down.' This will shift the last four data values in each row down by one row.
4. Select cells A1 through I13, and then click on the 'Insert' tab. In the charts area of the tab, select 'Column,' and from the chart types, select 'Stacked Column' from the 2D chart types. A chart will appear that has each of the columns of data displayed as a stacked column chart, but with a gap between columns.
5. Click on the chart that's created -- this will select it, and make the Chart Tools tabs appear. Right-click on one column of the chart; a 'Format Data Series' dialog box will open up, with options on the left pane and controls on the right.
6. Select 'Series Options' on the left pane, and on the right pane, slide the 'Gap Width' slider all the way to the left. This will eliminate the gaps between your columns. Click on the 'Close' button when you're done.

How to Use MS Query in Excel

1. Open a new document in Excel. Go to the 'Data' tab and click on 'From Other Sources.' Select the option 'From Microsoft Query.'
2. Double-click on the option 'New Data Source' in the Databases tab. Type a name you would like to give the data source. Select a driver for the type of database that you are using for the data source. Click on the 'Connect' button.
3. Provide the necessary information when prompted, and click 'OK.' This will depend on the type of driver that was selected. For example, if your data source is an Excel file, you will be asked to select a workbook.
4. Click on the empty field next to the 'Create New Data Source' dialog box if you have a table in your database that you would like to display automatically in the Query Wizard. Click on the tables you would like to display in the Query Wizard.
5. Check off the box labeled 'Save my user ID and password in the data source definition' if you didn't type in your user ID and password when using the data source. The new data source will now appear in the list under 'Choose Data Source.'
6. Go to the Data tab and click on 'Refresh All' under 'Connections' whenever you need to refresh the data in Excel from the original source.

How to Calculate Coefficient of Variation in Microsoft Excel 2007

1. Open your workbook in the Excel 2007 application.
2. Determine the cell addresses of your data array. For example, if your data is contained in the first 20 rows of column A, your cell addresses are A1:A20.
3. Click a blank cell and type the following formula: =(STDEV(A1:A20)/AVERAGE(A1:A20))Substitute the address of your data array in place of A1:A20 in the above example.
4. Press 'Enter.' Excel divides the standard deviation of the array by the average value of the array. The result of this calculation is the array's coefficient of variation, which is displayed in the cell.

How to Remove Duplicate Words in Excel

1. Open the worksheet.
2. Click the 'Home' tab on the command ribbon.
3. Click the 'Find Select' down arrow button in the 'Editing' group.
4. Click the 'Replace' command. A 'Find and Replace' dialog box will appear.
5. Click the 'Replace' tab. This tab includes two text boxes: 'Find what' and 'Replace with.'
6. Enter the word in the 'Find what' text box.
7. Leave a blank in the 'Replace with' box.
8. Click the 'Find All' button. A list of the repeated word(s) and cell references will appear.
9. Click on the row that contains the repeated word.
10. Click the 'Replace' button. The repeated word will disappear from that row. Click on other rows and the 'Replace' button to remove this word from other parts of the worksheet.

Monday, January 24, 2011

How to Hide Empty Data in Excel Graphs

Line and Scatter Graphs
1. Open the Excel 2010 file that contains the graph you need to alter.
2. Right-click in the graph itself and choose 'Select Data' from the pop-up menu. Once the 'Select Data Source' window appears, click the 'Hidden and Empty Cells' button.
3. Click the radio button next to 'Connect data points with line.' Click 'OK' to close the small window, and then 'OK' again to go back to your graph. The empty cells now get skipped and your line continues uninterrupted to the next data point.
Column, Bar and Area Charts
4. Open the Excel file that holds the chart you want to alter.
5. Right-click on the chart and choose 'Select Data' from the menu that pops-up. Click the 'Switch Row/Column' button to send the X-axis labels to the left side of the screen.
6. Select any label that has a blank entry and click the 'Remove' button. Continue until you have deleted all the labels with blank data entries. Click the 'Switch Row/Column' button again to send the labels back to the X-axis.
7. Click 'OK' to close the window. Your chart will now have the offending results removed.

How to Add Trendlines

1. Open the workbook in Excel, locate the spreadsheet containing the chart and click on it.
2. Select 'Chart' and then choose 'Add Trendline' from the toolbar menu.
3. Go to the Type tab and under 'Based on Series,' highlight the series that you want to add a trendline to.
4.
Go to the 'Type/Regression' section and select from either 'Linear,' 'Logarithmic,' 'Polynomial,' 'Power,' 'Exponential' or 'Moving Average.' (NOTE: If you choose 'Polynomial,' you will also be able to pick the independent variable's power. If you choose 'Moving Average,' you will also be able to choose the period amounts.)
5.
Go to the Options tab and, if desired, type in a more descriptive trendline name in the 'Custom' text box; otherwise, select the option for the default suggested name located next to 'Automatic.' Click 'OK' when finished.

How to Make a Scatter Plot in Excel 2007

1. Launch Excel 2007.
2. Create two columns of data, one for each variable, in an Excel spread sheet. These columns should be made up of a variable name as the header in the top row with the numerical data for the variable listed down the column below the header. It does not matter which order you list your variable data.
3. Highlight the two columns by dragging you mouse down from the header over all of the numerical data.
4. Click on the 'Insert' tab in the top menu bar, and select 'Scatter' from the Charts options.
5. Select the first chart style type from the drop down menu. Your scatter plot will now appear in Excel.
6. Select the 'Legend' with your mouse and click the 'Del' key on your keyboard to remove it from the graph.
7. Click on the 'Layout' tab in the top menu bar, and click 'Axis Titles' from the Labels option. Enter the variable names for your scatter plot. Keep in mind that Excel has placed the variable that was in the left column of the spread sheet on the x-axis and the second variable on the y-axis.
8. Change the scatter plot title by clicking on the current title in the graph, then typing in a new title in the text box above the plot and pressing the 'Enter' button on your keyboard.

Sunday, January 23, 2011

How to Setup the VLOOKUP Function in Excel

Determine the Value You Want to Look Up, Where to Put the Result and the Data Table
1. Put the value of the item you want to look up in a cell. For example, enter 'Product 1' in cell A1. This is the value you will look up and match to a value in the first column of your table array.
2. Determine where you want to place the result of the VLOOKUP. For example, you can look up the value 'Product 1' in cell A1 and place the result of the VLOOKUP (the color of the product) in cell A2. In this manner, you will have the name of the product next to the color of the product.
3. Select the location for your data set. For example: set the data set will be stored in D1:E5.
Create a Statement to Retrieve the Desired Value
4. Set the 'lookup_value' from the VLOOKUP statement. For example: the lookup_value of 'Product 1' is cell A1.
5. Set the 'table_array' from the VLOOKUP statement. For example: the table_array is the location of the data set where you will find the color of your products in cells D1:E5. Column D (the first column) will state all of the different products (Product 1, Product 2, Product 3, Product 4, Product 5) and Column E (the second column) will state all of the different colors of each product (Blue, Green, White, Red, Yellow).
6. Set the 'col_index_num' from the VLOOKUP statement. For example: the col_index_num is the number of the column in your table_array that contains the value you want to look up. Your product number is in column 1 and your color is in column 2. The column you want to retrieve from is column 2; therefore, your col_index_num is 2.
7. Set the '[range_lookup]' from the VLOOKUP statement. This is where you determine if you want an exact match or the closest match to the value you state. For example, if you want an exact match from Product 1 in column 1 to return the exact result of blue from column 2, you would set the [range_lookup] as FALSE, otherwise state it as TRUE.
8. Complete the VLOOKUP statement to retrieve the desired value in the cell you want to place the result.Example: in cell A2, input =VLOOKUP(A1, D1:E5,2,FALSE) to complete your VLOOKUP. A1 will contain 'Product 1' and A2 will 'Blue' as the result.

How to Add a Background Color or Pattern to Excel Cells

1. Start Microsoft Excel and open the file you want to change.
2. Select the cells in which you want to add a background color.
3. Open the Format menu and select Cells.
4. In the Format Cells dialog box, select the Patterns tab.
5. Click the color you want to use as a background color.
6. Click on the arrow head to open the Patterns menu.
7. Click the pattern you want to use as a background.
8. Click the color you want the pattern to use.
9. Select OK to accept the changes.

How to Unblock an Older Version of Microsoft in the Registry

1. Start Microsoft Word and then click the 'File' tab.
2. Click 'Options' under 'Help.'
3. Click 'Trust Center' in the left panel of the Word Options screen.
4. Click 'Trust Center Settings' on the right of the Word Options screen.
5. Click 'File Block Settings' in the left panel of the Trust Center screen.
6. Remove the check in the 'Open' box next to the type of file you want to unblock and open. These items are listed under 'File Type.' Repeat this step for all of the file types you would like to unblock and open.
7. Remove the check in the 'Save' box next to the type of file you would like to unblock and save. These items are listed under 'File Type.' Repeat this step for all of the file types you would like to unblock and save.
8. Click 'OK' twice to exit the Trust Center and Word Options screens.

Saturday, January 22, 2011

How to Add a Line to an Excel Worksheet

Adding a Row
1. Open your Excel worksheet and decide where you need to insert a new row. Select the row immediately below the desired location by clicking on the number in the far left of the row. The entire row should now appear highlighted.
2. At the top of the screen, locate the menu item entitled 'Insert' and click on it.
3. In the dropdown menu below the 'Insert' menu item, click on 'Rows.' A new row should now be highlighted above the previously highlighted row.

How to Subtract a Percentage in Microsoft Excel

1. Enter the amount you want to subtract a percentage from in cell A1. For example, if you wanted to subtract 20 percent of $80, you would enter '80' in cell A1.
2. Enter the percentage you want to subtract in cell A2. For example, if you wanted to subtract 20 percent of $80, you would enter '20' in cell A2.
3. Enter '=A1*(1-A2/100)' in cell A3 to have Excel calculate the find value. For example, when you enter the formula with 80 in A1 and 20 in A2, you will get 64, value of 80 minus 20 percent of 80.

How to Format UPC in Excel

1. Open Microsoft Excel and load your spreadsheet.
2. Click on the triangle icon at the place where the row and column numbers converge. This icon selects all the cells in your worksheet for quick formatting. To highlight selected cells, hold Ctrl and click on each cell individually.
3. Click on the 'Home' tab near the top left corner of the spreadsheet.
4. Click the 'Dialog Box Launcher' icon next to the 'Number' group. This icon is represented by a small arrow.
5. Click 'Text' from within the 'Category' box. This reformats the cells to display numbers and letters exactly as inputted, without chopping off extra UPC code zeros.
6. Click 'OK' to save your changes. Your Excel cells are now properly formatted for UPCs.

How to Find the End of an Excel Spreadsheet

Excel 2003
1. Select 'File' from the menu, and choose 'Open' from the submenu that appears. Navigate to the folder that contains the file you wish to open. Click on the file and click 'Open.'
2. Click on any cell in the worksheet.
3. Press the 'End' key on the keyboard, followed by the 'Home' key.
Excel 2007 and 2010
4. Click the 'Microsoft Office' button in the upper-left corner of the screen. Double-click on the name of the file you wish to open from the menu that appears. If your file is not in the list, click 'Open' and navigate to the folder that contains the file. Click on the file and click 'Open.'
5. Click on any cell in the worksheet.
6. Hold down the 'Ctrl' key on the keyboard and tap the 'End' key.

Friday, January 21, 2011

How to Add or Delete a Worksheet in Excel 2003

1. Open Microsoft Excel on your computer. Find the file that you want to change by clicking 'File' on the toolbar, selecting 'Open' and browsing for the correct file name.
2. Go to the toolbar and click on 'Insert' and then 'Worksheet' if you would like to add a worksheet.
3. Flip between worksheets by clicking on the corresponding numbered sheet at the bottom of your screen.
4. Rename the worksheet. To organize your worksheets, you can add descriptive names to each one. Just left-click on the tab of the sheet that you want to change to reveal a drop-down menu. Click 'Rename' on the menu and a cursor will appear where you can type the new name.
5. Delete a worksheet. If you no longer need a worksheet in your workbook, you can delete it. Just left-click on the numbered tab to reveal a drop-down menu. Click 'Delete' on this menu. A pop-up will appear asking if you want to delete the file. If you are sure, click 'OK' and the worksheet will be gone. If you are not sure, click 'Cancel.'
6. Save your work after making changes.

How Can I Get What I Print to Fit on the Page?

Microsoft Word
1. Select your entire document by highlighting the document or going to 'Edit,' 'Select' and 'Select All.'
2. Change the font size by a point or two. If you typed your document in 12 point, try changing it to 10 or 11.
3. Try different fonts if your paper is only over one page by a line or two.
4. Adjust your margins. One inch margins on the sides, top and bottom are standard on most documents. If you change them to .75 around the paper, that will help condense the document.
5. Go to 'Print Preview' and use the 'Shrink to Fit' option in Word if you don't want to make the changes manually. 'Shrink to Fit' changes the font size and margins to make the document fit on one page. In Microsoft Word 2007 and 2010, go to 'Print,' 'Print Preview' and then 'Shrink to Fit' under the 'Preview' option.
6. Print multiple pages on one sheet by going to 'File,' 'Print' and 'Zoom.' Under the 'Pages per sheet' box on the 'Zoom' page, choose the number of pages you want to print on one sheet. Microsoft Word shrinks the pages to fit.
Excel
7. Click 'Page Layout' from the menu options at the top of your Excel spreadsheet.
8. Change the number in the 'Height' box in the 'Scale to Fit' group to one page.
9. Switch the number in the 'Width' box in the 'Scale to Fit' group to one page.
10. Check the number in the 'Scale' box in the 'Scale to Fit' box. This number represents the percentage of the original size of your document that will print. Excel shrinks your spreadsheet in order to make it fit on one page. If the scale percentage is fairly low, the spreadsheet may be difficult to read.
11. Change your page orientation to 'landscape' rather than 'portrait' if you have a number of columns, but not as many rows. You can change the page orientation under 'Page Setup,' 'Orientation' and then 'Landscape.'

How to Draw a Line in Excel 2007

Straight Lines
1. Launch Excel 2007. Open an existing document if you do not need to start with a blank worksheet.
2. Click 'Insert' on the ribbon and select 'Shapes.'
3. Click on a line style from the 'Lines' section of the drop-down menu. A thin, black cross will appear on your worksheet.
4. Click and drag the line down or across to create the line. Hold down 'Shift' while dragging if you want a line with exactly a zero-, 45- or 90-degree angle. The line will appear with a circle on each end.
5. Click a circle on the end of the line and drag it out to extend the line. Drag the circle up or down to change the slope of the line.
6. Click on the line and select a different color or thickness from the Shape Styles menu to change the look of the line.
Curved Lines
7. Click the 'Insert' tab on the ribbon and select 'Shapes.'
8. Select a curved line style from the 'Lines' group in the menu. You can select a curve, a free-form shape or a scribble.
9. Click and drag the thin, black cross around the worksheet to create a curved or free-form line. If you select a curve or a free-form shape, you will need to click each time you want to define a peak and valley on your curve or bends in your free-form shape. If you select a scribble, you can simply let go of the mouse button once you are done drawing the line.
10. Double-click when you are done defining the line. Excel will outline your shape with a box with points, allowing you to further manipulate other aspects of the shape.

How to Make a Compound Bar Graph

1. Collect the data you wish to express as a compound bar graph. For example, data collected from an analysis of crime rates in Detroit, Boston and Chicago in 2008, 2009 and 2010 would be ideal for producing this kind of graph, as the data is quantifiable and cannot be expressed as a simple bar chart.
2. Present your data in a table. This makes the data easily accessible for drawing your chart. In this example, you would assign a column to each of the three cities and then assign a horizontal row for each of the three years, then insert the relevant data into each corresponding cell.
3. Draw your graph's X and Y axis. Along the Y axis--the vertical axis--write a scale of values that will illustrate your results. For example, if your results are all below 10 then a scale of one to 10 will suffice; if they are up to 1,000 it may be easier to go up in hundreds. Along the X--or horizontal--axis, mark your data parameters with a main heading and then several subheadings. In the example from Step 1, the names of the cities would be the main headings and the years would be the subheadings.
4. Add your data to the graph. Continuing the example, if Detroit 2008 is the first piece of data on the graph, plot this information from the table onto the chart by drawing a solid bar. The bar's width will be the width of the subheading and its height will correspond to value of the data. Repeat this for each of the subheadings for Detroit, then move onto Boston and Chicago until all the data from the table is plotted onto the chart.

How to Clear Excel 2003 Auto Recover

1. Launch Excel 2003, then click 'Tools' and 'Options.' The Options window opens.
2. Click the 'Save' tab. Under 'Settings,' click on the box next to 'AutoRecover file location.' Delete the location in the box to clear it.
3. Type a new location. If you don't do this, the AutoRecover tool continues to save documents to the destination you just cleared.
4. Turn off AutoRecover completely by clicking on the check box next to 'Disable AutoRecover,' found under 'Workbook Options.'

How to Balance Your Checkbook or Bank Account Using an Excel

1. Create a new worksheet in your spreadsheet software. Save the file in the folder of your choice with a name that includes the month and the account reconciling.
2. Type the label 'Bank Balance' in the first row of the first column. Next type 'Register Balance' in the second row of the first column. Leave the next two rows blank. Type the balance shown on your bank statement in row one of column two. Enter the balance shown in your check register in row two of column two.
3. Go to the fifth row to enter labels for your reconciliation columns. Use the first column for 'Dates.' Label column two 'Type.' The label for column three is 'Bank Credits' and the fourth column is 'Bank Debits.' Label column five 'Register Credits' and use the label 'Register Debits' for column six.
4. Enter all debits shown on your bank statement not recorded in your check register. List the date for each debit in column one under 'Dates.' Input the transaction type: check, bank fees or ATM withdrawal in the 'Type' column. Type the amounts in the column labeled 'Bank Debits.'
5. Total all 'Bank Debits' in the first empty row after all the entries made in this column. Place the cursor in column three of row two and subtract the 'Bank Debits' total from the register total.
6. Enter all deposits shown on the bank statement not in the check register. Input this data in the 'Date,' 'Type' and 'Bank Credits' column. Total the 'Bank Credits' column below the last entry that you made. Use column four of row two to add the 'Bank Credits' total to the revised register balance in row two column three.
7. Enter any deposits made that do not appear on the bank statement. Fill in the 'Date,' 'Type' and 'Register Credit' columns. Total this column after the last entry and use column three of row one to add this total to the statement balance.
8. List all outstanding checks, ATM withdrawals in the register, but not shown on the bank statement. Fill in the 'Date' and 'Type.' Put the amounts for each transaction under the 'Register Debits' column and total this column.
9. Use column four of row one to subtract the totaled 'Register Debits' from the revised bank statement balance in column three of row one. The result will equal the register balance unless you have overlooked a credit or debit.

How to Have Data Flow Between Spreadsheets

1. Open the source workbook, containing the cell you want to reference.
2. Open the destination workbook, where you want to create a link.
3. View the workbooks side by side. In Excel 2003, from the menu, choose 'Windows,' then 'Arrange.' Select 'Tiled.' Click 'OK.' In Excel 2007, under the ribbon's 'View' tab, in the 'Window' group, click 'Arrange All.' Select 'Tiled.'
4. Link cells. In the destination workbook, click a cell and type an equal sign ('='). If you are building a formula, type the beginning of the formula, up to the point where you want to insert a value from the other worksheet.
5. In the source workbook, click on the cell containing the value you would like to use. The focus will automatically return to the destination workbook. If you started a formula, complete it, ending with a close parenthesis. Click 'OK.'
6. Set up automatic updates.In Excel 2003, from the menu, select 'Tools,' then 'Options.' On the 'Calculation' tab, under 'Workbook options,' select 'Update remote references.' Click 'OK.'In Excel 2007, under the ribbon's 'Formulas' tab, in the 'Calculation' group, click 'Calculation Options.' Select 'Automatic.'
7. Save and close both worksheets. Later, whenever you change information in the source workbook, linked cells in the target workbook will automatically be updated, even if the other workbook is closed.

How to Graph UCL and LCL in Excel 2007

1. Calculate the mean of each group by typing '=AVERAGE(A1:D1)' (without quote marks) in cell F1. Press 'Enter,' then guide the cursor over the right hand corner of cell F1 until it changes to a cross-hair. Click and drag down to cell F5 and release to auto-fill the remaining means.
2. Calculate the mean of means by typing '=AVERAGE(F1:F5)' (without quote marks) in cell F7 and pressing 'Enter.'
3. Calculate the standard deviation of all observations by typing '=STDEV(A1:D5)' (without quote marks) in cell F8 and pressing 'Enter.'
4. Calculate the Upper Control Limit (UCL), which is the mean of means plus three times the standard deviation. In this example, type '=F7 3*F8' (without quote marks) in cell F9 and press 'Enter.'
5. Calculate the Lower Control Limit (LCL), which is the mean of means minus three times the standard deviation. In this example, type '=F7 3*F8' (without quote marks) in cell F10 and press 'Enter.'
6. Copy the mean of means in cell F7 and paste its value into cells A6 to D6. Repeat this step with UCL and LCL by pasting them into cell A7 to D7 and A8 to D8, respectively. This will ensure the final graph includes the mean of means, the UCL and the LCL.
7. Graph your observations. In this example, highlight cells A1 to D8, select the 'Insert' tab, then the 'Line' button, then select the basic 2-D line from the sub-menu. Right-click the graph, select the 'Select Data...' option, and then select the 'Switch Row/Column' button in the 'Select Data Source' dialog box before clicking 'OK'.

Thursday, January 20, 2011

How to Make Name Badge Labels From an Excel List

Excel 2003
1. Open Microsoft Word.
2. Click on 'Letters and Mailings' in the Tools menu, then click 'Mail Merge Wizard.'
3. Click on 'Labels' in Step 1 of the Mail Merge wizard, and then click 'Label options' in Step 2 of the Mail Merge wizard. Select the size of your name badge labels from the list.
4. Click 'next,' then click 'Use an existing list,' and click the 'Browse' button.
5. Find your Excel file, then click 'Open.'
6. Find your list in the Select Table box and click it. Check the 'First row of data contains column headers' box, then click 'OK.'
7. Look at the word labels to the left (in the Mail Merge Recipients dialog box) and then click any column labels that match. For example, you might see a match for 'name,' so click on it. Your basic labels have been created.
Excel 2007
8. Open Microsoft Word. A blank document will automatically open.
9. Click the 'Mailings' tab and then click the 'Start Mail Merge' button.
10. Click 'Step by Step Mail Merge Wizard.'
11. Click on the 'Labels' radio button, then press 'Next: Starting Document.'
12. Click on the 'Start from existing document' radio button, locate your Excel document, then press 'Next.' Follow the instructions to choose label size and printer, pressing 'Next' each time to complete the process.

How to Create XML Data From Excel 2003

1. Click the 'Start' or Windows button in the lower left corner of your screen, then select 'Programs' or 'All Programs.'
2. Scroll through your list of installed applications and select 'Microsoft Office,' then click 'Microsoft Excel 2003.'
3. Click 'File' at the top of the window and select 'New' to generate a new spreadsheet, or click 'Open' and navigate to a previously created Excel file.
4. Click 'File' at the top of the screen then select the 'Open' option to navigate to your XML source file and open it.
5. Select the 'Use XML Source task pane' option and click the 'OK' button.
6. Add XML elements from the pane on the right side of the screen to generate an XML map on your spreadsheet.
7. Make any edits or additions to your spreadsheet file.
8. Click 'File' at the top of the screen, then select the 'Save As' option.
9. Select the drop down menu next to the 'Save as type' wording and choose the 'XML Data' option.
10. Type a name for your file into the 'File name' text window, then select a location for your Excel XML Data file and click the 'Save' button.

Wednesday, January 19, 2011

How to Convert Excel Data to Charts

1. Open the Excel file that contains the data that you want to make into a chart.
2. Add column headers to your data, if applicable. These headers sit on the top row of the data and describe the information for the column beneath them. Excel uses these headers as labels when it create the charts, so you have a better chance of creating a good-looking chart if you add the headers now. If you don't have an empty row above the data, right-click on the row number at the top of your data and choose 'Insert.' Then add the heading for the information.
3. Select any cell within the data field. Excel automatically expands the selection to all adjacent cells when you create the chart. If you only want to chart a specific range of cells in a larger data field, select the top-left cell of your desired data, then hold down 'Shift' while you select the bottom-right cell of the data.
4. Click on the 'Insert' tab at the top of the Excel window. Locate the Charts area and determine the type of chart you want to create. Column, line, pie, bar, area and scatter charts all have their own button, or you can click the arrow next to the word 'Charts' to open up a window that contains every chart Excel can make. If you can't decide which type to create, just pick one. Excel lets you change the chart type on the fly.
5. Click anywhere on the chart and select the 'Design' tab at the top of the screen. Use the 'Change Chart Type' button on the far left side of the ribbon to choose a new chart type. A window containing every chart type appears, and you can select another type. Continue to experiment until you find a type that shows your data as you desire.
6. Right-click on any specific area of the chart, such as the legend, axis titles or the data labels, to bring up a list of options for that area. You can change the font, change the data area and add or remove gridlines. You can also make more intricate changes by choosing the 'Format' option from the pop-up menu. If you don't like your changes, you can choose 'Reset to Match Style,' and the area settings are reset to match the overall style of the chart.

How to Tell When an Excel Workbook Was Last Updated

1.
Open Microsoft Excel by going to 'Start,' 'Programs,' 'Microsoft Office' and 'Microsoft Excel' or 'Start' and then 'Microsoft Excel.'
2.
Open an existing spreadsheet by clicking on the Office icon and navigating to the file you want to open or go to 'File' and 'Open.'
3.
Click on the Office icon and go to 'Prepare' then 'Properties.'
4.
Click on 'Document Properties' then 'Advanced Properties.'
5.
Look at 'Modified' in the 'Properties' dialog box. The 'Modified' field is the date that the workbook was last updated.

How Do I Group Worksheets in Excel 2007?

1. Open the workbook containing the worksheets you want to group.
2. Group all of the worksheets in a workbook. Right-click any tab in the workbook and click 'Select All.'
3. Group adjoining worksheets. Press and hold the 'Shift' key. Click on the tab for the first worksheet in the selection you want to group and then click on the tab for the last worksheet you want to include in the group.
4. Group worksheets that are not adjoining. Press and hold the 'Ctrl' key. Click on the tab of each worksheet you want to include in the group.
5. Ungroup worksheets by right-clicking any tab in the group. Select 'Ungroup Sheets.'

How to Select Multiple Cells in Microsoft Excel 2003

1. Highlight the cells. Use your mouse cursor and left-click on the first cell and then hold the button in as you drag the cursor over the cells you wish to select. Once you are done selecting cells you can release the mouse button and the cells will remain highlighted.
2. Select multiple cells using hot keys. You can also use hot keys to select multiple cells. To do this, press the “CTRL” key and hold. While pressing “CTRL,” left-click on the cells that you wish to select. They will gray out when you select them. Release the “CTRL” key and the cells that you selected will remain highlighted.
3. Use the “Go To” command to select cells. To use the “Go To” command to select cells scroll to the “Edit” tab on the command bar and select “Go To.”
4. Enter cell references to select cells. To select multiple cells you will need to enter the cell references in the “Go To” properties box. For example if you wanted to select cells A1 and B3 you would enter A1, B3. To select a span of cells in the same letter you could enter B1:B9, which would select all of the cells in that sequence.

How to Make a Checklist on Excel

1. Open Microsoft Excel. Click into the first cell in the second column, B1. Type the first item for the checklist, such as 'Bread' or a task such as 'Clean windows.'
2. Double-click the small line separating the 'B' and 'C' columns. The 'B' column will spread out to the width of the text in the B1 cell.
3. Press the 'Enter' key on the keyboard to move into the cell below, B2. Type the next checklist item.
4. Continue pressing 'Enter' and entering tasks until they are all listed. Resize the 'B' column as necessary.
5. Click into the first cell, A1. Click the 'Insert' tab at the top of the screen. Click the 'Symbol' button on the right side of the ribbon below the tab.
6. Pull down the 'Font' menu and select 'Wingdings,' included with all Excel installations. Double-click the check box icon, in the very bottom right corner. Note the box is added to the cell. Click the 'Close' button.
7. Click once on cell A1 to highlight it. Press the 'Ctrl' and 'C' keys on the keyboard.
8. Click cell A2, hold down the left mouse button and drag vertically until all cells in column 'A' that have a checklist item next to them in column 'B' are highlighted.
9. Press the 'Ctrl' and 'V' keys, which pastes a copy of the check box into all of the cells.
10. Click the 'File' tab. Click 'Save As.' Type a name for the checklist spreadsheet and click the 'Save' button.

Tuesday, January 18, 2011

How to Add Images to an Excel 2007 Document

1. Open the Microsoft Excel document. Locate the Microsoft Excel file on the hard drive and double click on the file icon to open the spreadsheet.
2. Locate the image on the hard drive and insert it into the Excel document. This is for specific images outside the Microsoft Office program. From the main menu select 'Insert' and then choose 'Picture' from the insert menu. Now locate the image on the hard drive. Select the Image and click 'OK.'
3. Resize the image as necessary to fit into the Excel document properly. Click on the box, hold the mouse button down and drag the image to its appropriate size.
4. Insert clipart into the Excel document. Clipart are the images that are contained within and designed for Microsoft Office. Select the 'Insert' tab from the main menu. Next, select 'Clip art' from the insert menu. The Clip art dialog box will appear on the right hand side of the Excel document. Double click on the appropriate clip art to insert it into the Excel document.
5. Save the Excel 2007 document to the hard drive when finished. Click on the 'Office' button in the top right-hand corner of Excel 2007. Then select 'Save' from the office menu.

How to Use Software to Merge Two or More Excel Workbooks

1. Click the 'File' tab, and select 'Options.' In the 'Excel Options' dialog box, select 'Customize Ribbon.' In the 'Choose Commands From:' drop-down menu, select 'All Commands.'
2. Select 'Compare and Merge Workbooks' in the left hand pane, and then click 'Add.' Click 'OK' to finish the installation process.
3. Click the 'Insert' tab, and then select the 'Compare and Merge Workbooks' button. Select the workbooks you wish to merge in the dialog box. (Note that the workbooks must be in the same folder before they can be merged.) Click 'OK' when done.

How to Change the Horizontal Axis Labels in Excel 2007

1. Click on the cell that has the category axis label you want to change.
2. Type the new name for the axis label.
3. Press the 'Enter' key. The new label will be updated on the chart.

How to Remove a Named Cell

1. Click 'Insert' from the top menu of Excel.
2. Click 'Name' from the Insert drop-down menu.
3. Click 'Define' from the Name submenu.
4. Click the name you wish to remove under 'Names in workbook.'
5. Click the 'Delete' button.

Monday, January 17, 2011

How to Use the Outliers Function in Excel

1. Enter the set of numbers into a range of cells in the 'A' column of an Excel worksheet. As an example, we will use a set of 20 numbers, running from cell A1 to A20.
2. Select cell B1 and enter the 'AVERAGE' function, with a range from the first number in the set to the last number in the set. In this example, cell B1 should read '=AVERAGE(A1:A20).'
3. Calculate the standard deviation of the set in cell B2 using the 'STDEV' function. The formula in cell B2 should be '=STDEV(A1:A20).' Any figures in the set of numbers that fall more than two standard deviations from the mean are to be considered outliers.
4. Find the minimum and maximum values in the set that will be considered. Enter '=B1-(2*B2)' (mean minus two standard deviations) in cell B3 for the minimum, and enter '=B1 (2*B2)' (mean plus two standard deviations) in cell B4 for the maximum.
5. Select the original set of numbers (range A1:A20). Go to the first cell in the range (A1), select 'Conditional Formatting' on the 'Home' tab, and open the 'New Formatting Rule' dialog box.
6. Select 'Use a formula to determine which cells to format' under 'Select a Rule Type.' In the 'Format values where this formula is true' box that opens, type '=OR(A1
$B$4)' and click 'Format' next to the Preview box, then select 'Bold' under 'Font Style.'
7. Click 'OK' on both of these dialog boxes to close them and apply the rule. This will cause all outliers to appear in bold on the spreadsheet.

How Do I Print Column Headings on Each Page in Excel 2003?

1. Launch Microsoft Excel, or double-click an existing Excel spreadsheet to open it.
2. Click 'File' at the top of the screen, then click 'Page Setup.'
3. Click 'Sheet' at the top of the 'Page Setup' window.
4. Click the button to the right of 'Rows to repeat at top.'
5. Click the row number at the left side of the Excel window that contains the column headings you want to print on each page, then press 'Enter' on your keyboard.
6. Click the 'Print' button on the right side of the 'Page Setup' window to print your spreadsheet with the included column headings on each page.

How to Calculate the Quartile Value in Excel

1. Open up a new Excel 2010 spreadsheet, or an existing spreadsheet that contains the numbers set you want to work with. If you opened a new spreadsheet, type the numbers of your number set into the first column of cells, with each number in its own cell.
2. Select an empty cell somewhere else on the spreadsheet, with two additional empty cells directly below it.
3. Type the following formula into the cell, without quotes: '=QUARTILE.EXC(A1:A10,1)'. Change 'A1:A10' to whatever range of cells holds your number set. Press 'Enter' and you will see the first quartile value.
4. Select the cell directly beneath your formula. Enter the exact same formula, except change the '1' at the end to a '2.' Press 'Enter' and you will get the second quartile value. Do this one more time in another cell and change the last number to a '3.' This will give you the third quartile value.

How to Add Charts to Excel 2007

1. Open a worksheet in Excel. Enter the data into your worksheet using the columns and rows. Label each column and row, and place a title at the top of your table.
2. Select all of data you want displayed in your chart. Be sure to include the row labels, column titles and the title of your chart.
3. Click the 'Insert' tab at the top of your spreadsheet, and click the type of chart you want to add to your worksheet. The chart types are located in the 'Charts' group.
4. Change the type of chart by right-clicking the chart and selecting 'Change Series Chart Type.' Experiment with the many types until you find one that suits your needs.
5. Edit the information in your chart by changing the data entered into your table.
6. Move your chart by left-clicking anywhere in the white area. Drag the chart with your mouse to the desired location.
7. Change the colors or style of the data on your chart by selecting the specific bar, line or sector. Right-click and select 'Format Data Series.' In the Format Data Series dialog box, you can change the shape, fill, border color, border style, shadow and 3-D format.
8. Change the size of your chart by clicking anywhere inside the chart. Grab any outside corner and drag it in or out.

How to Print Envelopes From Data in Excel

1. Format your information in Excel with column headers. For example, write 'name' in cell 'A1' as a column header and then list the recipient names in column A starting at cell A2. Write 'address' in cell B1 and then list the recipient addresses below that in column B.
2. Open Microsoft Word.
3. Start the Mail Merge wizard in Word 2007 by clicking on the 'Mailings' tab and clicking on the down arrow to the right of 'Mail Merge.' Click on 'Mail Merge Wizard.' In Word 2003, you can start the Mail Merge Wizard by clicking on 'Tools->Letters and Mailings->Mail Merge.'
4. Choose 'Envelopes' from the list box. A pop-up window will appear.
5. Choose the envelope size from the 'Envelope size' drop-down box. For example, choose 'Size 10' envelope. Press 'OK.'
6. Click on 'Select Recipients' from the toolbar, then choose 'Use existing list.'
7. Find the Excel file on your computer, then press 'Open.' If you have multiple sheets in your workbook, Excel may ask you to select the worksheet. If that happens, click on the worksheet and click 'OK.'
8. Click on 'Insert Merge Field' on the toolbar. This is where you tell Excel to put the information. Excel will have pulled the fields from your Excel data. For example, you may have a 'name' field (select it from the list).
9. Click on 'Finish and Merge.' Choose 'Print' from the list of options to print your envelopes.

Sunday, January 16, 2011

How to Hide Cell Contents in Excel

1. Start Microsoft Excel and open an existing workbook that contains cells you would like to hide the contents of, or start a new workbook and enter some content into a cell that you would like to hide.
2. Select the cells that contain data you would like to hide. You can select cells by clicking on them and using the SHIFT or CTRL keys on your keyboard to select additional cells.
3. Choose the 'Format' menu and click 'Cells' to open the 'Format Cells' dialog box.
4. Click on the 'Number' tab in the 'Format Cells' dialog box to display the formats that can be applied to Excel cells.
5. Select 'Custom' from the 'Category' text box so you can type your own custom formatting to hide your selected cells.
6. Type 3 semicolons (;;;) in the 'Type' text box to tell Excel that you want to hide the cells.
7. Click the 'OK' button to close the 'Format Cells' dialog box and hide the selected cells.

How to Use Cell Ranges in Formulas in Excel 2003

1. Open the Excel file you would like to edit. It will be easier to make your formulas if you already have data in your worksheet.
2. Click on the cell where you want the formula. You should see a cursor, which means that you can type in the cell.
3. Start your formula with the '=' symbol (equal). Every formula in Excel will begin with the equal sign. This is how Excel recognizes that you are creating a formula.
4. Decide what range you would like to use in the formula. Is it a row or column? There is a slightly different approach for each.
5. Here is an example of a basic formula for a column: =SUM(A12:A24). This formula will add all the numbers in cells A12 through A24. For any formula using a column, you would use the column letter followed by the number of the first cell; then use a colon; then use the column letter and cell number for the last cell.
6. Use this basic formula for a row: =SUM(A1:G1). The only difference from the column range is that the letter changes and not the number.

How to Print a Header on Each Page in Excel 2007

1. Click the 'Office' button, then select 'Print.'
2. Click 'Print Preview.'
3. Select 'Setup.' Go to the 'Header/Footer' tab.
4. Click 'Custom Header' and enter your header information. Click 'OK' twice.
5. Click 'Print.'

How to Customize Excel 2007

1. Click the Microsoft Office button, choose the 'Excel options' box that appears under your recent documents and then choose the 'Customize' menu on the left. As a shortcut, you can right-click the Quick Access Toolbar itself and choose 'Customize Quick Access Toolbar...'The window for customizing will appear. The right column lists the current icons in the Quick Access Toolbar, and the left column lists all the icons available for use.
2. Use the drop-down menu above the left column to select icons from other groupings or 'All commands,' which gives you every icon available.
3. Locate an icon you would like to add to your Quick Access Toolbar. Click on it and click the 'Add >>' button located between the columns. This will place the icon at the bottom of the list of icons in use. Repeat for each icons you would like to use.
4. Rearrange the icons in an order that is desirable to you. Click on the icon you would like to move up or down, then click the 'Up' or 'Down' arrows to move each icon accordingly.Click the 'OK' button to use your new icons.

How to Do a Statistical Analysis in Excel

1. Type a list of at least five numbers ranging from about 50 to 100 in a column of cells. These numbers represent hypothetical test scores of students. The highest possible score is 100 and the lowest is 0. You'll use Excel's statistical functions to analyze the scores.
2. Click the cell below the last score you typed, then click the 'More functions' button of the 'Formulas' tab. Click the 'Statistical' item, then click the 'Average' function, which yields what number the list as a whole tends to be gravitating toward.
3. Click the gridded button next to the top text box of the dialog box that appears. Pressing the button enables you to select the range of cells you'd like to give as input to the 'Average' function.
4. Click the cell of the top number you entered, then drag the mouse to the bottom number and release the mouse. Press 'Enter' to make Excel enter the average function with your list of test scores into the workbook. The cell in which you entered the function now displays the average of the scores.
5. Use the instructions from steps 2 through 4 to insert the 'Min' function in the cell below your test scores. This function tells you the smallest score in the list. While you can determine this number by visually scanning the list, the 'Min' function can quickly discern the smallest number from a long list of numbers.
6. Use instructions 2 through 4 to insert the 'Max' function into the worksheet. This function reveals your list's largest score. Insert the 'Stddev' function next. This function computes the average distance of all test score from the average score. Standard deviation gives you a sense of how much variation exists in the scores. For the test scores example, if Excel computes a standard deviation of '2,' this means that the average difference of each score from the figure returned by the 'Average' function is only 2 percentage points, out of a scale of 100 points.

Saturday, January 15, 2011

How to Create a 3

1. Open the Microsoft Excel 2010 file that contains the data you wish to make into a three-sided graph.
2. Click on any cell in your data table. Press 'Ctrl' and 'A' to select the entire table. If you need to graph only a small part of a larger table, click and hold on the top-left cell that you want to include, then drag the mouse to the bottom-right cell and release the button. Your table selection needs to include at least two rows or two columns to become a three-sided graph.
3. Click the 'Insert' tab at the top of the screen.
4. Locate the 'Charts' area of the ribbon. Click on the button for the type of chart you want to create. To make a three-sided chart, you will need to select either a 'Column,' 'Line' or 'Area' chart type. Once you click the button, a pop-up menu will appear with a number of additional chart choices. Locate either the '3-D Column,' '3-D Line' or '3-D Area' button and click it. Be aware that both the 'Column' and 'Area' pop-up menus include 3-D charts that aren't three-sided, so be sure you are selecting the button labeled '3-D Column' or '3-D Line.' Once you click the button, your three-sided chart will appear on the spreadsheet.

How to Scroll Past Frozen Panes in Excel

1. Open Excel and open the spreadsheet that you want to freeze panes on.
2. Select a cell that is one column to the right and one row below the demarcation point you want to freeze in view. For example, if you want to freeze columns A and B, and row 1, you would select cell C2 for this purpose. You can also select an entire row or an entire column -- just make sure it's one row or column past where you want the edge of your frozen frames to lie.
3. Click on the 'View' tab and select 'Freeze Panes' from the Window section of the Ribbon. The fly out menu will give you an option to Freeze Panes. Select that option.
4. Scroll through the spreadsheet by clicking the sliders on the bottom of the window or on the right hand size, or by scrolling through with the wheel on your mouse, if your mouse is so equipped.
5. Unfreeze panes by clicking on a cell in the frozen area, and clicking on the icon that says 'Freeze Panes' -- the top option will have changed to 'Unfreeze Panes' on the fly out menu.

How to Make Labels With Excel 2007

1. Open the Microsoft Excel 2007 application on your computer and then click on the “Microsoft Office” button. Click on the “Open” option.
2. Locate the Excel document that includes the chart to which you want to add labels and then click on the “Open” button.
3. Click on the chart area to bring up the Chart Tools with the “Design,” “Layout” and “Format” tabs. Click on the “Layout” tab and then click on the “Data Labels” option from the “Labels” group.
4. Select the type of labels you want from the list. They will appear according to the type of graph or chart you have. Enter in the text you want for each label that corresponds with the chart.
5. Click on the “Format” tab and then click on the “Format Selection” option from the “Current Selection” group. Click on the “Label Options” button to change any label settings to meet your preferences. Click on the “OK” button to save your changes.
6. Edit any text in a label you’ve created by double-clicking on the label. Press the “Delete” key on your keyboard to remove text and then enter the new text you want to save. Click anywhere outside of the label and it will be saved on the chart.

Friday, January 14, 2011

How to Turn Off Excel Sound

1. Double-click on the Microsoft Excel icon on your desktop to launch the program or click on 'Start,' select 'All Programs,' hover over 'Microsoft Office' and click on 'Microsoft Excel.'
2. Click the 'Office' button in the upper left corner of the program and then scroll down and click on 'Excel Options' in the bottom right corner of the menu. A new window will appear.
3. Click on 'Advanced' along the left side of the window.
4. Scroll down to the 'General' section and un-check 'Provide feedback with sound.'
5. Click 'OK' to save your changes.

How to Insert Drop Down Boxes in Cells in Microsoft Excel 2007

1. Open Microsoft Excel 2007.
2. Type the data or names that you want to appear in the drop-down list in an empty column. You can choose any column as long as you don't use it for your other data.
3. Click the 'Data' menu tab and click 'Data Validation' from the list of options available.
4. Click the 'Settings' tab and select 'List' from the drop-down box titled 'Allow.' In the 'Source' box, type the cell range (i.e., A1:A5) for where the list appears or click the button to the right of the box and select the cells with your mouse.
5. Click the check box to select 'In-cell dropdown.' Click 'OK' once you have finished. An arrow appears next to the cell you originally chose, allowing you to input data from a list of choices.

How to Break a Column to List in Excel

1. Open the saved Excel worksheet.
2. Click 'Insert' tab on the command ribbon.
3. Click the 'SmartArt' command. A window of graphics appears, including 'List.'
4. Click the 'List' option in the window's left pane. A display of 40 list formats appears. Examples include the basic block list with both horizontal and vertical display or the segmented process list to display a sequence of steps.
5. Click the preferred list style.
6. Click 'OK.' The list template floats over the worksheet. A 'SmartArt Tools' ribbon appears.
7. Type in the '[Text]' spaces. You can also click the template's left arrow to open a 'Type your text here' pane. As you type the text, the text copies to the list.
8. Refine the list's shape by clicking a preferred 'SmartArt Styles' button in the 'Design' tab on the 'SmartArt Tools' ribbon. To adjust the color, click the 'Format' tab on the 'SmartArt Tools' ribbon for 'Shape Styles' and 'WordArt Styles.'
9. Click and drag on the template's frame to resize and position the list in the preferred column of the worksheet.

How to Use Macros in Excel 2003

Recording a Basic Macro in Excel
1. Open Microsoft Excel.
2. Type some data in the first row. You will create a macro that makes the first row bold and then creates a new worksheet; this is simply to demonstrate what macros can do.
3. Click 'Tools,' then 'Macro' then 'Record New Macro.' Name your macro, and assign a shortcut key to it.
4. Click Row '1,' then hold the 'Ctrl' key and press 'B'. This will make that row bold.
5. Hold the 'Ctrl' key and press 'F11.' This will create a new worksheet.
6. Click the blue 'Stop' square in the 'Stop Recording' window to stop recording your macro.
7. Type something in the first row of your new worksheet.
8. Press your shortcut key assigned to the macro, or click 'Tools,' 'Macro' and then 'Macros.' Select your macro and click 'Run.'

Thursday, January 13, 2011

How to Paste Into MS Excel

Paste Data Individually
1. Open the document you want to copy from. This could be a document in another Office program, such as Word, an email or a folder on your computer.
2. Select the text you want to copy. You can select plain text or a formatted table. For instance, to copy a table you made in Word, select the rows and columns you want to paste.
3. Press 'Ctrl C' to copy the text.
4. Open Microsoft Excel.
5. Select the top left data cell in which you want to paste the text.
6. Press 'Ctrl V' to paste. Alternatively, click the 'Home' tab and click the 'Paste' button in the 'Clipboard' group. A small icon will appear next to the text you just pasted.
7. Click the icon ('Paste Options') to change the formatting of the text. Click 'Keep Source Formatting' to format the text as it was previously formatted in the original document, or select 'Match Destination Formatting' to adapt the formatting to the Excel worksheet.
Paste Data All at Once
8. Open Microsoft Excel.
9. Click the 'Home' tab. In the 'Clipboard' group, click the small arrow at the bottom to launch the Clipboard Task Pane. This will show each item that you copy, as many as 24 items at once.
10. Repeat steps 1 to 3 from the above section for as many items that you want to copy.
11. Click in the Excel document where you want to paste the data.
12. Click the 'Paste All' button on the Clipboard Task Pane. Press 'Clear All' to clear the Clipboard before copying and pasting more items.

How to Lock Excel Workbooks

1. Go to the 'Tools' menu in Excel 2003, point to 'Protection' and click 'Protect Workbook.' In Excel 2007, go to the 'Review' tab and click 'Protect Workbook.'
2.
Protect the workbook window so that it cannot be re-sized or repositioned. Select the 'Windows' check box.
3.
Protect the structure of the workbook so that it cannot be moved, deleted, hidden or renamed by selecting the 'Structure' check box.
4.
Add a password in the 'Password' box to prohibit users from removing the 'Structure' or 'Windows' protection. Click 'OK' and re-enter the password when prompted.
5.
Require a password to open and/or modify the workbook. Click the 'Office Button' in Excel 2007 or 'File' in Excel 2003 or earlier and select 'Save As.' Click 'Tools' on the 'Save As' dialog box and then select 'General Options.' Enter a password in 'Password to Open,' 'Password to Modify' or both and then click 'OK.'

How to Convert an Excel Chart to PDF

1. Open Excel 2010 and click the 'File' tab. Select 'Open.' Browse the files and locate an Excel spreadsheet. Click the spreadsheet and the 'Open' button.
2. Click the tab in the spreadsheet that contains the Excel chart. Click the 'File' tab and select 'Save As.'
3. Change the 'Save As' type to 'PDF.' Type a name for the 'Name' field. Click 'Save.'

Wednesday, January 12, 2011

How to Change the Mouse Pointer in Excel

1. Open Microsoft Excel and wait for the application to load.
2. Select the 'View' tab on the right side of the toolbar.
3. Click 'Macros' then select 'View Macros' from the drop-down list.
4. Type a name for the macro into the 'Macro name' text box then click 'Create'. The Microsoft Visual Basic editor will open in a new window.
5. Type 'Application.Cursor = ' before the text that reads 'End Sub' and after the text string that contains the word 'Sub' followed by the macro's name and a pair of parenthesis.
6. Type 'xlWait' after the equals sign to tell the macro to change the pointer to an hourglass. Type 'xlIBeam ' after the equals sign to tell the macro to change the pointer to an I-Beam. Type 'xlNorthwestArrow ' after the equals sign to tell the macro to change the pointer to the northwest-arrow. Type 'xlNormal' after the equals sign to tell the macro to change the pointer back to the default pointer.
7. Hold the 'Alt' key and press 'F11' to return to the Excel spreadsheet.
8. Click 'Macros' on the 'View' toolbar then select 'View Macros' from the drop-down list.
9. Find and click on your macro from the list then hit 'Run'.

How to Get out of Compatibility Mode on Microsoft Word

1. Run the Microsoft Word program and open the document that was created using a previous version of Word.
2. Click the 'File' tab in the upper-left corner of the program window to display a toolbar with Microsoft Word options. The toolbar will appear on the left side of your screen.
3. Click the 'Info' tab on the left side of the program window. Document information and options will appear in the center of the screen.
4. Click the Convert button to save the file in Word 2010 format.
5. Click OK when you're prompted to convert the document. Word will convert the file and save it with the Word 2007/2010 extension .DOCX. Upon conversion, Word will exit Compatibility Mode and will operate with all functions available.

How to Export a CSV File From Office 2003 to Office 2000

1. Open Microsoft Excel 2003. The program is generally accessed by clicking the Windows 'Start' button and opening the following: 'Programs' > 'Microsoft Office' > 'Microsoft Office Excel 2003.'
2. Open your CSV file in Excel 2003. Click 'File' in the menu near the top of the screen and then select 'Open.' Find the location of your CSV file and double click its icon.
3. Click the 'File' menu again, this time selecting the 'Save As' option. Give the newly created file a name and choose the location where you want it saved. Select the drop down menu labeled 'Save as type' and choose the 'Microsoft Excel 97-2003' option. Click 'Save' to complete the save process. Your CSV file is now available for use with Excel 2000 in the Office 2000 suite of products.

How to Use Conditional Formatting Rules in Excel

1. Start Microsoft Excel and open an existing spreadsheet that contains cells to which you would like to apply a conditional formatting rule. Select the cells that you would like to apply a conditional formatting rule to by clicking and dragging over the cells or using the keyboard shortcut of CTRL A to select the whole Excel spreadsheet.
2. Choose the 'Format' menu and click on 'Conditional Formatting...' to bring up the 'Conditional Formatting' text box.
3. Set the first drop-down menu to either 'Cell Value Is' or 'Formula Value Is.' Choosing 'Cell Value Is' will apply the conditional formatting rule to only cell values where choosing 'Formula Value Is' will apply the rule to only formulas.
4. Select an option from the second drop-down menu which will specify the comparison of the rule. Options such as 'between' and 'greater than' will help you specify how the rule will work.
5. Input a value into the one or two text boxes that appear after you have chosen the comparison for the rule. You can input a value or cell reference into this text box.
6. Click the 'Format' button to bring up the 'Format Cells' dialog box and set the formatting cells you wish to be applied to the cells that will meet the specifications of the conditional formatting rule you have set.
7. Use your mouse to click the 'OK' button twice, once to close the 'Format Cells' dialog box and once to close the 'Conditional Formatting' dialog box. You will now be able to look at your spreadsheet and see which cells meet the conditional formatting rule you have set.

Tuesday, January 11, 2011

How to Have Excel Read a Directory Create a Spreadsheet

1. Click the Office button's 'Options' button, and then click the 'Show developer' checkbox to reveal the 'Developer' tab. This tab holds buttons for Visual Basic macros. You'll use a VBA macro to list the files in a directory and store that list in a new spreadsheet.
2. Click the 'Developer' tab, and then click the 'Visual Basic' button to enter the Visual Basic programming environment. Double-click the 'This workbook' item in the navigation pane at screen left. This action opens a new programming window in which to enter your directory listing program.
3. Paste the following program into the programming window. This program uses the VBA function 'Dir' to read a directory. The output of that function is a single file from that directory.Option ExplicitPublic Sub ListFiles()Dim dd = Dir('c:\windows\*')MsgBox dEnd Sub
4. Click any of the program's statements, and then click the 'Run' menu's 'Run' command to run the program. Excel will display a message box indicating the name of a file in the folder 'C:\windows.'
5. Paste the following program after the 'End sub' statement of the first program. This program creates a new workbook and inserts a value in one of its cells. Your final program that lists a directory's files will use the statements in this program to insert filenames into a new workbook.Option ExplicitPublic Sub MakeNewWorkbook()Workbooks.AddactiveCell = 'Hello there'End Sub
6. Run the program as you did the previous one, and then click the 'Excel' icon in the Windows taskbar to return to Excel. Your program will add a new workbook whose current spreadsheet displays the text 'Hello there' in cell A1.
7. Use step 2's instructions to return to the VBA programming environment, and then paste the following program after the 'End Sub' statement of the previous program. This program uses the 'Dir' function to gather all files in the 'C:\windows' folder. The program stores each filename in its own row in a spreadsheet of a new workbook.Option ExplicitPublic Sub ListOneFile()Dim dd = Dir('c:\windows\*')Workbooks.AddDo Until d = ''ActiveCell = d'move downActiveCell.Offset(1).Selectd = DirLoopEnd Sub
8. Run the program as you did the previous ones, and then return to Excel using step 6's instructions. You'll see a new spreadsheet whose cells display a listing of all files in the 'C:\windows' directory.

Subscribe to: Posts (Atom)