How to Work With Pivot Tables in Excel 2003

1. Open the Excel 2003 file that contains the data you want to work with.
2. Ensure that each column of data has a header at the top. If you do not have a header row, you can right-click the number on the left side of your data's top row and choose 'Insert' from the pop-up menu. Type headers for each column into the new row. You also need to remove any subtotal cells from the middle of the data field, as the PivotTable can't handle these types of cells.
3. Click the 'Data' menu at the top of the screen and choose 'PivotTable and PivotChart Report.' This launches the PivotTable wizard. Select the 'Next' button on the first screen of wizard.
4. Click the small box next to the 'Range' field and the wizard will minimize. Select the data that you want to include in the PivotTable. Click the top-left cell of your desired data range, then hold down 'Shift' and click the bottom-right cell. Don't include any subtotal cells on the bottom or the right side of the data area. Click the small box again to bring the wizard back up, and choose 'Next.'
5. Choose between placing the PivotTable on a new worksheet, or an existing worksheet. If you choose to use an existing worksheet, you will have to select the cell where you want the PivotTable to start. The table will expand down and to the right of your selected cell. Click 'Finish' to complete the wizard and create your PivotTable. You will see an empty PivotTable along with a field list that contains the names of all your column headers.
6. Click an item in the field list, then drag and drop it to one of the four areas of the PivotTable. The first area sits just above the actual PivotTable and acts as a global filter for the data in the table. Then, there are the two areas that make up the rows and columns of the PivotTable. Finally, there is a large area in the middle of the PivotTable that comprises the main data for the table. Each area can have multiple fields, as they will just stack onto one another automatically.
7. Rearrange the PivotTable by clicking the name of the field in the PivotTable and dragging that name to a different area. You can also drag and drop the field name off the PivotTable to remove it.
8. Click the arrows next to any of the field names on the PivotTable to filter the report. Remove check marks from any item you want to remove from the table, or add check marks to bring any item back onto the table. By using this feature on the page field area above the PivotTable, you can filter the entire table based on the elements in this list.
9. Double-click any data or subtotal cell in the PivotTable to see a list of all the source data that Excel used to create that number. The list will appear on a new worksheet that Excel adds to the workbook.

Read more ►

How to Export the Outlook Calendar to Excel

1. Open Microsoft Outlook and allow the main screen to fully load. Click on the 'File' menu at the top of the page and select 'Import and Export' from the subsequent menu. Select 'Export to a file' from the next page and click the 'Next' tab at the bottom of the window.
2. Select 'Microsoft Excel' from the list of programs and click the 'Next' button. Select the 'Calendar' folder from the list of options on the next page and click the 'Next' button at the bottom of the screen.
3. Select a name for the calendar file and type it into the text box. This will be the name of the file used for the calendar in Excel. Click the 'Finish' tab at the bottom of the screen to share the calendar with Excel.

Read more ►

How to Use the Pivot Table Wizard in Excel

1. Start Excel and open the workbook file where you want to create the pivot table.
2. Format your data as straight lists of values. For example, you might have month, type of product, salesperson, or income.
3. Select the data you want in the pivot table.
4. Open the Data menu and select Pivot Table and Pivot Chart Report.
5. Click the radio button in the first section for 'Microsoft Excel list.'
6. Decide if you want just a table for your data, or if you also want charts. Then Click Next.
7. Verify that the range of cells you selected is listed in the Range field.
8. Click Next.
9. Decide whether you want the pivot table in a new or existing worksheet, then click Finish.

Read more ►

How to Turn on Worksheet Gridlines Row Headings Using VBA in Excel 2003

1. Launch Office Excel 2003, click the “Tools” menu, click “Macro” and click “Visual Basic Editor” window. Click the “Tools” menu and click “Project Explorer.”
2. Click “ThisWorkbook module” and copy and paste the following code to hide the gridlines and headings:Private Sub hideGridLines()
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
End Sub
3. Press “F5” to run the procedure.
4. Copy and paste the following code to your module to show the gridlines and headings:Private Sub showGridLines()
ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayHeadings = True
End Sub
5. Press “F5” to run the procedure.

