Excel

Browse » Home » All posts

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.

Subscribe to: Posts (Atom)