How to Take the Label 'Sum of' Out of a Pivot Table

1. Click on a cell in the PivotTable in the column from which you want to remove 'Sum of.'
2. Click the 'Options' tab on the toolbar, then click the 'Field Settings' button under the 'Active Field' area of the toolbar. A small window will pop up.
3. Place your cursor in the 'Custom Name' field and erase the 'Sum of' from the name.
4. Move your cursor to the end of what is left and add a space to the name. This is because once you remove the 'Sum of,' the remaining name is that same as a recognized field in the field list, and if you try to make a second field, Excel will give you an error. The extra space lets Excel differentiate between the two names, but they will look the same to anyone who views your PivotTable.

Read more ►

How to Use Excel's VARP Function

1. Learn the syntax of VARP. It is VARP(number1,number2,...) where number1, number2,... are up to 30 arguments which evaluate to numbers and comprise a complete population. VARP requires at least one argument.
2. Use arrays, names or references that contain numbers as arguments for VARP, in addition to pure numbers. Only numbers in an array or reference will be counted. Empty cells, error values, logical values or text in an array or reference will be ignored.
3. Enter logical values and text that represents numbers directly into the argument list. Error values or text that does not evaluate to a number will cause an error.
4. Ensure VARP is the correct function to use. VAR should be used instead of VARP if the data represents only a population sample. Use the VARPA function instead if you wish to include a reference that contains logical values or numbers represented by text in the calculation.
5. Calculate the VARP as the sum of (number - AVERAGE(number1,number2,...))^2/n where number is each value in the population and n is the number of values in the population. For A2 = 3.5, A3 = 5, A4 = 7.23 and A5 = 2.99, VARP(A2,A3,A4,A5) would return 2.71335.

Read more ►

How to Extract Tables From Multiple Word Documents Import Them to Excel

1. Use Windows Explorer to copy a Word file containing at least one table to the folder 'C:\.' Rename the file as 'table.docx.' You'll write an introductory program that will read a table cell from this file.
2. Click the 'Developer' tab, then click the 'Visual Basic' button to enter the Visual Basic programming environment. Paste the following program into VB code window. This program creates a link to Word, which allows excel to use Word's virtual objects just as though the program were running inside a Word document. This process is called 'Automation.'Public Sub LoadWordTablebak()Dim pgmWord As Word.ApplicationSet pgmWord = CreateObject('Word.Application')pgmWord.Documents.Open ('c:\table.docx')MsgBox pgmWord.ActiveDocument.Tables(1).Cell(1, 1)pgmWord.ActiveDocument.ClosepgmWord.QuitEnd Sub
3. Click the 'Tools' menu, then click the 'References' command. Scroll through the window that appears to locate the 'Microsoft Word' item, then click the checkbox for that item. This action lets Excel access the visual objects in a Word document.
4. Click any statement in the program, then click the 'Run' menu's 'Run' command. The program will display a message box showing the contents of a table cell in a Word document. You'll now expand the program to load in a complete table from any Word document.
5. Paste the following revised program below the 'End Sub' statement of the original program. This program prompts the user for the filename of a Word document. The user must also type in the number of a table within the document. The program uses the automation method of the first program to load copy the specified table cell by cell into an Excel spreadsheet.Public Sub LoadWordTable2()Dim docname As StringDim TableId As IntegerDim c, r, startRow As IntegerDim curCellDim pgmWord As Word.ApplicationSet curCell = ActiveCellSet pgmWord = CreateObject('Word.Application')docname = InputBox('Enter Word document name')docname = InputBox('Enter Word document name')While (docname
'')TableId = InputBox('Enter table number')pgmWord.Documents.Open ('c:\table.docx')With pgmWord.ActiveDocument.Tables(TableId)startRow = ActiveCell.RowFor c = 1 To .Columns.CountFor r = 1 To .Rows.CountcurCell.Value = .Cell(r, c)'Move to next rowSet curCell = curCell.Offset(1, 0)Next r'Move to next columnSet curCell = Cells(startRow, curCell.Column 1)Next cEnd WithpgmWord.ActiveDocument.Closedocname = InputBox('Enter Word document name')WendpgmWord.QuitEnd Sub
6. Click the 'Excel' application icon in the Windows taskbar to return to Excel, then click the 'Macros' button of the 'Developer' tab. Double-click the 'LoadWordTable' macro to run that macro.
7. Type the name of a Word document containing a table when the prompt to do so appears. Type the number of the table you want when the prompt for that table appears. For example, if you want the second table in the document, type '2.' Excel will load the table into the current spreadsheet.
8. Continue loading Word tables with the program as needed. Press 'Enter' at the 'document name' prompt to terminate the program.

Read more ►

How to Create a Form in Microsoft Excel 2007

1. Launch Excel from the start menu or from the desktop. Click the small drop-down arrow at the very top of the screen, to the right of the Save, Undo, and Re-do buttons. In the menu that appears, choose 'More commands.' The Excel Options screen should appear.
2. In Excel Options, click the drop-down at the top of the left-hand list of commands, labeled 'Choose commands from.' In the list that drops down, choose 'All commands,' which is the third item down from the top. In the list box below the drop-down, a long list of commands will appear, sorted alphabetically. Locate the command named 'Forms,' click on it, and click the 'Add' button in the center of the screen. Click 'OK' to close the Excel Options screen, and the Forms button will appear next to the Re-do button at the top of the screen.
3. Open the spreadsheet with the rows that are to be viewed in forms. Click on a cell that is in the list of data the form is to be for. It can be a heading cell or any cell within the data. Click on the Forms button that was added in step 1. Excel will automatically create the form.

Read more ►

How to Paste Special HTML in Microsoft Excel 2003

1. Create the desired HTML that you wish to paste special into Excel with.
2. Copy the HTML code. You can easily copy the HTML by highlighting it all and pressing the hotkeys “CTRL-A” and then by pressing the hotkeys “CTRL-C.”
3. Access the 'Paste special' menu. To access this menu, you will need to scroll to the “Edit” tab and select “Paste Special.”
4. Set the paste special options. Under the paste special properties menu, in the “As” box, you should left-click on “HTML” to select the format.
5. Paste special the HTML into Excel. Now click on the “OK” button to paste special HTML into Excel.

Read more ►

How to Speed Up an ODC Connection in Excel 2007

1. Open the Microsoft Excel 2007 program on your computer and then click the 'Microsoft Office' button.
2. Click the 'Open' option and then select the Excel 2007 worksheet you want to speed up an ODC connection for. Click the 'Open' button.
3. Click the 'Data' tab from the top of the application and then click the 'Connections' option from the 'Connections' group.
4. Select the 'Usage' tab in the Workbook Connections dialog box. Click on each box in the 'Refresh control' section so that they're deselected.
5. Enter a lower number of records to retrieve in the 'Maximum number of records to retrieve' field.
6. Click the 'Definition' tab and then click the 'Parameter' button. You can then edit the parameters for your ODC query. For example, you can make your query as short as possible to speed up the connectivity.
7. Click the 'Apply' button to save all of your changes and then click the 'OK' button to close out of the dialog box.

Read more ►

How to Insert a Picture or Logo into an Excel Document

1. Open the Excel document that you want to add the logo or picture to.
2. Click Insert and select Header Footer from the Text group.
3. Click inside the header or footer where you wish the picture to appear.
4. Click Picture under Header Footer Tools in the Header Footer Elements group on the Design tab.
5. Select your picture and click 'Insert.' The picture will appear where you clicked.

Read more ►

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 ►