How to Use Subtotals in Excel 2003

1. Open your Excel worksheet with your data arranged in columns.
2. Select the column you want subtotaled. Hold down the left mouse button and drag down the column.
3. Under 'Data' on the top menu, select 'Subtotal' from the dropdown menu.
4. In the 'Subtotal' pop-up window that appears, check the features you want in your subtotal, and then click 'OK.'
5. A subtotal of your numbers will appear on the worksheet. Save your work.

Read more ►

How to Fix a Corrupt XLS File

1. Locate the file that requires repair. For best results, copy the corrupt file to a stable hard drive, if it is located on removable media or on a networked device.
2. Click 'File->Open...,' (Excel 2003), or the 'Office Button,' then 'Open.' (Excel 2007).
3. In the 'Open' window, select the corrupt file.
4. Click the drop-down arrow next to the 'Open' button, and select 'Open and Repair' from the menu. Excel will open the file in 'File Recovery' mode and attempt to repair the corruption.

Read more ►

How to Create Labels From Excel

1.
Open a blank Excel worksheet. Go to the 'Page Layout' tab of Excel 2007 and select 'Margins.' In Excel 2003 or earlier, go to the 'File' menu, select 'Page Setup' and go to the 'Margins' tab.
2.
Change the 'Top' box to '0' and leave the header at '0.5.' Change both the left and right margins to '0.19.' Change the bottom margin to '0' and leave the footer at '0.5.' Select 'Horizontally' and 'Vertically' under 'Center on Page.' Click 'OK' to apply these changes.
3.
Select cells A1 through A10. If you are using Excel 2007, click the 'Format' dropdown of the Home tab and select 'Row Height.' In version 2003 or earlier, go to the 'Format' menu, point to 'Row' and click on 'Height.' Change the row height to '72' and click 'OK.' Repeat this step, selecting 'Column Width' instead of row height. Change the width to '35.' Apply these changes to cells C1 through C10 and E1 through E10, also.
4.
Select cells B1 through B10. Following the instructions in Step 3, change the column width to '1.29.' Change D1 through D10 to '1.29' also.
5.
Select cells A1 through E10. Click the 'Borders' dropdown arrow, which is in the Font section of the Home tab in Excel 2007 and is on the Format toolbar in Excel 2003 or earlier. Select 'All Borders.'
6. Enter addresses into the cells in columns A, C and E. B and D will be margins between the columns.

Read more ►

How to Validate Data in Excel

1. Know the types of data that you can validate in an Excel spreadsheet. Numbers, dates and times and length can be limited to whole numbers, or ones including decimals, be a minimum or maximum amount or within a range.
2. Use a list of values for the cell. This is helpful when categorizing items with a specific list of data or when the cell always contains one choice such as small, medium or large.
3. Decide if you want the user to view a message when the cell is selected prior to them entering data (input message) or when incorrect data is entered (error message.) Error messages can simply provide information about what should be in the cell, a warning that the data doesn't fit the cell or a stop message not allowing any data that isn't correct to be entered.
4. Set up and name the list. The list can be in the same worksheet or in a different one. Once the data is entered, highlight all relevant cells and click on the 'Insert' command. Choose 'Name' then 'Define.' Enter the equals sign and the name of the list, for instance, '=listname.'
5. Choose one of the cells that you want to validate and select the 'Data' command. Choose 'Validation' and under the 'Settings' tab select the appropriate description for the cell limits. Depending on the choice, additional boxes will appear that need to be filled in.
6. Click the 'Format Painter' icon on the top of the toolbar and click the cell you just validated. Highlight any other cells that have the same validation criteria.

Read more ►

How to Reset the Excel Document Password

