How to Delete Characters Over 40 Character Length in Excel

1. Click the 'Start' button on the taskbar, type 'Microsoft Excel' in the Search box and then press 'Enter.'
2. Press 'Ctrl O' and then choose the Excel document from which you want to delete characters over 40 characters in length on the dialog box that opens.
3. Type the formula '=LEFT(cell_number,40)' in the cell in which you want to delete characters over 40 characters in length. Replace, cell_number with the unique number of the cell, for example, A1.

Read more ►

How to Generate a Random Number in Excel

1. Open Microsoft Excel 2007, and start a new, blank spreadsheet or open an existing spreadsheet from your files into which you want to generate a random number.
2. Click the cell you want to generate a random number into so it is selected. The cell will have a thick black line outlining it.
3. Type '=RAND()' (no quotations) into the 'Formula' text box near the top of the Excel screen. This is the function that instructs Excel to enter a random number between 0 and 1 into the selected cell.
4. Press the 'Enter' key on your keyboard. The random number will be generated into the selected cell and you will be taken to the cell beneath it.
5. Continue the process of generating random numbers into your Excel cells using the same method as outlined above.

Read more ►

How to Fill the Cells With the Names of the Successive Months in Excel

1. Open a new Microsoft Excel 2010 spreadsheet.
2. Click on cell “A1” and type in the name of the first month you want to use. Excel can begin to fill successive months from any month, so you don’t necessarily need to have “January” be first. You can also add the year, if necessary. Excel will automatically add to the year as it adds each successive month. Press “Enter” when you are done entering the month.
3. Click on cell “A1” again, as Excel will change the selected cell once you pressed “Enter.” Move your mouse over the lower-right corner of the cell. Your pointer will change into a “ ” sign.
4. Click and hold the mouse button. Drag the mouse down the spreadsheet. As you move the mouse, a small pop-up window will display the month that will appear in the last selected cell, so you can tell how far you need to drag the mouse.
5. Release the mouse button when you have reached the last cell that you want to fill with month names. Excel will automatically add the names of the successive months to each cell, starting from the first month that you entered.

Read more ►

How to Calculate Weighted Averages for Grades in Excel

1. Type 'Grade' in cell A1, 'Weight' in B1 and 'Total Worth' in C1.
2. Type your grades in column A. For example, if you received a 95 and an 80, then type '95' in A2 and '80' in A3.
3. Type the weight each grade has in column B, next to its corresponding grade. In the example, if the first grade is worth 60% and the second grade is worth 40%, then type 60% in B2 and 40% in B3.
4. Type '=A2*B2' in cell C2. This weights the grades. Copy and paste this formula next to each grade.
5. Type '=sum(' in column C after your last grade, then highlight all the cells that have a number in coumn C and hit the 'Enter' key. The result that appears is your weighted average.

Read more ►

How to Use the AutoFill Function in Excel 2003

1. Open Excel.
2. Open your worksheet by clicking 'File > Open.'
3. If you don't have an existing worksheet, create a new one. Go to 'File > New.'
4. Enter your information. The AutoFill function will be able to fill in information in a series. When making a calendar, for example, you can type 'January' into the first box and use AutoFill to put the subsequent months in the next cells.
5. Place your mouse over the bottom right hand corner of the cell with 'January' (or whatever the first cell in your series is). When you see a small 'x' appear where your mouse arrow was, click and drag the 'x' to the cell where your series will end. The rest of the series should be filled in for you.

Read more ►

How to Reduce the Size of an Excel Workbook

1. Delete the extra default worksheets in a new Excel workbook. Each new workbook that Excel creates has three worksheets (Sheet1, Sheet2 and Sheet3). These sheets require additional memory and space on the hard drive when you save your workbook. Right click on the name tab (at the bottom of the workbook) for the second worksheet. Then choose Delete from the pop-up context menu. Repeat these steps to delete the third worksheet.
2. Reduce the size of graphics, line art or smart art that you have inserted or imported into the workbook. Graphics quickly increase the size of every saved Excel file. Click on any embedded graphic object to activate the Picture Tools menu. Compress, crop or decrease the color saturation and resolution of jpeg, gif, bmp, tiff and png files. You can also use an external graphics program to resize clipart and other file formats, and then insert the smaller sized object into your workbook.
3. Use TrueType fonts as the default font for your Excel worksheet labels. Custom fonts require additional memory allocations. Excel stores the instructions for your printer in the workbook. Therefore, your file size will increase with each custom font.
4. Apply text formats such as bold, italics and colors to individual cells only. If you select a row or column when adding formats, Excel applies the formatting to all cells in the selection. Since Excel has more than 200 columns and thousands of rows, the formatting will use Random Access Memory to manage the formats for the rows and columns.
5. Eliminate unnecessary comments, text boxes and other enhancements such as watermarks, cell shading and borders. All of them require additional space when you save the file. Headers, footers, page numbers, charts, hyperlinks, tables and range names in formulas also increase the size of an Excel workbook. Printing options such as gridline displays and repeated row and column headings can also be eliminated to reduce the size of your workbook.
6. Save your workbook in PDF format. This option is included in Excel 2007; most earlier versions require an add-in that you must download. Choose Save As in the File menu in Excel versions through 2003. It is under the Microsoft Office Button menu (left of Home) in Excel 2007. Select PDF from the menu. Then select the Optimize for minimum size radio button in the file Save dialog box. This creates a smaller PDF file that is ready to email or publish online.

