Monday, November 28, 2011

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 ►

Sunday, November 27, 2011

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 ►

Saturday, November 26, 2011

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 ►

Friday, November 25, 2011

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 ►

Thursday, November 24, 2011

How to Print a Long Row on One Page in MS Excel


1. Highlight the row you want to print.
2. Select 'Page Layout' and then 'Print Area.'
3. Click 'Set Print Area.'
4. Select 'Scale to Fit' under the 'Width' and 'Height' drop-down menu.
Read more ►

How to Convert Microsoft Excel 2003 to 2007


Using Microsoft Excel XP or Excel 2003
1. Open a Web browser window and navigate to the Microsoft Windows Update website. Follow the on-screen instructions to download and install all high-priority updates for your operating system version.
2. Navigate to the Microsoft Download Center page for the Microsoft Office Compatibility Pack. Click the 'Download' button and save the installer file to your computer.
3. Close all open Microsoft Office programs.
4. Double-click the icon of the installer file you downloaded previously. Follow the on-screen instructions to install the Office Compatibility Pack on your computer. The software allows you to save files to XLSX format using Excel XP or Excel 2003.
5. Open Microsoft Excel XP or Excel 2003.
6. Click the 'File' drop-down menu and select 'Open.'
7. Locate the XLS file you want to convert and double-click its icon.
8. Click the 'File' drop-down menu again and select 'Save As.'
9. Give the file a name and select a save location. Choose the 'Excel 2007' option from the 'Save as type' drop-down menu. Click 'Save' to convert the XLS file to XLSX format.
Using Microsoft Excel 2007
10. Open Microsoft Excel 2007.
11. Click the round 'Office' button in the top left corner of the window and select 'Open.'
12. Locate the XLS file you want to convert and double-click its icon.
13. Click the 'Office' button again and select 'Save As.'
14. Give the file a name and select a save location. Choose the 'Excel Workbook' option from the 'Save as type' drop-down menu. Click 'Save' to convert the XLS file to XLSX format.
Using Zamzar
15. Open a Web browser and log on to the Zamzar website.
16. Click 'Choose File' and select the XLS file you want to convert.
17. Select 'xlsx' from the 'Choose the format to convert to' drop-down menu.
18. Enter your email address in the adjacent field.
19. Click 'Convert.' Your file will be converted to XLSX format within minutes. Once finished, a download link will be sent to the email address you provided.
Read more ►

How to Use Excel's MONTH Function