1. Open the worksheet that you wish to change or remove the password for. Enter the password when prompted.
2. Select the 'File' menu to open a left-hand menu pane.
3. Click 'Info.'
4. Click 'Encrypt Workbook' next to 'Permissions.' Another menu will show up.
5. Choose 'Encrypt with password.' A password encryption window will appear and the other menus will close.
6. Remove the password by clearing the 'Password' field and selecting 'OK.' Modify the password by typing in a new one in the 'Password' field, then selecting 'OK.'

Read more ►

How to Make a Pie Chart in Excel 2003

1. Enter the data labels into the cells in one column or row of the Excel worksheet. Enter the data into corresponding cells in the next column or row.
2. Select the data by depressing the left mouse button and dragging the mouse pointer over the cells.
3. Go to the 'Insert' menu and select 'Chart' or click the 'Chart Wizard' button on the standard toolbar to start the 'Chart Wizard.'
4. Select 'Pie Chart' as the chart type. Choose a subtype, such as 'Exploded Pie' or '3-D Pie.'
5. Click 'Next.' Make any other changes or additions in the wizard as desired. Click 'Finish' to complete the chart.

Read more ►

How to Remove Page Breaks From Excel 2007

1. Open the Excel file in which you'd like to remove a page break. Click the 'View' tab at the top of the screen.
2. Click 'Page Break Preview' in the 'Workbook Views' group. This allows you to see your manual page breaks.
3. Click the row or column label after the page break you wish to delete. As an example, to delete the page break between columns E and F, you would click the top 'F' column label, which selects the entire F column. Likewise, click the '20' row label to select the page break between rows 19 and 20.
4. Click the top 'Page Layout' tab.
5. Click 'Breaks' in the 'Page Setup' group, and select 'Remove Page Break.' Alternatively, select 'Reset All Page Breaks' to remove them all.
6. Click the 'View' tab, and click 'Normal' in the 'Workbook Views' group to return to your normal view mode.

Read more ►

How to Use the Quartile Function in Excel

1. Open a new Microsoft Excel 2010 spreadsheet. Click on the top-left cell in the spreadsheet.
2. Type your data set into the first column of cells. Each number should have its own cell.
3. Click on cell 'B1.' Type in '=quar' to open a pop-up menu with three options. Double-click 'Quartile.exc' to use the newer version of the quartile function. Double-click 'Quartile.inc' to use the older version of the function. If you need this worksheet to work with earlier versions of Excel, double-click the 'Quartile' function. 'Quartile' and 'Quartile.inc' are the same, but only 'Quartile' works on earlier versions of Excel. Once you make your choice, Excel completes the function name and places an open parenthesis in the formula bar.
4. Click on cell 'A1' and hold down the mouse button. Drag the mouse to the last data entry in the column and release the button. Press the comma key.
5. Enter '1,' '2' or '3' into the formula bar. '1' gives your the first quartile, '2' gives you the second quartile, which is also the median, and '3' gives you the third quartile. If you are using the 'Quartile.inc' function, you can also enter '0' or '4,' which gives your the minimum value and maximum value, respectively.
6. Enter a close parenthesis and press the 'Enter' key. The formula disappears and is replaced by your desired quartile for the data set. The number that appears represents the value where the range of numbers is divided into quarters.

Read more ►

How to Create a Calendar Using Excel