Read more ►

How to Use a Data Form in Microsoft Excel 2007

1. Open the Microsoft Excel 2007 program on your computer and then click on any cell where you want the form to start.
2. Click on the arrow next to the Quick Access toolbar and then click on the 'More Commands' option. Select 'All Commands' and then click on the 'Form' button from the list.
3. Click on the 'Add' button and then 'OK.' Click 'Form' from the Quick Access toolbar, and then you can enter your data into the first row of the form.
4. Click on the 'New' button to add a new row and then enter your data. Press the 'Enter' key on your keyboard, and the row of data will be added to the bottom of the form.
5. Select any row that you want to remove and then click on the 'Delete' button. Choose 'Close' at any time to close out of the data form and return to your worksheet.

Read more ►

How to Find Duplicate Entries on an Excel Spreadsheet

1. Select the first cell in your column of data. This cell will serve as a template for the conditional statement that searches for duplicates in the file. After the cell is selected, click the 'Format' menu item and select 'Conditional Formatting.' This opens a new dialog window.
2. Select 'Formula Is' in the new dialog window. A text box displays, prompting you for an Excel formula. Enter the following code into the text box:=COUNTIF (A:A,A1) > 1The 'countif' function searches through the 'A' column and finds any duplicates (the formula assumes that A1 is your first cell. If your data begins at A2, replace 'A1' with 'A2' in the formula. Replace each 'A' with a 'B' to check column B, etc.).
3. Click the 'Format' button in the 'Conditional Formatting' dialog box. This opens a window prompting you for a color selection. Click a color you want to use as the highlight color for the duplicates. The 'countif' function finds the duplicates, and the format color is used to show you which cells are found. Click 'OK.'
4. With your cursor in the first data cell, click 'Copy' from the Edit menu. Press 'Ctrl spacebar' then click 'Paste Special' from the Edit menu. Select 'Formats' from the Paste Special dialog, then click 'OK' to copy the conditional formatting through the entire column.
5. Scroll down the column of data to find each duplicate. The duplicates are highlighted in the color you chose. You can either delete these duplicates, exclude them from future formulas or move them to another area of the spreadsheet.

Read more ►

How to Use Excel's DSUM Function

1. Learn the syntax of DSUM (database, field, criteria). Database is a cell range that specifies the database, field is a column in the database from which DSUM will add values and criteria is a cell range with the criteria to be used in selecting the values.
2. Define the database for DSUM. The first cell in the range will indicate the upper left corner of the database array and the second cell will indicate the lower right corner of the array. The first row of the array must contain the column names.
3. Specify the field for DSUM. It can include the column name enclosed in double quotation marks or a number representing the column's position in the database where 1 is the first column, 2 is the second column and so on.
4. Supply the criteria for DSUM. This cell range must contain at least one column name and one condition for that column. The condition will generally be of the form ='condition.' Thus, a value of ='=Apple' would select rows where the value in the specified column is equal to 'Apple.'
5. Enter the following in the first four columns of an Excel spreadsheet:Row 1: Tree, Height, Age and Yield;
Row 2: ='=Apple' and ='>10' (nothing in columns three and four);
Row 3: ='=Pear' (nothing in columns two, three and four);
Row 4: Tree, Height, Age and Yield;
Row 5: Apple, 18, 20 and 14;
Row 6: Pear, 12, 12 and 10;
Row 7: Cherry, 13, 14 and 9;
Row 8: Apple, 14, 15 and 10;
Row 9: Pear, 9, 8 and 8; and
Row 10: Apple, 8, 9 and 6.
6. Interpret the formula =DSUM(A4:D10,'Yield',A1:B2) as follows: The database is defined as the array A4:D10. Note the column names in the first row of this array. 'Yield' is the column name from which the values will be taken. The criteria are given by A1:B2 which indicates that the column named Tree must contain 'Apple' and the column named Height must contain a value greater than 10. DSUM(A4:D10,'Yield',A1:B2) will therefore be evaluated as SUM(14,10)=24.

Read more ►

How to Convert Columns of Text to Rows

1. Click the letter at the top of a column to select a column. Hold 'Ctrl' and click on more column headers to choose several columns.
2. Press 'Ctrl' and 'X' to cut the selected columns.
3. Right-click inside the first cell into which you want to paste the data as rows.
4. Select 'Paste Special' from the drop-down menu.
5. Select the 'Transpose' check box in the 'Paste Special' dialog box.
6. Click 'OK.' The columns will now appear as rows.

