1. Select the cell where you want your hyperlink to be placed. You can either insert a hyperlink into a blank cell or into a cell already filled with information. If the cell is blank, the hyperlink is displayed by its file or Internet location unless you specify different text later. If the cell is already filled, the text will be the link.2. Choose 'Hyperlink' from the 'Insert' menu. A hyperlink wizard pops up.3. Note the 4 icons on the left side of the wizard. These icons represent the 4 types of hyperlinks you may insert into Excel. The 'Existing File or Web Page' option is probably...
Browse » Home » Archives for May 2013
Tuesday, May 28, 2013
Monday, May 27, 2013
How to Sort Information in Excel 2007
1. Access a worksheet with data that can be sorted. Open Excel 2007 and either open an existing worksheet or add information into an empty worksheet. Data should be in a table, preferably with assigned column headers to make selecting the sorting criteria easier. Make sure that all of the data in a given column is of the same format so that it is sorted consistently.2. Choose the data to be sorted. Left-click and drag to select the data. Ensure that both the data and the headers are included.3. Open the sort dialog. Select 'Data' on the Excel 2007 ribbon, and then click on the 'Sort' icon.4....
How to Set up a PivotTable
1. Open the Excel worksheet that contains the data you want to make a PivotTable out of.2. Look through your data and make sure the top cell in every column is the column title. If any of the top cells are empty, add a title that will tell you what the data in that mean. If none of your columns has a titles, right-click on the '1' on the left side of your screen and choose 'Insert.' A new row will appear at the top of the page. Enter column titles into this row.3. Click on the top-left cell in the range of data you want to use for the PivotTable. Scroll to the bottom-right cell in the field,...
Sunday, May 26, 2013
How to Import Delimited ASCII127 in Excel
1. Run the Excel 2010 application.2. Click the 'File' tab and click 'Open' to open a file browser window. Click the 'Files' drop-down list, and choose 'Text Files.'3. Navigate the file browser to the delimited ASCII TXT file that you wish to import, then click 'Open.' Excel will initiate the Import Text Wizard.4. Click the radio button next to 'Delimited,' and click the 'Next' button.5. Click the radio button next to the character that the ASCII file uses for delimiting fields. If your delimiting character is not in the list, then click 'Other,' and type the character into the adjacent...
How to Remove an Unknown Password From a Protected Excel Spreadsheet
1. Download an Excel password recovery software, such as Free Word/Excel Password Recovery Software, Excel Password Remover 2008, or Password Recovery Engine for Excel (see resources). Save the file, then click 'Install' at the prompt.2. Open the application. Click 'Open,' followed by 'Browse.' Select your protected Excel spreadsheet from the menu. Click 'Recover' and wait for the program to recover your password.3. Open the Excel spreadsheet using the password. From the 'File' menu, select 'Save As.' Under 'Tools,' select 'General Options.'4. Double-click the asterisks in both the 'Password...
How to Convert Microsoft Project to Microsoft Excel
1. Double-click on your Microsoft Project file.2. Click 'File' at the top of the screen (Project 2003 or earlier) or the Windows button in the top-left corner (Project 2007).3. Choose the 'Save As' option (Project 2003 or earlier) or 'Save As,' and then 'Other file formats' (Project 2007).4. Click the arrow for the drop-down menu next to 'Save file type as' and select the 'Microsoft Excel (.xls)' option.5. Select the location for your saved file and change the name, if necessary.Click the 'Save' butt...
How to Create a Personal Budget Using Excel 2007
1. Open Excel 2007 and select the 'Office' button. Select 'New.' Excel displays the templates available. Click on the word 'Budgets' in the left task pane. Review the available templates and download one of the personal budgets by clicking the 'Download' button.2. Update your personal budget by adding your income. Locate the income section of the template and add your income. Locate the expense section of the template and add your expenses. If necessary, right click on the row and select 'Insert Row' to add additional rows to the template.3. Save your changes to the budget by clicking...
Saturday, May 25, 2013
How to Have Different Footers on a Word Document
Insert Different Footers in a Word 2003 Document1. Place your cursor on the page before the one in which you want to create a different footer.2. Go to the Insert menu and select Break. In the Insert Break dialog box that appears, select Next Page under Section Breaks.3. Go to the View menu and select Header and Footer. Place the cursor inside the footer section of the first page in which you want to make a different footer.4. Click the Link to Previous button on the Header and Footer toolbar. This will disconnect the footer from the previous section.5. Enter the information you desire...
How to Open a Specific Spreadsheet Each Time Excel Starts
1. Right-click the Windows desktop. Mouse over 'New' and select 'Shortcut.' The 'Create Shortcut' window will open.2. Click the 'Browse' icon.3. Locate and select the 'Excel.exe' file. This is the executable file that opens the Excel program. If you installed the program in the default location, the path to the file for the different versions is as follows:Excel 2003 - C:\Program Files\Microsoft Office\Office11\XlstartExcel 2007 - C:\Program Files\Microsoft Office\Office12\XlstartExcel 2010 - C:\Program Files\Microsoft Office\Office14\XlstartIf you can't find the 'Excel.exe' file in the...
How to Activate Macros in Excel
1. Open Microsoft Excel.2. Find and click the 'Office' button. It's not labeled, but it's the large round button with the Microsoft logo in the upper left corner of your Excel window.3. Click on the 'Excel Options' button at the bottom of the resulting menu.4. Click on the 'Trust Center' option in the list at left, and then click on the 'Trust Center Settings' button.5. Click on 'Macro Settings' in the list at left, and then click the radio button next to 'Disable all macros with notification.' Using this setting, Excel will notify you when a macro opens and ask you to choose whether...
How to Open XLS Files in a Microsoft Spreadsheet
Using Excel1. Click the 'Start' button or orb in the lower left corner of your screen, and point your cursor to 'All Programs.' Scroll down, click the 'Microsoft Office' folder, and click 'Microsoft Office Excel.' A blank Excel spreadsheet will open.2. Click the circular Microsoft Office orb on the top left of the spreadsheet. Click the second option, 'Open.' Click the location of your Microsoft Excel spreadsheet on the left side. People usually store documents on their desktop or in the 'My Documents' folder.3. Click the drop-down box on the lower right corner of the dialog box, and click...
How to Not Show MS Excel 2007 Page Breaks
1. Open the Excel 2007 program by double-clicking on the Excel document icon.2. Click on the circular Microsoft Office Button at the top-left corner of the Excel 2007 window.3. Choose 'Excel Options' from the very bottom of the drop-down box that appears. A new window will open up.4. Click on 'Advanced' on the left side of the window.5. Scroll down until you find the 'Display Options for this Worksheet,' which is about half-way down the page.6. Click on the check mark next to 'Show page breaks' to remove it. Page break lines will no longer show up on your workshe...
Friday, May 24, 2013
How to Create a Control Chart in Excel
1. Download a control chart template for Microsoft Excel. A good free template is available from www.vertex42.com/ExcelTemplates/control-chart.html.2. Double-click the downloaded Excel template. The file will open in a Microsoft Excel window.3. Choose the desired control chart format for your project. The two common control chart designs are listed as separate tabs at the bottom of the Excel window. The 'XbarR' worksheet tab creates a control chart for mean and range calculations. The 'XbarS' spreadsheet works with a control chart for mean and standard deviation.4. Click on the control...
How to Link an Excel 2007 Chart to a PowerPoint 2007
Prepare Your Chart in Excel 20071. Open the spreadsheet containing your chart in Excel 2007.2. Click the chart to select it and then press the 'Ctrl' and 'C' keys simultaneously to copy the chart to the clipboard.3. Select a blank sheet using the tabs near the bottom of the Excel 2007 window.4. Press the 'Ctrl' and 'V' keys simultaneously to paste the copied chart into the selected sheet.5. Save the spreadsheet to confirm the changes.Link Excel 2007 Chart in PowerPoint 20076. Open your presentation in PowerPoint 2007 and then navigate to the slide where you want the chart inserted....
How to Filter Data in Microsoft Excel 2007
1. Select the Home Tab on the Ribbon. The Ribbon is the group of icons and tools located directly above the work area.2. Locate the Editing Group on the far right side of the Home Tab. Click on the 'Sort and Filter' button to reveal a drop-down menu of options. Click on 'Filter'.3. Click on the drop-down arrow that Excel added next to the field you would like the data filtered by. Select the choice you would like to see filtered.4. Click on another drop-down menu if you would like to filter the remaining data by an additional criteria.5. Click on the filter icon next to the field header...
How to Remove Certain Colors in Excel 2007
1. Open the Microsoft Excel 2007 document.2. Highlight the cells that you want to remove color from.3. Scroll to the top of the screen and click the 'home' tab.4. Scroll to the right where there are six boxes stacked on top of one another. Click on the box that says 'normal.' This will remove all color and restore the cells back to normal.5. Scroll to the paint-bucket icon.6. Click on it to reveal a drop-down menu.7. Scroll down to where its labeled 'no fill' and click on it. This removes background color.8. Scroll to the icon that is a letter 'A' with a color pallet beneath it.9....
Thursday, May 23, 2013
How to Create a Pivot Table in Excel
Create a Basic Pivot Table From an Excel List1. Ensure your data source contains all data elements you want to include in your report. All data ranges need to have labeled rows, columns and associated data values.2. Select the 'PivotTable and Pivot Report' option from the Data menu. This option launches a wizard to aid you in creating a report.3. Choose the 'Microsoft Office Excel List or database' data source option from the wizard. This option specifies that your data resides in an internal spreadsheet.4. Specify PivotTable as your desired report. This triggers the data range prompt...
How to Create Bins in Excel 2007
Creating Bins in Excel 20071. Click the Microsoft Office link in your start box and click on Microsoft Excel 2007. Microsoft Excel 2007 is the green icon, located among the choices of Microsoft programs.2. Click 'Data Analysis,' located in the analysis group on the data tab. In the dialog box after clicking data analysis, click 'Histogram' and then click OK.3. Type your desired input range in the 'Input Range' box.4. Type your desired range to create a new bin in the 'Bin Range' box.5. Click 'New Workbook' which is located under 'Output Options.' Check the 'Chart Output' check box then...
Wednesday, May 22, 2013
How to Use the Selection Pane in Excel
1. Start Microsoft Excel 2007, and open an existing workbook from your files that contains multiple images that you would like to view with the selection pane.2. Click to select any one of your images so you can gain access to the Format ribbon. You can tell an image is selected, because it will be surrounded by white sizing handles and the 'Format' tab will appear at the top of the Excel screen.3. Select the 'Format' tab to display the Format ribbon. This ribbon displays all the settings you can use to format images in Excel.4. Choose the 'Selection Pane' button in the 'Arrange' section...
How to Write Macros in Excel 2010
1. Open Excel on your computer. Click the 'View' tab on the ribbon and then click the 'Macros' icon.2. Enter a descriptive name for the new macro. Do use spaces in the macro name. Create a sample macro to use during the process of learning to write a macro in VBA. Therefore, enter the macro name 'Change_Worksheet_Names' in the macro name field and then click 'Create.' The Microsoft Visual Basic for Application window opens and displays a 'Module (Code)' window with the blank 'Sub Change_Worksheet_Names()' code document.3. Continue creating the sample macro, which changes all of the generic...
Tuesday, May 21, 2013
How to Insert Error Bars in Excel 2007
1. Open the Microsoft Excel 2007 application that contains the chart that you want to add error bars to.2. Click on the area of the chart where you want to add the error bars. Click on the “Format” tab and then click on the arrow next to the “Chart Elements” box.3. Click on the chart element from the list that you want to add the error bars to. Click on the “Layout” tab and then click on the “Error Bars” option from the “Analysis” group.4. Select the “Error Bars with Standard Error”, “Error Bars with Percentage” or “Error Bars with Standard Deviation” option.5. Click on the “More Error...
How to Paste a Function in Excel 2003
1. Click to select the cell that you want to copy. To select multiple cells, click and drag your mouse to highlight the range or hold down the Ctrl key and click each cell you wish to copy.2. Press Ctrl C on your keyboard to copy the cells, or click 'Edit' and then 'Copy' on the main toolbar.3. Click in the blank cell where you want to paste the formula.4. Right-click, then choose 'Paste Special,' or click 'Edit' and then 'Paste Special' to display the 'Paste Special' dialog box.5. Select the 'Formulas' radio button, then click 'OK.' The formulas or functions are pasted into your ...
How to Add the Microsoft Date Time Picker in Excel 2007
1. Download Microsoft Windows Common Controls-2 6.0 from the Microsoft website.2. Extract the .cab file's contents. Newer Windows versions open .cab files directly. Older versions need extraction software such as WinZip or WinRAR.3. Run the 'mscomct2.bat' file. This installs the Microsoft Date Time Picker.4. In Excel, click the Office button on the screen's top left corner. Select 'Excel Options.' The 'Excel Options' window will open.5. Click 'Popular' from the top of the left column.6. Check the box next to 'Show Developer tab in the Ribbon.' Close the window. Excel's ribbon now...
Monday, May 20, 2013
How to Create Multiple Directories From an Excel Spread Sheet
1. Click on the 'Office' button.2. Mouse over 'Save As.' This displays an options list. Click on the type of file you want to save. For example, 'Excel workbook.'3. Click on the directory you want to save the file in, or click on the 'New Folder' icon to create a new directory. For example, click on 'My Documents' or create a new folder called 'My business tax documents.'4. Enter the name of your file in the 'File name' box.5. Click on 'Save.'6. Repeat steps 1 to 5 to save the file to as many directories as you wi...
How to Record an Excel Macro
1. Open the Tools menu and select Macro, then select Record a Macro.2. Fill in the blanks - the macro name, description, shortcut-key combination and worksheet where the macro will be available - in the Record Macro box that appears. The macro can be available from only one worksheet or from any worksheet.3. Click on OK.4. Notice that the word 'recording' appears in the lower left of the Excel screen while recording is in progress.5. Perform a series of keystrokes or mouse actions. The macro will record them. You can record any normal Excel action.6. Open the Tools menu and choose...
How to Create Microsoft Flow Charts
1. Open Excel 2010 and click the 'Insert' tab. Select 'SmartArt.' Click the 'Process' category in the left task pane. Click on the different process flow charts in the center task pane. Select a flow chart. For example, select the 'Basic Bending Process' flow chart. Click 'OK.' The SmartArt flow chart and Text Pane appear.2. Type your flow chart tasks in the bulleted 'Text' pane. Move to each shape in the flow chart by pressing the 'Enter' key as you complete each task. Click on the outside border of the flow chart pane.3. Notice the new 'SmartArt' tab on the ribbon. Use the 'Design'...
How to Remove a Hyperlink in Excel 2003
1. Right-click the cell with a hyperlink.2. Click 'Hyperlink.'3. Select 'Remove Hyperlink.' The hyperlink is now removed from your Excel docume...
How to Set Page Breaks in Microsoft Word 2003
1. Set your cursor where you want to page break to occur. Scroll to the “Insert” tab and the click on “Break.” Select “Page Break” from the drop down menu.2. Insert a page break using hotkeys. Select where you wish for the break to occur and the press hotkeys “CTRL ENTER” to create the break.3. Remove a page break. To remove a page break, left-click on the page that the break begins and press the “Backspace” button to delete the added page.4. Insert a custom page break. Scroll to “Insert” and then select “Break.” You can choose several custom breaks including: page break, a column break...
Sunday, May 19, 2013
How to Align Images in Excel
1. Open Microsoft Excel 2007 and open a workbook from your files that contains at least two images you wan to align to each other.2. Hold down the 'Shift' key on your keyboard and click to select the first image you want to align. Continue holding down your shift key and click to select any other images that you want to align in relation to the first image. Once selected, the images will be surrounded by a thin, black line and white sizing handles.3. Select the 'Format' tab from the top of the screen (if necessary) to display the Format ribbon. Locate the 'Arrange' section in the Format...
How to Graph Linear Equations in Excel 2007
1. Open a new document in Microsoft Excel 2007.2. Enter 'x' in the top left cell. Press 'Tab' and enter 'y' in the next cell over.3. Enter a series of values in the first column under 'x' (cells A2 and down). These values will make up the parameters of the horizontal axis. For instance, enter '-5' in cell A2. In the next cell down, enter '-4' and onward: -3, -2, -1, 0, 1...all the way up to 5.4. Define the name of the x values so you can enter the formula for the 'y' column. Use your cursor to highlight the cell values in column A. In other words, highlight cell A2 and drag your cursor...
How to Make Text Vertical in Excel
1. Click the Excel cell containing the text you want to rotate.2. Click 'Home' at the top of the Excel window.3. Click on the 'Orientation' button. The 'Orientation' button has 'AB' written diagonally with a diagonal arrow pointed up on it. It is located under the words 'Data' near the top of the Excel window.4. Select 'Vertical Tex...
How to Do a Calibration Curve on Excel
1. Run a set of known X variables through the equipment to produce a series of Y outputs.2. Open Microsoft Excel. In cell A1 type 'concentration.' In cell B1 type 'output' or the appropriate unit of measure for your experiment.3. Enter experimental concentrations for the X variable in column A, starting at A2.4. Enter experimental outputs for the Y variable in column B, starting at B25. Highlight entire data set.6. Click the 'Insert' ribbon.7. Click the 'Scatter' button and select the 'Scatter plot with markers and straight lines.' The graph should now appear and the Excel ribbon...
Saturday, May 18, 2013
How to Make a Graph on Excel With X Y Coordinates
1. Open a new Microsoft Excel 2010 worksheet. Click on cell 'A1' and type in the header for your first series of data. This series will wind up on the horizontal, or 'X,' axis.2. Click on cell 'B1' and enter the header for your second series of data. This series will be plotted on the vertical, or 'Y,' axis.3. Select cell 'A2' and enter the first value you want to plot. Then select cell 'B2' and enter the corresponding coordinate. Continue entering your coordinates down the columns until you have entered all your data.4. Click on any cell in the data field you have just created. Click...
How to Create Criteria Ranges in Excel for Database Functions
1. Open the Excel application on your computer that contains the table of database functions that you want to create criteria for.2. Highlight the range of cells containing numeric data using your mouse. Click on the “Data” tab for Excel 2007 or the “Data” option from the top toolbar menu for Excel 2003.3. Click on the “Filter” option and the click on the drop-down arrow located in the column header.4. Move your mouse over the “Number Filters” option and then click on the “Custom Filter” option. The Custom AutoFilter dialog box will appear.5. Select the number criteria for your ranges...
How to Restore Gridlines in Excel 2003
1. Click 'Tools' in the menu bar.2. Select 'Options.'3. Click on the 'View' tab, then check the 'Gridlines' box.4. Save your docume...
How to Convert a Microsoft Word Table Into an Excel Workbook
1. Open Microsoft Word and Excel from the desktop menu. In Word, find the document containing the text to be converted. In Excel, open a blank worksheet.2. Return to the Word document and confirm that the text appears in true table format. If there are gridlines or a compass symbol enclosed in a box at the top left corner of the first column, the text is a true table. If not, the text appears in columned format only and must be converted to a true table.3. Convert columned text to a true table by inserting a tab, a comma or an asterisk in between each row of the columns. Highlight all the...
How to Unfreeze Panes
Freezing Panes in Excel 97/2000/XP/20031. Open the spreadsheet you want to freeze.2. Select the row below the one you want to freeze by clicking on the row header.3. Select 'Window' from your menu and choose the 'Freeze Panes' option.Unfreezing Panes in Excel 97/2000/XP/20034. Open the spreadsheet with frozen panes.5. Select 'Window' from the menu.6. Choose the 'Unfreeze Panes' option.Freezing Panes in Excel 20077. Select the row just below the one you want to freeze by clicking on the row header. This will make a specific row or rows visible as you scroll within Excel. Select the...
Friday, May 17, 2013
How to Move Averages in Excel 2010
1. Enter the date, or other reference information, in column A, and enter its corresponding data in column B. Repeat for each data point. As an example, you might have 'Jan 1, 2011' in cell A1 and '$43.25' in cell B1. On the next row, you might have 'Jan 2, 2011' in cell A2 and '$44.50' in cell B2. This continues for however many data points you have, but you will need several data points to construct a moving average.2. Click the cell in column C whose row number corresponds to the number of data points you want to include in the moving average. As an example, to include the last 20 data...
How to Make Double Line Graphs on Excel
1. Open the Excel worksheet that contains the data values.2. Click and drag on the categories and two data series you want to plot.3. Click the “Insert” tab in the Command Ribbon.4. Click the arrow for the “Line” button in the Charts group. A gallery of seven thumbnail charts appears.5. Click the preferred chart sub-type.6. Click “OK.” The two data series convert to a line graph. The Chart Tools Ribbon displays.7. Customize the chart with the commands in the Chart Tools Ribbon. Chart Tools contains three tabs: “Design,” “Layout” and “Format.” Experiment with the commands in each...
How to Format Dates in Microsoft Excel 2003
1. Select a cell and activate it. You will need to first left-click on a cell that you wish to enter a date into.2. Create a date inside of a cell. Enter in any date that you like in the cell and then press enter; this will store the information into that cell.3. Access the cell formatting menu. To access this menu, right-click on the cell that you just entered the date into and select “Format Cells.”4. Format the date as desired. Under the cell formatting properties menu, left-click on the “Number” tab and then scroll to and highlight the “Date” field. Under the “Type” box you can scroll...
How to Turn Off Page Lines in Excel
1. Click the 'File' tab in the Excel window, and then click 'New' if you want to make a new Excel document. Click on a template that you want to use, and then click 'Create.' A new Excel document will appear. Otherwise, click the 'File' tab, then click 'Open,' and then click on a folder where your file is stored. Click on the file to select it, and then click 'Open.'2. Click the 'File' tab in the Excel window.3. Click on 'Normal' in the 'Workbook Views' group. Excel will now display your spreadsheet without the page break lines. Click 'Page Layout View' if you want to see the lines again....
Thursday, May 16, 2013
How to Create a Workbook in Excel 2007
1. Decide what your needs are. You need a purpose for using Excel. Whether it is for a budget or to keep track of work, you need to know what you are planning to build.2. Open Excel and you will see the option to create a workbook or use a template. You may want to check what templates are available and if you feel one of them meets your needs you could try using it. However, since most work is unique and you may have your own thoughts on how you want to present the data, you may be better off creating your own workbook.3. Use column headings that will make your life easier when it comes...
How to Format Data Labels in Excel
1. Launch 'Microsoft Excel' by double clicking the program's shortcut on the desktop, or by selecting 'Microsoft Excel' from the 'Programs' or 'All Programs' menu.2. Click 'File' or the 'Office Button' in the upper left corner of the program window and select 'Open.' Click on the downward pointing arrow to the right of the 'Look in' text box, and navigate to your Excel file. Select the file and click 'Open' or double click the file to open it.3. Go to step 4 if you have Excel 2007; otherwise select the chart with the data labels you want to format. Click 'Chart' on the menu bar and select...
How to Graph a Normal Distribution Curve in Excel 2007
1. Enter the number -4 into cell A2.2. Select the cell with your mouse. Click on the down arrow under 'Fill' in the Editing group to the far right on the ribbon (the toolbar). Choose 'Series.' A pop-up will appear.3. Click on the radio buttons to choose 'Columns and Linear.' Type .25 into the 'Step Value' box and 4 into the 'Stop' box. Press 'OK.' This should fill column A with the x-values for the normal distribution.4. Type =NORMDIST(A2,AVERAGE($A$2:$A$34),STDEV($A$2:$A$34),FALSE) into cell B2. This calculates the mean and standard deviation from your x-values and also creates your first...
How to Compare Data in Excel with VLOOKUP
1. Open the Excel 2010 spreadsheet that contains that data you want to compare. Click on the top cell of the first empty column in your spreadsheet.2. Enter the following formula into the cell:=vlookup(A1,B:B,1,False)Change 'A1' to the top cell in the first column of data. Change the 'B's in 'B:B' to the letter of the column where the second set of data is located. Keep the '1,' as this tells VLOOKUP that you want to check the first column in the 'B:B' range. 'False' specifies that you want to search for an exact match, instead of an approximate one.3. Press 'Enter' to complete the formula...
Wednesday, May 15, 2013
How to Create and Name an Excel 2007 File
Create New File Instructions1. Open the 'Start' menu and type 'Excel' into the search box.2. Click the 'Windows Office' icon in the top left corner of the Excel 2007 menu bar.3. Click the 'Blank Workbook' in the 'New Workbook' window, then click the 'Create' button.4. Click on the 'Microsoft Office' icon and select 'Save.' Create a name for your spreadsheet and ensure that the 'Save as type:' menu is set as 'Excel Workbook (*.xlsx).'5. Click 'Save' in the 'Save As' window to save the new Excel 2007 file.Existing Data Instructions6. Open the 'Start' menu and type 'Excel' into the search...
How to Calculate Pearson's R (Pearson Correlations) in Microsoft Excel
1. Put the values of the two variables you wish to correlate into two columns of the same length. For example, say you have data about the heights and weights of 50 people, and want to calculate the Pearson correlation between the two. Put the data into two columns: the heights in cells 1 to 50 of column A, and the widths in cells 1 to 50 of column B.2. Select an unused cell and type '=CORREL(' (without the quotes). After typing the first open parentheses, select all of the cells in your first column, type a comma, select all of the cells in your second column, and type the closing parentheses...
How to Remove Hidden Macros in an Excel Spreadsheet
1. Double-click the Microsoft Excel document you want to edit. This opens the document and loads the Excel application.2. Click the 'View' menu option and select 'Unhide.' In the section labeled 'Personal,' select 'Unhide workbooks' from the list of options.3. Click the 'Developer' tab at the top of the Excel window. If you don't have the Developer tab, click the Office button and select 'Excel Options.' Click the 'Popular' tab and select 'Show Developer tab in the Ribbon.' Press the 'OK' button.4. Click the 'Macros' button in the 'Developer' tab. A pop-up window opens with a list of macros...
How to Create Sequences in Excel
1. Click the first cell in the row or column that you want to contain the sequence and type the first item. For example, to create the sequence of 'Red,' 'Green' and 'Blue,' type the word 'Red.' Press 'Enter' to move to the next row or press the right arrow key to move to the next column.2. Type the next item in the sequence, such as 'Green.' Press 'Enter' or the right arrow key.3. Type the next item in the sequence, such as 'Blue.' Repeat this process until you have typed every item in the sequence.4. Click and drag with the mouse pointer to highlight the cells containing the complete...
How to Open Large Files in Excel 2003
1. Launch Excel, and navigate to the 'tools' menu.2. Choose the 'macro' submenu, and select the Visual Basic editor.3. Choose 'module' from the insert menu.4. Copy and paste the following text:Sub LargeFileImport()'Dimension VariablesDim ResultStr As StringDim FileName As StringDim FileNum As IntegerDim Counter As Double'Ask User for File's NameFileName = InputBox('Please enter the Text File's name, e.g. test.txt')'Check for no entryIf FileName = '' Then End'Get Next Available File Handle NumberFileNum = FreeFile()'Open Text File For InputOpen FileName For Input As #FileNum'Turn Screen...
How to Use the Conditional Sum Wizard in Excel
1. Open the file that contains that data that you need to run the conditional sum wizard on.2. Add a header row to your data, if it lacks one, by right-clicking on the number next to the first row and choosing 'Insert.' Type in names for the columns in the cells directly above the data.3. Click on the top-left cell of the data range. Move to the bottom-right cell in the data range. Press and hold the 'Shift' button and click on the cell to select the entire data range.4. Click the 'Formulas' tab at the top of the screen, then click on the 'Conditional Sum' button located on the far right...
Tuesday, May 14, 2013
How to Create a Macro to Run an Access Query Paste the Result Into Excel
1. In Access, create a table of sample data: enter the following data in a new table:the accidental tourist,12/1/2009,$6.01the accidental tourist,12/3/2009,$7.98iron john,12/5/2009,$4.98iron john,12/6/2009,$5.982. Double-click the column headers (e.g. 'Field1') and replace each with these headers, in this order:book,datesold,netsaleSave the table ('control-s') with the name 'books.'3. Create a query from the table, and press the 'Esc' key in the 'show table' dialog box. Right-click on the query's tab and select 'SQL view.' Enter the following in the code window:SELECT books.* INTO queryresultsFROM...
How to Create a Newsletter in Microsoft Office
1. Open Microsoft Word, and select a newsletter template by clicking the drop-down menu File > New Works Template > Tasks. Additional templates may be downloaded from the Microsoft Office Online website.2. Create a nameplate for your newsletter. Using Word's 'header' is the simplest way to do this. Select View > Header and Footer from the drop-down menu to edit text boxes within the header and footer. Center your newsletter's title and add clip art, if desired, selecting Insert > Picture > Clip Art from the drop-down menu. If the software is available, you may use Copy and...
How to Use VLookup in Excel 2003
1. Open the Excel 2003 file that contains the data table that you want to work with.2. Select the top-left cell of the data table and hold down the mouse button. Move the mouse to the bottom-right cell and select it. Click the 'Data' menu at the top of the screen and select 'Sort.' Select 'Ascending' to sort the information from smallest to largest. The first column has to be sequential in order for VLookup to work.3. Select any cell outside of the data table. Press the '=' key to start your formula. Type 'vlookup('. The open parenthesis is required to begin entering arguments.4. Type...
How to Remove Checkboxes in Excel 2007
1. Open Microsoft Excel 2007.2. Click the 'File' tab, click 'Open' and browse to the spreadsheet with the check boxes to delete. Double-click the name of the file to open it.3. Click once on the first check box to remove. Press the 'Delete' key to remove it. Scroll throughout the spreadsheet or page tabs at the bottom of the work area and continue clicking and pressing the 'Delete' key to remove the check box...
Monday, May 13, 2013
How to Make a Pivot Table in Excel 2007
1. Open the Microsoft Excel spreadsheet program. Click 'Start' on the main operating system menu. Then choose 'Programs' and from the program menu select 'Microsoft Excel.'2. Choose a cell in a Microsoft Excel spreadsheet. Make certain the call range has a column heading. From the main tab menu, select the 'Insert' tab, then in the table menu click on 'Pivot Table.'3. Select the data range to be analyzed. A user can either select a range of data in the current spreadsheet or choose data from an external source. Also, determine if the pivot will appear in the current worksheet or in a new...
Sunday, May 12, 2013
How to extend the Microsoft Office 2010 trial
1. Install the Office 2010 trial of your choice. Wait until the end of the initial 30 days to complete the rest of the steps. Office will have a pop-up reminder when the trial is about to expire.2. Run the command prompt as an administrator. To do this in Windows 7, go to the Windows orb logo (formerly the 'Start' menu). In the search field, type 'cmd' and then press the 'Ctrl' 'Shift' 'Enter' keys together. Click 'Yes' if prompted by the User Account Control.3. Enter 'C:\Program Files\Common Files\Microsoft shared\OfficeSoftwareProtectionPlatform\ OSPPREARM.exe' into the command prompt...
How to Delete Duplicate Rows in Excel Based on One Column
1. Select the entire column of data by left clicking on the top cell and dragging the cursor to the bottom cell.2. Click on the 'Data' tab and select 'Filter > Advanced.' Click the 'Filter the list, in-place' radio button and check the box next to 'Unique records only.' Click the 'OK' button.3. Open the Office Clipboard by pressing the 'Home' tab and choosing 'Clipboard.'4. Hold down the Crtl and C keys at the same time to copy the cells into the clipboard.5. Click on the 'Filter' tab to restore the original cell data.6. Paste the modified cell contents from the clipboard into the...
How to Make a Gantt Chart Using Microsoft
1. Open a new Excel worksheet. Enter column headings into Row 1, such as 'Start Date,' 'Amount Completed' and 'Amount Remaining.' Enter your data into the columns for each heading.2. Select the cells that contain data. If you are using Excel 2003 or earlier, click the 'Chart Wizard' button on the 'Standard' toolbar. In Excel 2007, go to the 'Chart' section on the 'Insert' tab. Select 'Bar' as the chart type, and then select 'Stacked Bar' as the subtype. Click 'Finish.'3. Double click the first color in the chart bars to open the 'Format Data Series' dialog box. Go to the 'Patterns' tab....
How to Change Columns From Numbers to Letters in Excel 2007
1. Open Microsoft Excel 2007 to open a blank worksheet. Locate the 'Office' button in the top left hand corner next to the exit button. Click on the 'Office' button.2. Click on 'Excel Options.' A new menu window will appear in which you need to click on the 'Formulas' tab.3. Deselect he 'R1C1 Reference Style' check box. Click 'OK'to close the window and save your selections. This will change the columns from numbered to lettered immediately. To change the R1C1 reference style again simply revisit the excel options again and check the R1C1 b...
Saturday, May 11, 2013
How to Reassign a Macro Button in Excel 2003
1. Open Excel 2003 and locate the macro button on your toolbar. Click 'Tools' on the menu bar and click 'Customize.' The Customize dialog box will appear. Click the 'Commands' tab. Click the macro on the toolbar and click the 'Modify Selection' button in the Customize dialog box.2. Select 'Assign Macro.' The Assign Macro dialog box appears. Select a macro from the list and click 'OK.' If necessary, you can update the macro image by clicking the 'Modify' button in the Customize dialog box and select 'Change Button Image.'3. Update the macro description by clicking the 'Modify' button...
How to Make Box Plots in Excel
1. Open the Excel file that contains the data you want to represent as a box plot.2. Scroll to the bottom of the data set and type in five new row headers on the left-hand side of the screen. These headers, from top to bottom, are: “First Quartile,” “Minimum,” “Median,” “Maximum” and “Third Quartile.”3. Select the cell to the right of “First Quartile.” Type in “=Percentile (XXX, 0.25),” where XXX is the field of data for that sample. Write out the field by typing in the column and row of the first cell in the field, then add a colon, and then type in the column and row of the last cell in...
How to Insert a Countdown Timer in an Excel Worksheet
1. Open the Excel 2010 worksheet where you want to add a countdown timer. Right-click the cell where you want the timer to go and choose 'Format Cells.' Click 'Custom' on the left side of the window that appears and then select 'h:mm:ss' from the list on the right. Click 'OK' to continue.2. Hold down 'Alt' and press 'F11' to open up the VBA console. Select your current worksheet from the list on the left side of the console. Click the small arrow next to the 'Insert Userform' button at the top of the screen, which is the second button from the left side, and choose 'Module' from the list of...
How to Create a Standard Deviation Graph in Excel
1. Open Excel. Open a data file or type the data into a series of columns with one value per column.2. Click 'Insert' and select 'Scatter.'3. Select the type of scatter graph you wish to insert.4. A blank area should be inserted and you should see new tabs as options on the menu bar. Click the 'Design' tab and choose 'Select Data.' Hold 'Shift' while selecting the data and click 'OK' in the pop-up window.5. Click on the 'Layout' tab and select 'Error Bars.' Select 'Error Bars with Standard Deviation' from the drop-down wind...
How to Calculate Mean in Excel 2007
1. Enter your the data in column A. For example, if you have four numbers to enter, you would enter them in cells A1 through A4.2. Determine the range for your data. For example, if you entered four data points, your range would be A1:A4.3. Enter the formula '=AVERAGE(Range)' into cell B1 to have Excel automatically calculate the average of your data. In this example, since your range equals A1:A4, you would enter '=AVERAGE (A1:A4)' into cell B1 and the average will appe...
How to Embed a PDF File in Microsoft Excel 2003
1. Open the Excel document you're editing if it's not open already.2. Select the location where you want to embed the PDF. When you insert the PDF, the top-left corner of the document will be flush against the top-left corner of whatever cell you have selected.3. Click the 'Insert' menu at the top of the screen and select 'Object' to open the 'Object' window.4. Select 'Adobe Acrobat Document' from the 'Object type' section and then click 'OK.' Excel will display an 'Open' window.5. Navigate to and select the PDF that you want to embed into Excel.6. Click 'Open.' Excel will embed the...
Subscribe to:
Posts (Atom)