1. Open Microsoft Excel. Click on the 'Tools' menu, point to 'Macro' and click on 'Visual Basic Editor' if using Excel 2003 or earlier. For Excel 2007/2010, click on the 'Developer' tab and then click on 'Visual Basic.' If you don't see the 'Developer' tab, click on the 'Office' button and then 'Excel Options.' On the first screen, click on the check box next to 'Show Developer tab on the Ribbon' and then click 'OK.' The 'Developer' tab should show now.
2. Click on the 'Insert' menu item in the Visual Basic Editor. Click on 'Module' to start a new code module.
3. Copy the first part of the following code in the new module:' Sub CalendarMaker()' Unprotect sheet if had previous calendar to prevent error.ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _Scenarios:=False' Prevent screen flashing while drawing calendar.Application.ScreenUpdating = False' Set up error trapping.On Error GoTo MyErrorTrap' Clear area a1:g14 including any previous calendar.Range('a1:g14').Clear' Use InputBox to get desired month and year and set variable' MyInput.MyInput = InputBox('Type in Month and year for Calendar ')' Allow user to end macro with Cancel in InputBox.If MyInput = '' Then Exit Sub' Get the date value of the beginning of inputted month.StartDay = DateValue(MyInput)' Check if valid date but not the first of the month' -- if so, reset StartDay to first day of month.If Day(StartDay)
1 ThenStartDay = DateValue(Month(StartDay) '/1/' _Year(StartDay))End If' Prepare cell for Month and Year as fully spelled out.Range('a1').NumberFormat = 'mmmm yyyy'' Center the Month and Year label across a1:g1 with appropriate' size, height and bolding.With Range('a1:g1').HorizontalAlignment = xlCenterAcrossSelection.VerticalAlignment = xlCenter.Font.Size = 18.Font.Bold = True.RowHeight = 35End With' Prepare a2:g2 for day of week labels with centering, size,' height and bolding.With Range('a2:g2').ColumnWidth = 11.VerticalAlignment = xlCenter.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.Orientation = xlHorizontal.Font.Size = 12.Font.Bold = True.RowHeight = 20End With' Put days of week in a2:g2.Range('a2') = 'Sunday'Range('b2') = 'Monday'Range('c2') = 'Tuesday'Range('d2') = 'Wednesday'Range('e2') = 'Thursday'Range('f2') = 'Friday'Range('g2') = 'Saturday'' Prepare a3:g7 for dates with left/top alignment, size, height' and bolding.With Range('a3:g8').HorizontalAlignment = xlRight.VerticalAlignment = xlTop.Font.Size = 18.Font.Bold = True.RowHeight = 21End With' Put inputted month and year fully spelling out into 'a1'.Range('a1').Value = Application.Text(MyInput, 'mmmm yyyy')' Set variable and get which day of the week the month starts.DayofWeek = WeekDay(StartDay)' Set variables to identify the year and month as separate' variables.CurYear = Year(StartDay)CurMonth = Month(StartDay)' Set variable and calculate the first day of the next month.FinalDay = DateSerial(CurYear, CurMonth 1, 1)' Place a '1' in cell position of the first day of the chosen' month based on DayofWeek.'Remove the quotation marks on the first and last line of the code.
4. Copy the rest of the code that takes the format from the code above and create the actual calendar. Paste the code right under where the code from above ends.'Select Case DayofWeekCase 1Range('a3').Value = 1Case 2Range('b3').Value = 1Case 3Range('c3').Value = 1Case 4Range('d3').Value = 1Case 5Range('e3').Value = 1Case 6Range('f3').Value = 1Case 7Range('g3').Value = 1End Select' Loop through range a3:g8 incrementing each cell after the '1'' cell.For Each cell In Range('a3:g8')RowCell = cell.RowColCell = cell.Column' Do if '1' is in first column.If cell.Column = 1 And cell.Row = 3 Then' Do if current cell is not in 1st column.ElseIf cell.Column
1 ThenIf cell.Offset(0, -1).Value >= 1 Thencell.Value = cell.Offset(0, -1).Value 1' Stop when the last day of the month has been' entered.If cell.Value > (FinalDay - StartDay) Thencell.Value = ''' Exit loop when calendar has correct number of' days shown.Exit ForEnd IfEnd If' Do only if current cell is not in Row 3 and is in Column 1.ElseIf cell.Row > 3 And cell.Column = 1 Thencell.Value = cell.Offset(-1, 6).Value 1' Stop when the last day of the month has been entered.If cell.Value > (FinalDay - StartDay) Thencell.Value = ''' Exit loop when calendar has correct number of days' shown.Exit ForEnd IfEnd IfNext' Create Entry cells, format them centered, wrap text, and border' around days.For x = 0 To 5Range('A4').Offset(x * 2, 0).EntireRow.InsertWith Range('A4:G4').Offset(x * 2, 0).RowHeight = 65.HorizontalAlignment = xlCenter.VerticalAlignment = xlTop.WrapText = True.Font.Size = 10.Font.Bold = False' Unlock these cells to be able to enter text later after' sheet is protected..Locked = FalseEnd With' Put border around the block of dates.With Range('A3').Offset(x * 2, 0).Resize(2, _7).Borders(xlLeft).Weight = xlThick.ColorIndex = xlAutomaticEnd WithWith Range('A3').Offset(x * 2, 0).Resize(2, _7).Borders(xlRight).Weight = xlThick.ColorIndex = xlAutomaticEnd WithRange('A3').Offset(x * 2, 0).Resize(2, 7).BorderAround _Weight:=xlThick, ColorIndex:=xlAutomaticNextIf Range('A13').Value = '' Then Range('A13').Offset(0, 0) _.Resize(2, 8).EntireRow.Delete' Turn off gridlines.ActiveWindow.DisplayGridlines = False' Protect sheet to prevent overwriting the dates.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _Scenarios:=True' Resize window to show all of calendar (may have to be adjusted' for video configuration).ActiveWindow.WindowState = xlMaximizedActiveWindow.ScrollRow = 1' Allow screen to redraw with calendar showing.Application.ScreenUpdating = True' Prevent going to error trap unless error found by exiting Sub' here.Exit Sub' Error causes msgbox to indicate the problem, provides new input box,' and resumes at the line that caused the error.MyErrorTrap:MsgBox 'You may not have entered your Month and Year correctly.' _ Chr(13) 'Spell the Month correctly' _ ' (or use 3 letter abbreviation)' _ Chr(13) 'and 4 digits for the Year'MyInput = InputBox('Type in Month and year for Calendar')If MyInput = '' Then Exit SubResumeEnd Sub'Remove the quotation marks on the first and last line of the code. Click on the 'Save' icon on the toolbar. Click on the 'File' menu item and then 'Close and Return to Microsoft Excel.'
5. Run the 'CalendarMaker' module. In Excel 2007/2010, click on the 'Developer' tab and then click on 'Macros.' In earlier versions of Excel, click on 'Tools,' point to 'Macros' and then click on 'Macros.' Select the 'CalendarMaker' macro from the list in the window that pops up and then click on 'Run.'