Read more ►

How to Share Password Protect Track Changes in Excel

Password Protection for Specific Cells
1. Determine the level of protection needed for your spreadsheet. If there are specific cells you do not want changed but others that can be, you can just lock cells. If the spreadsheet needs to be fully locked, or you want to prevent users from accessing the actual file, then file-level protection is appropriate.
2. Click 'Home' on the ribbon after opening a file in Excel.
3. Navigate to the Cells section, click 'format,' and then click 'Lock Cell.'
Password Protection for Workbooks
4. On the ribbon click 'Review.'
5. Navigate to the changes group and click 'Protect Workbook.' This will bring up a pop-up window.
6. Select 'Structure' or 'Windows' from the pop-up window. If you lock the structure, users will not be able to reorder worksheets. If you lock windows, users will not be able to change worksheet window sizes.
7. Choose and write down a password to lock the workbook. This is optional, but enhances the security of your worksheet.
Tracking Changes
8. On the ribbon click 'Review.'
9. Navigate to the changes group and click on 'Share Workbook.' This will bring up a pop-up window.
10. Select the check box next to 'allow changes by more than one user at the same time.' Then click on the 'Advanced' tab and type in the number of days you want Excel to keep the change history. Click 'OK.' Note that Excel defaults to 30 days of change history. This is sufficient for most users.
11. Navigate to the changes group and click on 'Track Changes.' Then select 'Highlight Changes.' This will highlight any changes made to a cell so that you will be able to review it later.

Read more ►

How to Do Stacked Charts in Excel

1. Enter the data for the stacked chart into an Excel spreadsheet. Clearly label the data in the first row so that it is easier to identify when you are making the chart.
2.
Highlighting the data tells Excel which data to include in the chart.
Highlight the data you want to include in the stacked chart. Include the data labels (typically placed in the first row). You can highlight by clicking your left mouse button, holding it down, and dragging to include all pieces of data. Alternatively, to include the entire spreadsheet in the chart, press 'Ctrl-A' to highlight everything.
3. Open the chart interface. In Excel 2007, click the 'Insert' tab, then click the down arrow under Column, and click 'All Chart Types.' In Excel 2010, click the 'Insert' tab and click 'Chart.'
4. Choose the chart type by clicking or scrolling to the corresponding section. The most common type of stacked chart is column, but you may also choose bar.
5. Choose stacked chart style by clicking the corresponding stacked style under the chart type. Verify that the style you have chosen is stacked by hovering over it and looking for 'stacked' in the tool tip description. Choose a 100 percent stacked style if you want all bars to be full and display the composition breakdown. Choose the style that most clearly displays your data and fits your taste.
6. Press 'OK' and verify the data.

Read more ►

How to Audit Formulas in Excel 2007

1. Open an existing Microsoft Excel document to audit. Double click on the 'My computer' icon on the operating system desktop. Then locate the existing Excel document and double click on that file.
2. Select the 'Microsoft Office Main Menu Button' located in the top right hand corner of Microsoft Excel. From that menu click on the 'Excel Options' button located at the bottom of the menu dialogue box.
3. Click on the 'Advanced' options button on the Excel options dialogue box.
4. Check all the options under the 'Display Options for This Workbook' section. Also, make sure that the 'For Object: Show' option choose 'All.'
5. Show all the formulas in an Excel worksheet by selecting all the cells. Do this by clicking on the top left hand corner of the worksheet. Once they are all selected click on the 'Formulas' tab and choose 'Show All Formulas' from the formula auditing section of the formulas tab.
6. Audit the formulas by choosing the 'Error Checking' button in the formula auditing section of formula ribbon. A user can also trace dependents and precedents by choosing the 'Trace Dependents' and 'Trace Precedents' option from the very same section of the formula auditing section of the formulas ribbon.

Read more ►

How to Subtract Dates in Excel 2007

1. Open Excel 2007 and enter the current date in cell A1. Enter another date in cell B1.
2. Highlight cells A1 and B1. Click the 'Home' tab and locate the 'Numbers' group. Change the number format to 'Short Date.'
3. Click cell C1 and enter a subtraction formula. Enter 'A1-B1.' Press the 'Enter' key to see the results.

Read more ►

How to Create a Toggle Switch in Excel 2003

1. Open Microsoft Excel 2003. Click the 'Microsoft' button on the top left corner of the window. Click the 'Excel Options' button in the lower right corner of the dropdown menu.
2. Click the 'Popular' tab. On the box to the right, check the 'Show Developers tab in the Ribbon' box. Click the 'OK' button on the bottom right of the window.
3. Click the 'Insert' button in the top center of the window. Click on the 'Toggle' button on the bottom right corner of the dropdown menu.
4. Click the left mouse button anywhere on the screen. The toggle button appears. Click and hold the left mouse button on the 'Toggle' button to move it. Your toggle switch is now created.

Read more ►