Read more ►

How to Open an MS Excel File Without Excel

1. Locate the Excel file you would like to open on your computer. It may be on your desktop, in a folder or in a portable disk drive. The file extension (portion of the file name after the period) will be '.xls' for spreadsheets created in Excel 2003 and earlier versions. For Excel 2007 and later spreadsheets, the extension will be '.xlsx.'
2. Right-click on the file name if you use a PC or press 'Ctrl' and click on the file name if you use a Mac. This will cause a menu, known as a context menu, to pop up with options.
3. Select 'Open with...' from the context menu. This will open a list of programs you can use to view the spreadsheet. In some versions of Windows, you may need to click 'Choose default program' to get the list of programs.
4. Find the recommended programs at the top of the list. If you have any other spreadsheet programs on your computer, they should be listed here. Microsoft Works, Apple's Numbers, Lotus 1-2-3 and OpenOffice Calc can all open Excel spreadsheets, although you may lose some formatting. If you have any of these programs, just click the program name and the computer will open the spreadsheet in that application.
5. Click 'Cancel' if you do not have any spreadsheet programs on your computer, or if you try the recommended programs and they do not work.
6. Open an Internet browser, such as Internet Explorer, Safari or Chrome, and visit Microsoft.com to download the free Excel Viewer software. (A direct link is listed under this article's Resources section.) This software will allow you to open and view an Excel spreadsheet, but you will not be able to edit the data.

Read more ►

How to Rename a Worksheet in Excel 2003

1. On your computer, locate the folder that contains the worksheet you want to rename.
2. Right-click on the worksheet file name. This will summon a small pop-up menu with options.
3. Click the 'Rename' option. A small box will appear around the text.
4. Type in the new name for your Excel 2003 worksheet.
5. Click outside of the text box. This will save the new name.

Read more ►

How to Delete Comments in Excel 2007

Deleting Comments
1. Open the Excel workbook and worksheet containing the comment to be deleted.
2. Highlight the cells that contain comments you wish to delete. You may need to hold down the 'Ctrl' key while clicking if you are selecting several cells that are not adjacent to each other.
3. Left-click once on the 'edit' menu at the top of the screen. Then select 'Clear,' followed by 'Comments.'
Deleting All Comments
4. Open the workbook and worksheet containing the comments to be deleted.
5. Left-click on the 'Find Select' button at the top of the screen in the editing tool bar and choose 'Comments.'
6. Left-click 'Clear' and then 'Comments' in the 'Edit' tool bar.

Read more ►

How to Protect Cells From Modification in Excel 2003

1. Open Windows and pull up your Excel spreadsheet. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Highlight the cells to be protected. Choose 'Cells' from the 'Format' menu. Select the 'Protection' tab. Check the box to the left of 'Locked'.
3. Go to 'Tools'. Hold the mouse on 'Protection'. Then click 'Protect Sheet'.
4. Enter a password if you want to use one. If you do not want a password, just click 'OK'.

Read more ►

How to Add a Background Color or Pattern to Cells in Excel 2003

1. Open Microsoft Excel on your computer. You should be able to locate the program by using the 'Start' button. If you can not find it there, use the 'Search' or 'Find' function, or just look for the icon on your desktop.
2. Highlight the cells that you want to emphasize. You can click and drag or click on each cell individually while holding down the 'Ctrl' button. Using the 'Ctrl' button and clicking will allow you to select non-adjacent cells.
3. Look for the tiny paint bucket icon on your tool bar. Use this icon to fill in a cell with a single color. Choose the color by hitting the down arrow on the right of the paint bucket. To select the last color you used or the default color, just click on the paint bucket icon. If you only need one color, you'll be done after clicking 'OK.'
4. Left-click on your cell selection to reveal a drop-down Formatting menu if you want to add a pattern.
5. Choose a color and then click on 'Add Pattern' to customize the color and pattern for the chosen cell(s). Then click 'OK.'
6. Repeat the above steps for all of the different color and pattern combinations that you need. When you want to remove a color or fill pattern, just highlight the cells, click the arrow next to the paint bucket icon and select 'No Fill.'

Read more ►

How to Have Excel Solver Give Only Whole Numbers

1. Go to the 'Data' tab in Excel, and click the 'Solver' button to launch the Solver dialogue box.
2. Click the 'Add' button under Constraints to launch the 'Add Constraints' dialogue box.
3. Enter the name of the cell in the 'Cell Reference' box, or highlight a range of cells with your cursor to fill this box automatically.
4. Click the drop-down menu in the middle of the 'Add Constraints' dialogue box, and select 'int' from the menu. The word 'integer' will appear in the Constraint box.
5. Click 'OK'

Read more ►

How to Make Columns Wider in Excel

Click Drag
1. Place your mouse pointer on the top row where the column letters are displayed.
2. Move the cursor over the lines separating the columns and notice that the cursor becomes a cross.
3. Place the cursor over the line to the right of the column that you want to make wider. Be sure that it turns into a cross before going to the next step.
4. Click and drag the column over until it is the width that you want.
Use the Toolbar
5. Go to 'Format.'
6. Select 'Column' and then 'Width.'
7. Enter the width that you want the column to be in the 'Column Width' box.
8. Click 'OK.' The column's width will change.

Read more ►

How to Get the Row Number of a Cell in VBA

1. Open the Visual Basic Editor (VBE) by clicking on the 'Developer' tab and then clicking on 'Visual Basic.'
2. Click on the 'Insert' tab and then click 'Module.' This opens a blank window.
3. Copy and paste the following code into the blank window:Sub myActiveRow()myRow=ActiveCell.RowMsgBox myRowEnd Sub
4. Press 'F5' to run the routine.

Read more ►

How to Create a Button in Excel 2007

1. Click the 'Microsoft Office' button located in the top left corner of Excel.
2. Click 'Excel Options' (a rectangular button on the bottom right of the panel).
3. Click the 'Customize' category from the selection list on the left. Select 'All Commands' from the 'Choose commands from' drop-down list. Scroll down the list to select the type of button you want to add to your Excel 2007 spreadsheet. For example, if you want to add a macro to a button, choose 'Option Button (Form Control)' and click 'OK.' This adds a button to the 'Quick Launch' toolbar (located above the ribbon).
4. Click the button on the Quick Launch toolbar. Left-click anywhere on the spreadsheet and drag the mouse to create a box.
5. Right click-on the button to format the button or assign a macro.

Read more ►

How to Print Continuing Row and Column Headings for an Excel Worksheet

1. Open Microsoft Excel and the file you want to change.
2. Open the File menu and select Page Setup.
3. Select the Sheet tab.
4. Enter the cell numbers you want to use as your row headings in the Row to Repeat box. Enter the first cell number for the heading, then a colon, then the last cell number. For example: B4:F4.
5. Enter the cell numbers you want to use for your column headings in the Columns to Repeat box. Enter the first cell number for the heading, then a colon, then the last cell number. For example: A1:A2.
6. Click Print Preview to see how your document will look.
7. Select Close to exit from the Print Preview function.
8. Click OK to accept your changes.

Read more ►

How to Convert XLSM to XLS

1. Open the 2007 version of Excel or later. Click “Open” in the “File” drop-down menu. Open the .XLSM file from the folder tree that opens up.
2. Click “Save As…” in the “File” drop-down menu. Select “Excel Workbook” as the format. Check the portability notes of the format at the bottom of the Save panel to confirm the format has adequate backward portability for your needs.
3. Convert the XLSM file using an older version of Excel the same way by first downloading the converter titled “Microsoft Office Compatibility Pack for 2007 Office Word, Excel and PowerPoint File Formats.” Before downloading the converter, it is important that you update your version of Microsoft Office to the latest service pack (see Resources).

Read more ►