Read more ►

How to Change the Text Orientation in Cells in Excel 2003

1. Open your Excel worksheet.
2. Select the text cells you wish to change. Hold down the left mouse button and drag across the cells or hold the 'Ctrl' key down as you click on individual cells.
3. Click 'Format' on the top menu to access the dropdown list of formatting options.
4. Select 'Cells.' A new window will appear with cell formatting options.
5. Click on the 'Alignment' tab for options on how to align and orient text in your cells.
6. Under 'Orientation,' change the angle of your text cells until they are oriented.
7. Click 'OK' to implement your changes. Then save your work.

Read more ►

How to Change the Cursor in Microsoft Excel

1. Open Excel.
2. Navigate around the spreadsheet. The mouse cursor remains as a white plus sign whenever the cursor is located over a cell in any of the program's rows or columns.
3. Hover the mouse over a cell that is currently selected to change the cursor to other designs that reflect certain actions. Selected cells have a dark black border around them, and their contents are also displayed in the program's formula bar above the spreadsheet grid. When the mouse is placed along any of the cell's borders, it turns to a quadruple arrow. When displayed, this cursor may be clicked and dragged to move the contents of the cell to another cell.
4. Position the mouse over the lower right corner of the selected cell. The cursor changes to a black plus sign. This is the 'fill handle' and may be used to quickly copy and paste the cell's contents to any adjacent cells. Simply drag the cursor in any direction and the cells copied.
5. Hover the mouse over any part of the Excel formula bar. This is the long white strip at the top of the program window. It is labeled at its start with the term 'fx.' The cursor will change to a standard text input cursor, resembling a capital letter 'I'. When displayed, you may click in the formula bar to edit cell contents. The same cursor will appear if you double-click in any cell.
6. Position the mouse over any toolbar button or menu at the top of the program and the mouse cursor will change to a standard Windows pointer so you may click and access those tools.

