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 ►

How to Create Spread Sheets in Excel 2007

1. Click on the 'Office' button. This is the round button with the Microsoft logo at the upper left-hand corner of the ribbon menu.
2. Click 'New' to create a new spreadsheet.
3. Click on the icon for 'New Blank Workbook' in the center pain of the menu that pulls up. If there is no icon for 'New Blank Workbook,' make sure that 'Blank and Recent' is highlighted. You can also click on the button at lower right that reads 'Create.'

Read more ►

How to Calculate Time Duration in Excel 2007

1. Format the relevant cells as time by selecting them and choosing 'Time' from the Number group drop-down menu in the Home tab.
2. Enter a start time and an end time in different cells, such as the start time in cell A1 and the end time in cell B1. Ensure Excel hasn't misinterpreted either time, such as making '9:15 pm' read '9:15 am.' If necessary, type 'am' or 'pm' after the times or use a 24-hour clock, typing '21:15' instead of '9:15 pm.' Investigate Excel's Help menu on 'time of day' if necessary to learn more about these formats.
3. Subtract the start time from the end time to calculate the time duration. In this example, type '=B1-A1' and press 'Enter.'
4. Change the number format for the result, which automatically formats itself as a time of day, which is incorrect. For example, the duration between 2:00 pm and 2:30 pm reads '12:30 am,' which is 30 minutes after zero-hour, or midnight. Instead, choose 'Custom' from the Number group drop-down in the Home tab to set the format correctly.
5. Choose 'Time' in the left-hand panel of the resulting dialog box. Click '13:30,' the second option in the right-hand list, to display the duration in hours:minutes -- in this case, '0:30.' Click '37:30:55' for hours:minutes:seconds.

Read more ►

How to Learn Excel 2007

1. Purchase a copy of Microsoft Excel or download a 60-day free trial of Microsoft Office Professional, which includes Excel 2007.
2. Visit the Microsoft Office website to access dozens of free online Excel 2007 training courses. Take the free 40-minute course called 'Up to speed with Excel 2007.' This course teaches the basics, such as how to use common commands and save workbooks. Take the test at the end of the course to assess how much you have learned.
3. Take the online Excel course called 'Get to Know Excel 2007: Create your first workbook.' The course will teach you how to create a new workbook, how to enter and edit data, and how to insert columns and rows. Once you are finished with the self-paced course, open your copy of Excel 2007 and practice creating your own sample worksheet.
4. Learn to create charts in Excel. Participate in the free online training course called 'Charts 1: How to create a chart in Excel 2007.' Complete the practice assignments at the end of the course, which require you to make a basic chart using Excel 2007.
5. Visit the Excel Help Page on the Microsoft website if you don't have time to take an online course but want to learn about specific features of Excel 2007.

Read more ►

How to Install Analysis ToolPak for Excel 2003

1. Open any Excel document.
2. Navigate to the 'Tools' menu. Click on 'Add-Ins.'
3. Select the check box next to Analysis ToolPak under 'Add-Ins.' Click 'OK.'
4. Click 'Yes' to install Analysis ToolPak if you see the message 'Analysis ToolPak is not currently installed on your computer.' Analysis ToolPak will be installed.
5. Click on 'Tools' under the menu bar. Open a new Excel document. You will see that the data analysis command listed, which indicates the program has been added properly.

Read more ►

How to Combine Column and Scatter Charts in Excel

1. Open the Excel worksheet with the data you wish to chart.
2. Click and drag the cursor to select the data series and the categories.
3. Click the “Insert” tab on the command ribbon.
4. Click the “Column” menu to display a gallery of column thumbnail images.
5. Click the preferred image. The worksheet data converts to an embedded column chart.
6. Right-click on one data series in the plot area that you wish to convert to a scatter chart.
7. Click the “Change Series Chart Type” option. The “Change Chart Type” dialogue window opens with a gallery of chart images.
8. Click the “X Y (Scatter)” tab on the left pane. Click the preferred “X Y (Scatter)” image in the gallery.
9. Click “OK.” The 'Change Chart Type' dialogue window closes. A combination chart displays both the column and scatter charts on one plot area.

Read more ►

How to Change the Order of the Legend in an Excel Chart

1. Right-click on one of the names listed on your legend.
2. Click on the 'Select Data' option from the list that appears.
3. Click on the entry you want to move in the 'Legend Entries (Series)' box.
4. Click the 'Up' or 'Down' arrows in the box to change the position of the legend. Click 'OK' when finished to save your changes.

Read more ►

How to Create a Searchable Database in Excel

1. Type the following data into a new spreadsheet, pressing 'Tab' in place of the commas. This data is for a hypothetical art supply store. Click your mouse on the data's top left cell, then drag down to the bottom right cell to select the data. Type 'ArtProducts' in the left text box above the worksheet grid. This action names the table, which makes it easier to identify in database queries.product, pricepaintbrush, 1.98Gesso, 3.45
2. Click the 'File' menu's 'Close' command, then click 'OK' to indicate you want to save the workbook. Type 'ArtProduct' for the file name, then click 'Save' to save the workbook. Click the 'File' menu's 'New' command to create a new workbook, then click the 'Data' tab's 'Other sources' icon. Click 'Microsoft query.'
3. Click 'Excel files' in the 'Choose data' dialog box, then click 'OK.' Microsoft query will display a dialog box with which to choose an Excel workbook file. Navigate to and double click the workbook you saved in the previous step, then click 'OK.'
4. Click the arrow button in the 'Columns' dialog box to tell Excel you want to base your query on the 'ArtProduct' database listed in the left pane. Click 'Next,' then click the 'View data' option button. Microsoft Query's main window will open.
5. Click the 'View' menu's 'SQL' command, then type the SQL statement following this step into the new dialog box. This statement runs a query on your 'ArtProducts' database to select only those products whose price is greater than $2.00. Click 'OK' to perform the query. Excel will display only the 'Gesso' product, confirming that you've created an Excel database whose records you can selectively search with SQL commands from querying programs like Microsoft Query.SELECT *FROM ArtProductswhere ArtProducts.price>2.00;

Read more ►