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...
Browse » Home » Archives for November 2011
Monday, November 28, 2011
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...
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 wo...
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...
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...
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 cos...
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...
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...
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 'Creat...
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...
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...
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 proper...
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)”...
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 chang...
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.452. 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...
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 me...
How to Convert Microsoft Excel 2003 to 2007
Using Microsoft Excel XP or Excel 20031. 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...
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,...
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...
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 colum...
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 ce...
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...
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 cel...
How to Make a Gantt Chart
Outlining Your Project1. 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...
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...
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...
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...
Sunday, November 20, 2011
How to Create a Pivot Table in Excel 2010
Use Excel Data1. 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...
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...
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...
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...
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...
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 settin...
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...
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....
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...
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,'...
How to Do Trend Charts
Insert Chart1. 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 Chart7. Click in the chart area. The Chart Tools ribbon appears with three tabs: Design, Layout...
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 fo...
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 ...
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...
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...
Wednesday, November 16, 2011
How to Restore Previous Excel Files
Files Never Saved1. 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 Files6. Open your file.7. Click 'File' then 'Info'.8. Click 'Versions' and select the one labeled 'when I closed without savin...
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...
How to Edit the Legend in Microsoft Excel
Microsoft Excel 20071. 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...
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 PivotTable2. 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...
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....
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...
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...
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...
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...
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 Al...
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...
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”...
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,'...
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...
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...
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...
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...
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 b...
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...
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...
How to Change Default Fonts in Excel
Change the Font Attributes in a Worksheet1. 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...
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 installing5. ...
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 20...
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...
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 fi...
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...
Subscribe to:
Posts (Atom)