Read more ►

How to Group and Outline Data in Microsoft Excel 2003

1. Select the data that you wish to group and outline. The fastest way to select data for this operation is by clicking and holding the mouse button as you drag the cursor over the desired data and highlight it; release the mouse button when you are finished and the data will remain highlighted.
2. Access the 'Group and Outline' menu. Scroll to the “Data” tab on the command bar and select “Group and Outline Data.”
3. Group and outline the data. From the submenu that opens, scroll to and select “Group Data.” This will group the data and outline the rows and columns of the data that you just grouped. The outline will appear as a bar above the column letters and row numbers, which has a plus sign and a minus sign on the ends.
4. Hide the data group. Click on either sign--the plus sign reveals data and the minus sign hides data--to hide or show the groups of data.

Read more ►

How to Calculate Payback on Excel

1. Open a new Microsoft Excel worksheet and type 'Initial Investment' in cell A1. Put the cost of the project in cell B1. For example, assume a project costs $500.
2. Type 'Annual Cash Inflows' in cell A2. Put your estimated cash inflows each year in cell B2. In the example, assume you receive $60 a year from the project.
3. Type 'Payback' in cell A3.
4. Type '=B1/B2' in cell B3. In the example, B3 will say 8.333333333. The project will take about eight years and three months to cover its costs.

Read more ►

How to Change the Background Color in MS Excel

1. Select the cell or cells in which you wish to change the background color. In Excel 2007, click on the 'Home' tab to view the 'Home' ribbon.
2. Click on the arrow next to the paint bucket icon in the 'Font' section. This is the 'Fill Color' button. In Excel 2003, the paint bucket icon is located in the 'Formatting' toolbar. This toolbar is typically located just above the actual spreadsheet and under the main menu.
3. Select the color you want from the menu that appears. If the color you want is not in the menu, click on 'More colors.' Under the 'Standard' tab you can select from a larger array of colors. Or, under the 'Custom' tab you can enter in the RGB values for your custom color.
4. Click the 'OK' button to apply the background color to the selected cells.

Read more ►

How to Restore Data on Microsoft Excel

1. Run 'Detect and Repair' if you are using Excel 2003 or earlier. Go to the 'Help' menu and select 'Detect and Repair.' Clear the checkboxes and click start. Detect and Repair will scan the program and make certain repairs, which may restore the missing data.
2. Run 'Diagnostics' in Excel 2007. Start Excel and click the 'Office Button.' Select 'Excel Options.' Select 'Resources,' 'Diagnose,' and then 'Continue.' Click on 'Start Diagnostics.' Diagnostics will scan Excel for potential problems and repair what it can.
3. Recover data when Excel stops responding. Go to the 'Start' menu, point to 'All Programs,' and then 'Microsoft Office Tools.' Select 'Microsoft Application Recovery.' Click on Excel in the Recovery window, and then click 'Recover Application.' Excel will close and restart with the files that were open when it stopped responding.
4. Start Excel and go to the 'File' menu or the 'Office Button.' Select 'Open.' Find the damaged file and click on it to select it. Click the drop-down arrow on the 'Open' button at the bottom of the window. Select 'Open and Repair.'
5. Save the file as HTML, if you are able to open it. Go to the 'File' menu or 'Office Button' and click 'Save As.' In the 'Save as Type' select 'Web Page.' When you open the HTML version, save it again as a 'Microsoft Office Workbook' with a different name than the corrupted file.

Read more ►