1. Learn the syntax of MONTH. It is MONTH (start_date) where start_date is the date for which you are trying to find the month.
2. Enter start_date using the DATE function or some other function that returns a date to prevent the problems that can occur when a date is entered as text. For example, DATE(2007,4,12) would be used for April 12, 2007.
3. Observe that Excel uses serial numbers internally to store dates. This is a number that represents the number of days beginning from a default date. Windows uses January 1, 1900, as serial number 1 and Excel for the Macintosh uses January 2, 1904, as serial number 1.
4. Examine the return values for the MONTH function. Excel returns the integers 1 to 12, representing the months in the Gregorian calendar, regardless of the display format of serial_number.
5. Study some examples of MONTH by entering 17-Apr-2008 as a date in cell A2. Example 1: =MONTH(A2) will return 4 because April is the fourth month of the year. Example 2: =MONTH(DATE(2007,3,12) will return 3. Note the use of the DATE function to ensure the argument for MONTH is in date format when entering it directly.
Read more ►

How to Edit Charts in Excel 2010


1. Open the spreadsheet containing your chart in Microsoft Excel 2010.
2. Click anywhere on the chart to enable the Chart Tools, which consist of the Design, Layout and Format menu tabs.
3. Click the 'Design' tab to edit the appearance of your chart. Clicking 'Change Chart Type' allows you to select a different format, such as Column, Line or Pie charts. To choose chart data or add a new data series, use the 'Select Data' group. Clicking an option from the Chart Layout group applies a design template that alters the positioning of labels and data. Clicking an option from the Chart Styles group changes the chart's color scheme.
4. Select the 'Layout' tab to change labels or add special formatting to the chart. The Insert group offers the option to add pictures, shapes and text boxes. The Labels group lets you change titles, labels and legends. The Analysis group allows you to add trend lines and error bars to the chart.
5. Click the 'Format' tab to access additional design features, such as borders, color fills and shape effects. Clicking an option from the WordArt Styles group changes all text on the chart. The Size group allows you to edit the horizontal and vertical size for the chart.
Read more ►

Wednesday, November 23, 2011

How to Remove Blank Cells in Excel 2007


1. Select the range of cells that contains blanks to remove.
2. Click 'Find Select,' at the far right of the Home tab.
3. Choose 'Go to Special....'
4. Click the 'Blanks' radio button in the resulting window.
5. Click 'OK' to close the window and highlight all blank cells in the selected range.
6. Click the 'Delete' arrow button on the Home tab and choose 'Delete Cells....'
7. Choose an option for filling in the blanks: Shift cells left or up, or delete entire rows or columns.
Read more ►

How to Insert a Degree Symbol in Excel


1. Turn on the number lock function on your keyboard's number pad by pressing the 'Number Lock' button. You can only enter the degree symbol by using the number pad (not the row of numbers above the letters.)
2. Click in the cell into which you wish to type the degree symbol.
3. Locate the 'Alt' key on your keyboard.
4. Hold down the 'Alt' key with your left hand, and type '0176' simultaneously on the number pad. You should see the degree symbol appear in your cell.
Read more ►

How to Create High Resolution TIF Files From Excel


1. Open Microsoft Excel by selecting it from the Start menu or double clicking on the Microsoft Excel icon on your desktop. Open the worksheet you want to convert by clicking 'File' and 'Open.' This will open a search window. Locate your document and then click 'OK.'
2. Edit your worksheet, if needed, until you have achieved the final version that you want to appear in the TIFF file. This is the time to hide any columns or rows you do not want to appear in the final document as well as add design elements such as shading and borders. Click 'File' and 'Print Preview' to preview the file and make sure it looks exactly the way you want.
3. Press the 'Print' icon on the upper left corner of the print preview screen to open the print dialog box. Click on the arrow next to the printer 'Name' box to select a different printer. Locate 'Microsoft Office Document Image Writer' and select. Then click the 'Properties' button to open the properties dialog box.
4. Select the 'Layout' tab on the upper left corner of the properties dialog box. Click the 'Advanced' button to open the 'Microsoft Office Document Image Writer Advanced Options' box. Under 'Output Format' select 'TIFF- Monochrome Fax.' Then click 'OK' to accept the option. Keep clicking 'OK' until you return to the Printer dialog box and then click 'OK' one last time to start the process. A small pop-up window will tell you that the document is printing. Then a 'Save As' dialog box will open.
5. Enter the name you want to save the file under in the 'Name:' box. Make sure the document is saving in the correct file location or click the down arrow next to the 'Save in:' box to select a new location. Then click 'Save' to finish the process. Your new TIFF file is now ready to be opened.
Read more ►

How to Delete Data in Excel Not Formulas


1. Click on the cell containing the data you wish to delete. Select more than one cell by holding down the 'Ctrl' key while clicking on the other cells.
2. Go to the 'Edit' menu at the top of the page and highlight the 'Clear' option.
3. Choose 'Contents' by clicking on it to delete only the data and not the formulas from the selected cells.
Read more ►

How to Make a Gantt Chart


Outlining Your Project
1. Document an overview or synopsis of the project before plotting a Gantt chart.
2. Make a list of the tasks required for completion of the project and the dates that each of these tasks needs to be completed by.
3. Create a new spreadsheet.
4. List each of the tasks that must be completed during the course of the project in the far left-hand column. Skip a row at the end of the list.
5. Place the dates of completion for each of the required tasks in a cell on the following row. Allow spaces between the date cells if they are needed for readability.
6. Select the cell that represents the start date of the task, click the 'Format Cells' function and fill the cell with color to indicate the beginning of a task. Repeat this action across the row. Click and drag the mouse over a group of cells to select the cells all the way to the project's end date. Each task needs to be plotted according to its beginning date and end date.
7. Format the text with bolding or italics; add headings and shading to enhance the visual appeal and readability of the chart; or create borders if appropriate.
Read more ►

Tuesday, November 22, 2011

How to Create a Form for the Web in Excel


1. Go into Microsoft Excel and open the worksheet you want to make into a Web form.
2. Open the Tools menu and select Wizard, then select Web Form.
3. Follow the instructions in the Web Form wizard.
4. In the second set of instructions, select which cell or range of cells you need data for.
5. Click Next.
6. Select your server type.
7. Click Next.
8. Enter the location and file name of the Web page you're adding the form to.
9. Click Next.
10. Enter your closing message. This message will appear after the Web user enters the required data.
11. Follow the instructions in the Web Form wizard.
12. Click Next.
13. Click Finish.
Read more ►

How to Set Up Monte Carlo in Excel 2003


1. Access the Monte Carlo program by either placing the CD in your computer's CD drive or by connecting to the program's network drive if the program is located on a network server. Wait a minute or two while the program loads onto your system.
2. Click 'Start' and go to 'All Programs.' Click on 'Microsoft Excel' to launch the program.
3. Click the 'Tools' tab on the main menu bar and choose 'Add-Ins.' The add-in manager will open. Check 'Monte Carlo' in the add-in list. If the program is not in the list, click 'Browse' and locate the 'MCSim.xla' file under 'Computer' in the removable storage section. Click 'OK.'
4. Click 'Yes' to copy the program to the Excel add-in folder. Monte Carlo is now set up in the Excel program. Click 'OK' to launch the add-in.
Read more ►

Monday, November 21, 2011

How to Convert New Excel From R1C1 to A1


1. Double-click the Excel file you want to work with to open it using Excel 2010.
2. Click the 'File' tab at the top of the Excel ribbon, then click 'Options' from the bottom of the list on the left side of the screen. The Excel Options window appears.
3. Select 'Formulas' from the list on the left side of the window, then look for the 'Working with formulas' area on the right side of the window. Click the check next to 'R1C1 reference style' to remove the check.
4. Click 'OK' to close the Excel Options window. Your spreadsheet now uses the A1 reference style and Excel automatically changes all the current references to the A1 style.
Read more ►

Sunday, November 20, 2011

How to Create a Pivot Table in Excel 2010


Use Excel Data
1. Open the Excel 2010 file that holds the data you want to make into a PivotTable.
2. Check the top of each column to ensure that your data has headers. If any columns do not have headers, type a word into the cell atop the column that briefly describes the data. If you don't have any headers at all, right-click on the row number for the top row in the data field and choose 'Insert' from the pop-up menu. You can then enter headers into the new row.
3. Click any cell within the data field. Select the 'Insert' tab at the top of the screen, and then click the 'PivotTable' button on the left end of the ribbon.
4. Choose whether to place the PivotTable on a new worksheet or an existing worksheet at the bottom of the small window that appears. If you choose an existing sheet, you will then need to click on the worksheet and cell where you want the PivotTable to go. Click 'OK' to continue and a blank PivotTable will appear.
5. Place a check mark next to any fields you want to include on the PivotTable on the 'Field List' that appears on the right side of the Excel window. As you add fields, Excel will automatically place them into one of the four PivotTable areas, represented by the four small boxes at the bottom of the 'Field List.' You can then drag and drop the fields from one box to another to change how they appear on the PivotTable.
Use External Data
6. Open the Excel 2010 spreadsheet where you want to create the PivotTable.
7. Select the cell where you want to insert the PivotTable. Select the 'Insert' tab and click the 'PivotTable' button.
8. Select the radio button next to 'Use an external data source.' Click 'Choose Connection,' located in the middle of the window.
9. Select your desired connection from the list of existing connections that appears. Click 'Open' to continue. If you don't see your desired connection, click the 'Browse for More' button and then locate the data source you want to use. Click 'Open' once you have selected the data file, and then 'Open' again.
10. Click 'OK' to create the blank PivotTable. Place check marks next to the fields that you want to add to the PivotTable and Excel will automatically assign them to one of the four PivotTable categories, which are located in four boxes beneath the field list. You can drag and drop fields from one box to another to alter how the PivotTable looks.
Read more ►

How to Use Excel's AveDev Function


1. Learn the syntax of AVEDEV. It is (number_1,number_2,...,number_n) where number_1,number_2,...,number_n are 1 to 30 arguments for which the average of the absolute deviations using the arithmetic mean will be determined.
2. Study the valid arguments for AVEDEV. They must be names, numbers or arrays and references that contain numbers. Logical values and text representations of numbers that are entered directly into the argument list also will be included. FALSE is implicitly converted to 0 and TRUE is implicitly converted to 1.
3. Examine the way AVEDEV uses arguments that are arrays or references to arrays. Empty cells, logical values and text will be ignored. Cells with the value zero will be included, however.
4. Determine the result for AVEDEV. It is determined by first calculating the arithmetic mean m where m = t/n such that t is the population total and n is the population size. The average deviation is then given by the sum of |x-m|/n for all members x of the population.
5. Look at simple example of AVEDEV.=AVEDEV(4,5,6,7,5,4,3) returns 1.020408. The arithmetic mean m is (4 5 6 7 5 4 3)/7 = 34/7 = 4.857143, so the average deviation is (|4-m| |5-m| |6-m| |7-m| |5-m| |4-m| |3-m|)/7 = 7.142857/7 = 1.020408.
Read more ►

How to Delete Redundant Cells in Excel


1. Right-click the Excel file you want to edit, and click 'Open With.' Click 'Microsoft Excel' in the list of programs.
2. Highlight all the cells you want to check for redundancy. You can highlight all cells at once by clicking the corner square in the upper-left corner of the spreadsheet.
3. Click the 'Data' tab and click the 'Remove Duplicates' button. A window opens prompting you for a list of columns on which you want to run the duplication utility.
4. Click the 'Select All' button if you want to check all columns. Otherwise, check each column you want to check.
5. Click 'OK' to remove the redundant cells. Excel returns a report of how many cells were deleted from the spreadsheet.
Read more ►

Saturday, November 19, 2011

How to Convert Lotus to Excel 2007


1. Open the file you need to convert in Lotus. Click on the 'File' menu button in the top bar then click on 'Save As.' This will open up a save dialog box.
2. In the dialog box, browse folders until you find the location you want to save the file in and rename the file. Click on the drop down menu next to 'Save as Type' and save the file as a .wk1, .wks, or .123 file. These are earlier versions of Lotus files.
3. Download the OpenOffice suite at openoffice.org. OpenOffice is a free set of office productivity programs which includes a word processor, spreadsheet, presentation, database, drawing program and a calculator.
4. Double click on the OpenOffice icon to bring up the launch screen. Click on the button 'Spreadsheet' to open the spreadsheet program.
5. Click on the 'Open' button in the top menu bar (it looks like an file folder). Search for the file you saved, click on it to select it, and then click 'Open' to open the file in OpenOffice.
6. Click on the 'File' menu in the top bar to open a drop down menu. Click on 'Save As' to open the 'Save File' dialog box.
7. Browse though the folders to determine where you would like this file to be saved and rename the file. Click on the drop down menu next to 'Save as type' to bring up a list of acceptable file types. Click on 'Microsoft Excel (.xls).' Click on 'Save.'
8. Open Excel 2007. Click on the Windows icon at the top left of the screen and click on 'Open.' Browse through the folders until you find the file you saved in OpenOffice, click on it to select it, and click on 'Open' to open it in Excel.
Read more ►

How to Calculate Discount Levels on an Excel Spreadsheet


1. Open Microsoft Excel. Click the 'A1' cell in the blank document. Type 'OP' in the cell to label the original price column.Click the 'B1' cell. Type 'SP' in the cell to label the sale price column.Click the 'C1' cell. Type 'Discount' in the cell to label the column that will show the discount level of the sale.
2. Click the 'A2' cell, and type in the original price of your item---the price the item sells for before tax. For example, type '50' if the original price of the item is $50.Click the 'B2' cell, and type in the sale price of your item---the price the customer actually pays, or the advertised price of the item. For example, type '20' if the sale price is $20.
3. Click the 'C2' cell. Type '=(B2-A2)/ABS(A2)'---the formula that will automatically calculate the discount level---into the cell.Click the 'D1' cell to apply the formula.Click the '%' sign under the 'Number' category in the top toolbar to change the number displayed to a percentage. Your percentage discount will now display. For example, if you entered '50' for the original price and '20' for the sale price, the discount column will show '-60%' to reflect a 60 percent discount on the item.
Read more ►

Friday, November 18, 2011

How to Repeat Header Rows in Excel


1. Launch Microsoft Excel and open the worksheet that you want to use.
2. Click the 'Page Layout' tab, and then click 'Print Titles' in the Page Setup group. The Page Setup window opens displaying the Sheet tab.
3. Click the 'Collapse Dialog' button in the 'Rows to Repeat at Top' box.
4. Select the row that contains the header you want to repeat, and then click the 'Collapse Dialog' button. The row reference automatically populates the 'Rows to Repeat at Top' box. Click 'OK' to save the settings.
Read more ►

How to Insert Drop


1. Open your workbook to a blank page or add a new worksheet. To keep your active worksheet tidy, you will want your list stored on a separate sheet from where the drop-down box is.
2. Enter your items for the drop-down list in a single column on the blank worksheet. For easy identification, add a title to your list in the row before your list begins, such as 'Colors' for a list of colors. Your list will appear in the order you have it in this column.
3. Highlight your list of items with your mouse, not including the title. With these highlighted, click in the box to the left of the formula bar and type in a name for your list. Use a name that matches your list title exactly, including case, to make the name easier to find if you have multiple lists. This process creates a name for your range of cells for the computer to identify. For example, if your list is titled 'Colors' in cell A1 and your list includes 'Red,' 'Blue' and 'Green' in cells A2 through A4, respectively, highlight A2 through A4 and type 'Colors' in the upper left box to name that cell range.
4. Press 'Enter' to set the name for your list. If you do not press 'Enter,' the name does not apply.
5. Select the worksheet where you want the drop-down list to appear, and click inside the cell where you want the list. Single-click in the cell only; you cannot add data validation while editing a cell, which is accessed by double-clicking.
6. Select the 'Data' tab on the Office ribbon, and then choose 'Data validation' in the 'Data tools' area.
7. Click 'List' from the drop-down menu under 'Allow' in the dialog box, and make sure 'In-cell drop-down' is selected.
8. Click inside the 'Source' box. Type an equal sign, and then type the list name exactly as you entered it on the other worksheet. For example, type '=Colors' (without quotation marks) for the list in the previous example.
9. Select 'OK.' The drop-down list is now a part of the cell, and when users click inside the cell, they will see the arrow indicating a drop-down list. Click the arrow to see the list, and then select an option to populate the cell.
Read more ►

How to Use a VLookup in Excel 2003 to Reference the Data in a CSV


1. Start Excel with a blank worksheet.
2. Click on the 'Data' menu, and select 'Import.' Select 'Text File' and navigate to where your CSV file is located.
3. Select 'Delimited' to identify the type of file. Select which row to start the import from; by default, Excel will import from the first row and work its way down. Click the 'Next' button.
4. Check the box for 'Commas' as the delimiter type on the next screen, then select 'Next.'
5. Select 'General' for the import rules; this is the most flexible option. You also have the choice of telling the import to exclude specific columns. When you've specified the import rules for each column, click 'Finish.' You'll be prompted to choose the upper left-most cell for the placed data; this defaults to cell A1. Excel will link to the CSV file, and every time Excel is re-opened, the imported file will update.
6. Press 'Ctrl End' to find the lower right most cell containing datat. If your imported file had 6 columns and 1000 rows, 'Ctrl End' would take you to cell F1000.
7. Enter the following formula in the blank cell of your choice:
=VLOOKUP(T1,A1:F1000,2,FALSE)
8. Enter the value you want to match in cell T1, and change the range of A1:F1000 to match the actual first and last cells in the VLOOKUP table range. The part of the formula with a '2' in it indicates it will return the value found in column 2; change this to reflect the column of the data you're interested in. FALSE indicates that it's looking for an exact match on the lookup value
Read more ►

How to Delete Blank Rows in Excel


1. Make a backup copy of your spreadsheet. This is most important during the learning process.
2. Select the entire area that contains the blank rows that you want removed. For example, if you have data in rows 1 through 30, you can click on 'Row 1' then press 'Shift' and click on 'Row 30.' To select the entire spreadsheet, press the 'Ctrl' and 'A' keys simultaneously.
3. From the 'Edit' field, select 'Go To.' Alternatively, you can press the 'Ctrl' and 'G' keys simultaneously.
4. Click 'Special...'
5. Select the 'Blanks' radio button and click 'OK.'
6. From the 'Edit' menu, select 'Delete...'
7. Select 'Entire Row ' and click 'OK.'
Read more ►

Thursday, November 17, 2011

How to Hide the Tool Bar Menu in Excel


1. Click the icon consisting of a down-facing arrow with a short horizontal line above it. In Excel 2007, this icon is located near the upper-left corner of the screen. In previous Excel versions, it is located near the upper-right corner.
2. Click 'Minimize the Ribbon' in Excel 2007. This hides the top toolbar. You can bring the toolbar back permanently by repeating Steps 1 and 2, or temporarily by clicking one of the menu options at the top of the screen, e.g. 'Data' or 'View'. If you are using Excel 2007, stop here. If you are using a previous version of Excel, click 'Add or Remove Buttons,' then click 'Customize.'
3. Click the 'Toolbars' tab, then remove the check from each box displayed below, e.g. 'Standard' and 'Formatting.' The toolbars disappear in real time as the checks are removed.
4. Click the 'Close' button to save your changes after removing the desired toolbars. You can bring toolbars back by right-clicking the open gray area at the top of the screen, then clicking 'Customize.'
Read more ►

How to Do Trend Charts


Insert Chart
1. Open the saved Excel worksheet.
2. Click the 'Insert' tab on the command ribbon.
3. Click and drag the cells with the categories and numeric data.
4. Click the lower-right arrow for the Charts dialog box launcher. A chart gallery appears.
5. Select a chart type that best communicates your numeric data: 'Area,' 'Bubble,' 'Bar,' 'Column,' 'Line,' 'Stock' or 'xy (scatter). Select an unstacked, 2D chart type.
6. Click 'OK.' The chart type appears over the worksheet.
Add Trendline to Chart
7. Click in the chart area. The Chart Tools ribbon appears with three tabs: Design, Layout and Format.
8. Click the 'Layout' tab.
9. Click the arrow to the right of the Chart Elements text box in the Current Selection group.
10. Click the preferred chart element to appear in the chart area. For example, click 'Legend' to insert a legend box.
11. Click the 'Trendline' button in the Analysis group. A list of options appears.
12. Click the 'More Trendline Options' link. The 'Format Trendline' dialog window opens.
13. Click the 'Trendline Options' button in the left pane. The right pane displays a trendline gallery.
14. Click one radio button in the Trend/Regression Type section: 'Exponential,' 'Linear,' 'Logarithmic,' 'Polynomial,' 'Power' or 'Moving Average.'
15. Select the options for 'Trendline Name,' 'Forecast' and other trendline details. The trendline appears on the chart.
Read more ►

How to Set the Default Font Size in Excel 2003


1. Open Excel 2003 and select the 'Tool' on the menu bar. Select 'Options.' The Options dialog box appears.
2. Click the 'General' tab. Select a font in the 'Standard Font' drop-down list. Select a size for the font using the 'Size' drop-down list.
3. Click 'OK' to confirm your changes. Excel will prompt you to restart for your changes to take place. Once you restart Excel, your new worksheets will be based on this new font.
Read more ►

How to Reset the Autofilter Area in MS Excel


1. Open the worksheet in which you would like to clear the filter.
2. Click the 'Filter' button on the column heading. It looks like a funnel in Row 1.
3. Click 'Clear Filter from
.' For example, if you have a filter in column A, click 'Clear Filter from A.'
Read more ►

How to Create Multiple Charts in Excel


1. Open up a new spreadsheet in Microsoft's Excel program.
2. Enter your data in the columns provided in the spreadsheet. For example, let us say you have three sets of data. You will use columns A, B, and C to record your data. Simply click on A1, and type your first number. Hit Enter, and your cursor will move to A2. You will follow this process until you have entered all three sets of data.
3. Click cell A1. Then, press Control and the letter A at the same time. Your three sets of data should be highlighted.
4. Click Insert on your toolbar. Experiment with different types of charts. You have the choices of Column, Line, Pie, Bar, Area, Scatter and Other Charts. Simply click the chart style you like, and it will appear on your screen.
Read more ►

How to Add Subtotals in Excel


1. Create the list of numbers to be added. Make sure to label the top of each column with a heading.
2. Highlight the column of numbers to be added by clicking on the number at the top of the column. Once all of the data is outlined and highlighted in blue, the numbers are ready to be added.
3. Go to the 'Data' tab at the top of the Excel toolbar.
4. Select 'Subtotal' from the 'Outline' section, which is on the far right side of the toolbar.
5. Confirm the column being added. Make sure that the function is set to 'Sum.' If it isn't, select 'Sum' from the 'Use Function' drop-down menu. Click 'OK' at the bottom off the confirmation screen to add the subtotals.
Read more ►

Wednesday, November 16, 2011

How to Restore Previous Excel Files


Files Never Saved
1. Open Excel.
2. Click 'File,' then 'Recent'.
3. Select 'Recover Unsaved Workbooks'.
4. Select your file and click 'Open'.
5. Save your file.
Previously Saved Files
6. Open your file.
7. Click 'File' then 'Info'.
8. Click 'Versions' and select the one labeled 'when I closed without saving.'
Read more ►

How to Find Copy Results in Excel 2003


1. Open the Excel 2003 spreadsheet in which you want to locate and copy data.
2. Type 'Ctrl' 'F' on your keyboard to open the 'Find' dialog box. Alternatively, click the 'Edit' pull-down menu near the top of the spreadsheet and select 'Find' from the list.
3. Type the data you want to find in the 'Find What' box. You can enter numbers or letters or a combination. Use the 'Options' menu to select special features, such as enabling the 'Match case' option to distinguish uppercase and lowercase letters in your search.
4. Click 'Find Next' to locate the next appearance of your search term in the spreadsheet. Alternatively, click 'Find All' to generate a list of all appearances of your search term and click an item in the list to go to that location in the spreadsheet.
5. Type 'Ctrl' 'C' on your keyboard to copy the contents of the cell you are in. Alternatively, click 'Edit' to open the pull-down menu and click 'Copy.'
6. Move your cursor to another cell, a different spreadsheet or document in another program and type 'Ctrl' 'V' to paste the copied data into the new location.
Read more ►

How to Edit the Legend in Microsoft Excel


Microsoft Excel 2007
1. Highlight a chart by clicking on it.
2. Click on the “Design” tab near the middle of the top of the page.
3. Click on “Select Data”, the fourth icon from the top left of the page. A box titled “Select Data Source” will appear.
4. Click on the name of the “Legend entry” that you wish to edit.
5. Press the “Edit” button. The “Edit” button is located directly above the list of the names of the legend entries in your chart. When you press the “Edit” button, a box titled “Edit Series” will appear.
6. Type the name that you wish to title the “Legend entry” into the box titled “Series name”.
7. Click “OK”. This will edit the legend and return you to the “Edit Series” box.
8. Add another “legend entry” to the legend by clicking on the “Add” button. The “Add” button is located directly to the left of the “Edit” button. When you press the “Add” button, another box titled “Edit Series” will appear.
9. Type the name that you wish to title the new “legend entry” into the box titled “Series name.”
10. Insert the values for the x-coordinates in the “Series X values” box.
11. Insert the values for the y-coordinates in the “Series Y values” box.
12. Click “OK”. This will edit the legend and return you to the “Edit series” box.
13. Repeat this process if you wish to add or edit any additional legend entries.
14. Click “OK” when you are finished.
Microsoft Excel 2003
15. Highlight a chart by clicking on it to modify its legend. A menu will appear.
16. Select “Source Data…” from the menu. It is the third option from the top of the menu. A box titled “Source Data” will appear.
17. Click on the tab labeled “Series”. It is one of two tabs displayed at the top of the “Chart Wizard” box.
18. Highlight the “Data series name” you wish to change.
19. Place your cursor in the text box titled “Name.” Type the name that you wish to title the “Legend entry” into the box.
20. Press the “Finish” button when you are done.
Read more ►

How to Change the Source of an Excel Pivot Table Using VBA


1. Create a PivotTable object in VBA so that you can update the source. Copy the code below and put it in your macro or function.Dim ptMyPivotTable As PivotTable
2. Connect your PivotTable object with your actual Pivot Table. Copy the code below.Set ptMyPivotTable = ActiveSheet.PivotTables(1)
3. Update the SourceData property of your PivotTable object to the new values. You can do this by copying the VBA code provided. Substitute your desired data range in place of 'A1' in the example.ptMyPivotTable.SourceData = Range('A1').CurrentRegion.Address(True, True, xlR1C1, True)To reference a range on a different sheet, you can use the following code.ptMyPivotTable.SourceData = Sheets('mySheetName').[A1].CurrentRegion.Address(True, True, xlR1C1, True)
4. Update the data actually displayed in your Pivot Table by using the VBA command below.ptMyPivotTable.RefreshTable
5. Release the resources you used in your PivotTable object by setting it equal to Nothing.Set ptMyPivotTable = Nothing
Read more ►

How to Convert Integers to Minutes Seconds in Excel


1. Double-click the Microsoft Excel 2010 icon on your computer's desktop with the left mouse button. This will open the program and display a blank spreadsheet.
2. Double-click a cell on the blank spreadsheet that appears after the program opens with the left mouse button. This will place the text cursor into the cell and allow you to enter data.
3. Press the '=' key on your keyboard. This informs Microsoft Excel 2010 that you would like to create an equation in that cell rather than simply display text.
4. Enter the integer you wish to convert to a minutes-and-seconds format using your keyboard.
5. Press the '/' key on your keyboard. This represents the division mathematical function.
6. Type '86400' using your keyboard. This represents the number of seconds in a day.
7. Press the 'Enter' key on your keyboard. This will calculate the equation and convert your integer into a decimal number.
8. Right-click the decimal number calculated in step seven with the right mouse button. This will automatically open a pop-up menu.
9. Single-click the 'Format Cells...' option from the pop-up menu to open the 'Format Cells' screen.
10. Single-click the 'Custom' option listed in the 'Category:' section on the left side of the 'Format Cells' screen with your left mouse button. This will automatically cause the 'Type:' section to appear.
11. Single-click the 'mm:ss' option listed in the 'Type:' section with your left mouse button. This option represents a format of minutes, followed by a colon, then seconds.
12. Single-click the 'Ok' button on the lower-right side of the 'Format Cells' screen. This will close the 'Format Cells' screen and automatically return you to your spreadsheet. The cell you entered data into will now display your integer in a minutes-and-seconds format.
Read more ►

Tuesday, November 15, 2011

How to Add Percentages to a Chart in Excel


1. Open the Excel 2010 spreadsheet where your chart is located.
2. Click on the first cell in the empty column -- or row if your data is arranged by rows -- next to the data table that makes up your chart. Type in the name of the column or row that you want to display as percentages in your chart, followed by the word 'percentage' or simply the '%' sign.
3. Enter the following formula into the cell just below, or to the right of, your header cell:=A2/sum(A:A)Change 'A2' to the first cell in the column or row that contains the data series you want to display as percentages. Change 'A:A' to the column letter or row number of the column or row where the targeted data series is located. Press 'Enter' to complete the formula.
4. Move your mouse over the fill handle in the lower right corner of the cell with your formula in it. Click and hold the mouse button, then drag your mouse toward the end of your data table. When you reach a cell that is level with the last cell in your data table, release the mouse button. Excel fills the entire row or column with your formula.
5. Click on the column letter or row number where your formulas are located. Click the 'Home' tab, then click the drop-down arrow in the 'Number' area of the ribbon. Choose 'Percentage' from the drop-down menu. The numbers in that column or row all turn into percentages.
6. Click on the data series on your chart where you want to add your percentages, then click the 'Layout' tab at the top of the screen, followed by the 'Data Labels' button in the Labels area of the ribbon. Choose one of the four data label options available in the drop-down menu.
7. Click on the data label attached to the first data point in your chart, then click it again to edit the label. Place your cursor into the formula bar at the top of the spreadsheet. Type in a '=' sign into the formula bar, then click on the cell from the series of percentages that you created which contains the percentage for the selected data point.
8. Press 'Enter.' The percentage appears on your chart. Repeat this process for every data label on your chart.
Read more ►

How to Use Excel's SUBTOTAL Function


1. Learn the syntax for SUBTOTAL. It is SUBTOTAL(function_number, reference_1, reference_2,...reference_i...,reference_n) where function_number is the number corresponding to the function to use for calculating the subtotal and reference_i are up to 29 references for which the subtotal will be calculated.
2. Examine the following function numbers and their corresponding functions: 1, AVERAGE; 2, COUNT; 3, COUNTA; 4, MAX; 5, MIN; 6, PRODUCT; 7, STDEV; 8, STDEVP; 9, SUM; 10, VAR; and 11, VARP. Note that these function numbers will include hidden values. Add 100 to these function numbers if you want the function to ignore hidden values.
3. Expect nested subtotals within the references to be ignored by the SUBTOTAL function to avoid counting them twice. Rows that are not included in a filter's result also will be ignored. SUBTOTAL will return the #VALUE! error value when any reference is a 3-D reference.
4. Enter the following values into the first column of an Excel spreadsheet:Row 1: Data;
Row 2: 110;
Row 3: 15;
Row 4: 165.
5. Look at some examples of SUBTOTAL based on the entries made in Step 4: =SUBTOTAL(9,A2:A4) will evaluate as SUM(110,15,165) or 290 because function 9 is the SUM function; =SUBTOTAL(1,A2:A4) will evaluate as AVERAGE(110,15,165) or about 96.67 because function 1 is the AVERAGE function.
Read more ►

How to Format Numbers in a Concatenate Function in Excel 2007


1. Click the cell in which you want to insert the concatenate function.
2. Type the function in this format:CONCATENATE('This is the contents of cell A1: ', A1, '. And this is the contents of cell B2: ', B2, '.')Anything you put in quotes, including numbers, will be reproduced as you wrote it. Outside of quotes, strings (like A1 above) will be interpreted as cell numbers, and the concatenate function will fill in the contents of that cell. For example, if cell A1 contains the number '123,' and B2 the number '456,' the above function would produce:This is the contents of cell A1: 123. And this is the contents of cell B2: 456.Remember, if you want to use fixed numbers in a concatenate function, enclose them in quotes.
3. Press 'Enter' to complete the addition of the function.
Read more ►

How to Define Cell Names in Excel 2003


1. Go to the Start menu and open Excel.
2. Decide which cells you would like to define. You can do this by highlighting them with your mouse. If you want to define multiple cells that are not next to each other, click on each of them and hold down the 'Ctrl' button.
3. Click on the name box, which is is directly above the 'A1' cell. Once you click on the cell, it will be ready for you to type the new name.
4. Type the new name for the cell(s). For example, if you are making a budget and you want to name the cells accordingly, you might name them 'debit' or 'bill.'
5. Press 'Enter' to save the name into the name box. If you forget to do this, your name will be lost.
6. Use the names to make your formulas simpler. Now you can use 'debit' as a part of your next formula. This simplifies everything.
7. Visit the Help section of Microsoft.com for more information on how to make formulas. There are a few things that you need to know to make your formulas work properly.
Read more ►

How to Replace All Occurrences of a Word in a Microsoft Word Document


1. Scan your document until you find an occurrence of the word that you want to replace.
2. Click your mouse to highlight the word.
3. Click the 'Home' tab at the top of the window.
4. Click 'Replace' in the 'Editing' section of the Ribbon at the top of the window.
5. Click inside the 'Replace With' field at the bottom of the window.
6. Type the word that you want to use as the replacement word, then click 'Replace All.'
Read more ►

How to Insert Page Breaks in Microsoft Excel 2003


1. Select the area where you wish to insert page breaks. To do this you will need to left-click on the area of the spreadsheet where you would like to insert a page break.
2. Insert the desired page break. Scroll to the “Insert” tab on the command bar and select “Page Break” to insert a page break.
3. Check “Print Preview” to see and edit page breaks. Scroll the “File” tab on the command bar and select “Print Preview” which will show you where your page breaks occur and how they will affect the printed spreadsheet. To see page breaks, left-click on the “Page Breaks Preview” and it will show you where your page breaks are.
4. Adjust page breaks as necessary. You can adjust the page breaks in the page breaks print preview menu simply by left-clicking on the corners and holding while dragging the page breaks where desired.
Read more ►

How to Add Chart Legends in Excel 2010


1. Double-click the Excel 2010 file that you want to work with to open up the spreadsheet.
2. Click anywhere on the chart to select it. Then click the “Layout” tab at the top of the screen.
3. Click the “Legend” button, which is located in the Labels area of the ribbon. Choose one of the four direction buttons to place the legend above, to the right, to the left or below the chart. Alternatively, click either of the “Overlay” buttons to place the legend at the right or left side of the chart, overlapping the chart itself.
4. Right-click the legend on the chart, and choose “Format Legend” from the pop-up menu. The Format Legend window will appear.
5. Select any of the options on the left side of the window other than “Legend Options” to adjust how the legend will appear on your chart. These settings will let you add a filled-in background, adjust the border color or style, add a shadow element and adjust the edges of the legend. Click “Close” when you are done adjusting the legend’s appearance.
Read more ►

Monday, November 14, 2011

How to Make a Grid in Excel


1. Decide on the size of each cell, and how many rows and columns to use. This example creates a grid of cells that is nine rows long and seven columns wide with cells measuring 1-inch square.
2.
Click on cell A1 in the upper left and while holding the left mouse button, drag the selection to cell G9 in the lower right.
3. From the 'Format' menu, choose 'Row,' then 'Height' to display the 'Row Height' dialog box. Excel measures the row height in points, with roughly 72 points per inch. Enter a row height of 72, then click 'OK' to close the dialog.
4. From the 'Format' menu, choose 'Column,' then 'Width' to display the 'Row Width' dialog box. Excel measures the column width in the number of standard characters that fit into the width, with roughly 13 characters per inch. Enter a row width of 13, then click 'OK' to close the dialog.
5.
If the grid is too big to fit on one page, reduce the cell dimensions or adjust the page numbers by choosing the 'File' menu, then 'Page Setup.' In this case, the left and right margins of the page are reduced to 0.5 inches each.
6. With the cells still selected, choose the 'Format' menu, then 'Cells' to display the 'Format Cells' dialog box.
7.
Click the 'Borders' tab.
8. Click on a 'Style' in the right 'Lines' box to define the border appearance.
9.
In the left 'Presets' box, click the 'Outline' and 'Inside' buttons to draw borders on the grid.
10. Save or print the grid as needed.
11.
Read more ►

How to Convert XPS Files


1. Download and install an XPS file converter. You can download such a program from sites like 'Verydoc.com/', 'NovaPDF.com' and 'OpenXML.biz/XPSconvert.html'.
2. Launch the XPS converter program. Most likely, there will be a new desktop icon for the program. If not, you can find it under 'Start,' 'All Programs'.
3. Click and drag the XPS file into the main viewing area of the converter program.
4. Select an output location. Depending on the program you are using, there most likely is a browse button that allows you to select any location on your computer as a save location.
5. Select an output format, if you are given the option. Most XPS file converters simply convert the file into a PDF. Click 'OK' and the file is converted to the desired file format.
Read more ►

How to Learn Excel VBA Online


1. Learn Excel VBA. Look for nonmacro solutions before you start messing with VBA. If you don't know Excel's capabilities well, you're likely to write macros that do things you could accomplish instead with good spreadsheet design and a few mouse clicks.
2. Navigate to Microsoft's VBA for Excel 2003 training page. There is no equivalent page for Excel 2007, but you won't need version-specific knowledge until you've mastered the fundamentals of VBA.
3. Watch the introductory videos and work through the textual tutorials that introduce programming with VBA. There are three courses. Complete them, do the practice sessions and take the tests.
4. Complete the VBA tutorials at xlpert.com and xl-vba.com. Xlpert offers a free eight-lesson course, while xl-vba's course has 23 lessons. The writing doesn't sparkle and there's a lot missing (to encourage you to buy a complete course), but the material will reinforce what you learned from Microsoft's site. Repetition is useful when learning a new language.
5. Build a spreadsheet that does something useful for you, then turn it into a macro-driven application. When you feel stymied--and you will from time to time--press the F1 key to activate Excel VBA's help system. You can learn VBA entirely from the help system, but it would be very hard if you're not already a programmer.
6. Visit Microsoft's developers' forums to research problems and ask questions of other programmers. A link below leads to the forums page where you'll find a list of several Excel-related discussion groups.
Read more ►

How to Define a Table in Excel 2007


1. Start off by doing one of the following: Select the range of cells that you want to make into a table within your worksheet then proceed to Step 2 OR Start immediately with Step 2.
2. Click on the 'Insert' tab at the top of the document.
3. Find the 'Tables' group, then click on 'Table.' The 'Create Table' dialog box will appear.
4. Type in a range for your table if you did not select a range in Step 1. If you did already select a range then that range will automatically appear in the dialog box.
5. Check the 'My Table Has Headers' box if the data set you selected already contains the headers you want to use. If you don't check this box, the table will display default header names, which you can then go in and change.
6. Hit the 'OK' button at the bottom of the Create Table dialog box to create your table.
Read more ►

Sunday, November 13, 2011

How to Calculate Population Standard Deviation Using Excel


1. Add the numbers in the first column of your Excel 2007 document. For example, add the numbers 15.4,13.2,11.1,12.5,11.3 and 13 in A2, A3, A4, A5, A6 and A7.
2. Click on the 'A9' cell for this example. This is the cell where you will calculate the standard deviation. When you calculate another standard deviation, choose any cell at the bottom of the list of numbers you are using.
3. Click on the 'Formulas' tab while you are in the 'A9' cell. Click on 'Statistical' on the drop-down menu.
4. Click 'Insert Function' on the top left-hand side of the Excel spreadsheet. The 'Insert Function' window will pop open.
5. Click on the drop-down menu of 'Or select a category.'
6. Scroll down the 'Select a function' window. Choose 'STDEV,' which is the function of standard deviation based on the sample.
7. Click 'OK.' The 'Functions Arguments' window will pop open. Ensure that on 'Number 1' cell, A2:A7 is populated. If A2:A7 is not populated, enter A2:A7 manually. Click 'OK.'
8. The standard deviation has been successfully calculated in the 'A9' cell. In this example, the calculated value of the standard deviation is 1.560449.
Read more ►

How to Insert a Checkbox in Excel 2007


1. Click on the 'Developer' tab.
2. Click 'Insert' then 'Check Box' under 'Form Controls.'
3. Click where you want the check box to appear on the spreadsheet.
4. Click 'Properties' on the 'Developer' tab. Edit the properties you want to have on your check box.
Read more ►

How to Select Multiple Entries From the List in Excel 2003


1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office' in the list of programs, then click 'Microsoft Excel' to open the software.
2. Click the 'File' ribbon tab at the top of the Excel software. Click 'Open' to view the 'Open' dialog window. Double-click the Excel file you want to use.
3. Click the first cell you want to select. Hold the 'Ctrl' key and use the mouse to click each cell you want to select.
4. Right-click one of the selected cells and select 'Copy.' The cells' contents copy to the Windows clipboard. You can now move the content to a new program window or copy the cells to a new spreadsheet.
Read more ►

Saturday, November 12, 2011

How to Align Decimal Points in a Word Table


1. Open the saved Word table.
2. Click the “View Ruler” button at the top of the right vertical scroll bar to display the horizontal ruler and left ruler near the document.
3. Click the “Tab” selector at the top of the left ruler to bring up the “Decimal” tab. The “Decimal” tab symbol displays one horizontal line, one vertical line and a decimal on the right side. Usually, this “Decimal” symbol appears on the third click.
4. Click and drag the cursor on the column that will contain the decimal tab stop. This column will appear highlighted.
5. Click the horizontal ruler where you wish to insert the decimal tab stop. A dashed vertical line will temporarily appear where you clicked.
6. Click in the cell. A vertical bar will indicate where the decimal will line up.
7. Type the number with the decimal point. Continue typing numbers in the column. The decimal points will line up vertically.
Read more ►

How to Change Default Fonts in Excel


Change the Font Attributes in a Worksheet
1. Select the cell, or group of cells, that you want to format. To select one cell, place the mouse pointer on the cell and click the left mouse button. To select a group of adjacent cells, click on one of the cells and drag the mouse pointer to highlight all the cells in the group.
2. Locate the font box on the tool bar. Click the arrow on the right side of the box to see the drop down menu. The menu contains the various fonts.
3. Change the default font. Use the scroll bar on the right side of the menu to see all the fonts available. Click on a font to select it.
4. Change the font size. Click on the font size box, located on the right side of the font box, to select a new font size. You can also increase the font size with the 'Increase Font Size' button or decrease it with the 'Decrease Font Size' button.
Change the Font Attributes in a Workbook
5. Open the 'Options' dialog box. Click on 'Tools' on the tool bar and select 'Options.' This opens the Options dialog box in which you can change the default attributes of your workbook.
6. Change the default font. Click the 'General' tab and then click the arrow in the 'Standard font' field. Select a new font from the list.
7. Change the font size. Click the 'Size' box and select a new font size.
8. Click 'OK' when you have finished making your changes. You will have to restart Excel in order for the changes to take effect.
Read more ►

How to Install Word, PowerPoint and Excel Only


1. Place your Microsoft Office disc into the optical drive of your computer to begin the installation.
2. Enter the Microsoft Office product key in the field and click 'Continue.' Accept the software agreement and again press 'Continue.'
3. Click the button 'Customize' and select the tab 'Installation Options' to display the list of all programs included in the Microsoft Office package.
4. Right-click on each program in the list except Microsoft Word, Excel and PowerPoint, and select the option 'X Not Available' from the menu. This will prevent those unneeded programs from installing
5. Click the button 'Install Now' on the bottom of the installation window.
6. Restart the computer when prompted to finish up the Microsoft Office installation process.
Read more ►

Friday, November 11, 2011

How to Disable a Macro in Excel 2003


1. Open Excel 2003. Select 'Tools' on the menu bar. Click 'Macro' and 'Security.' The Macro Security dialog box appears.
2. Select 'High.' This will disable all macros that are not from a trusted source. Select 'Very High.' This will only allow macros to run if they are in a trusted location. So using these two choices will disable any macros that are not from trusted source or in a trusted location.
3. Click 'OK' to confirm the macro security changes in Microsoft Excel 2003.
Read more ►

How to Print a Large Excel Spreadsheet


1. Set the print area by selecting the top left corner of the area you wish to print, and dragging to the bottom right. This will create an outline around the area you wish to print. Click 'File,' 'Print Area' and 'Set Print Area.'
2. Click 'File' and 'Print Preview.'
3. Set the page size to the paper you will be using. The standard is A4.
4. Set the scaling. Try checking 'Fit to one page(s) wide by one page(s) tall' box. If the percentage showed in the toolbar changes to below 40 percent, the text will probably be too small to read. If not, click on the page to view a print preview and check if the spreadsheet is big enough to be understood. Choose a bigger percentage if not.
5. Reset the margins. You may need to click the 'Margins' icon in the toolbar to show the margins if they do not show up automatically. Drag along the margin to shrink it, and create more usable space.
6. Click 'Print.'
Read more ►

How to Auto Fill in a Cell in Excel


1. Click the first cell that you want to include in the data range.
2. Type the first value for the series.
3. Type the second value in the next cell to establish a pattern.
4. Click the first cell and drag your mouse to the second cell to highlight both cells.
5. Hover your mouse over the Fill Handle, located in the lower-right corner of the highlighted cells.
6. Drag the Fill Handle across the range of cells that you want to auto fill.
Read more ►

How to Make a Drop


1. Type the entries you want in the drop-down box. Place one word in each cell in a single column. For example, you may want a drop-down box in cell A1 limited to red, yellow and blue. Type 'Red' in cell 'B1,' 'Yellow' in cell 'B2' and 'Blue' in cell 'B3.'
2. Click on 'Data > Validation > Settings' in Excel 2003 or 'Data > Data Validation > Data Validation' in Excel 2007.
3. Click on 'List' in the Allow box.
4. Enter the location of the inputs for the list box. In the above example, the inputs are in cells B1 to B3, so enter '=B1:B3.' Click on the 'In-cell drop-down' box if it isn't already checked.
5. Click on 'OK.'
Read more ►

Blogger news