1. Select the cell in which you want the returned value.2. Click on the formula bar, the long empty box at the top of the screen.3. Type =VLOOKUP(“text you want to match”,Be sure to include the double quotes.4. Click the tab of the other spreadsheet, the one holding the searchable table.5. Highlight the table.6. Click back to the original spreadsheet. Now the location will be in the VLOOKUP function.7. Click the formula bar again, add a comma after the table location and type in which column you want the value returned from. For example, if you want to return two columns to the right...
Browse » Home » Archives for July 2012
Saturday, July 28, 2012
How to Add Radio Buttons to Microsoft Excel for Data Collection
1. Define the mutually exclusive data to be collected. For example, if the spreadsheet is used to track clothing by size, the option buttons might be for small, medium and large sizes. This prevents mistyping information.Select the 'Developer' tab; from the 'Controls group,' press 'Insert > Option Button.' Place the cursor into the position where the upper left corner of the button and label are desired, and then drag a square, creating the frame. Do the same for the remaining option buttons to be created. Excel will name these 'Option Button 1,' '2,' and '3.'2. Select 'Option Button 1,'...
How to Insert Auto Numbering in a Header for Excel 2003
1. Run the Microsoft Excel 2003 application, and open the workbook that you want to edit.2. Click 'View' in the menu bar, and then click 'Header and Footer' in the drop-down list.3. Click the 'Custom Header' button.4. Click the section of the header that you would like to add automatic page numbering to.5. Click the '#' icon to add automatic numbering to the specified section.6. Type any additional text that you want to display in the header, and then click 'OK' twice to save your header chang...
Friday, July 27, 2012
How to Use Multiple Regression in Excel
Excel for Multiple Regression1. Enter the data you will use to conduct your regression analysis into an Excel spreadsheet. You can enter the data by hand or import a data file from another source, such as an ASCII file or another spreadsheet, into Excel.2. Unlock the Data Analysis tool from the add-ins menu and install it. Open Excel, click “Tools” and select “add-ins” from the drop-down menu that appears. A smaller window opens that displays a set of options. Check the box next to “Analysis ToolPak” and click “OK.” The Data Analysis option appears in your Tools menu, ready for use. If you...
How to Compare Two Columns in Excel 2007
1. Activate Excel and open a new worksheet. Set up three different columns entitled 'A,' 'B' and 'C'. Allow for five data entries within each column so that you are working with a 3x5 grid.2. Enter the data for the two known columns in 'A' and 'C'. Leave column 'B' blank for the time being, and make sure not to enter zeros in the 'B' column. Column 'B' needs to remain blank because this is where the formula will be entered and function.3. Select the first cell in column 'B' and enter the following formula therein; =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),'',A1)4. Navigate to 'Fill' from the...
How to Merge Columns From Two Tables
1. Open the Excel file that contains the two tables with the data you want to merge.2. Select the cell where you want the merged column to start and type in a header for the column. Select the cell directly beneath the header. If you want the merged call to be part of one of the tables, right-click on the top of the column where it lists the column letter, directly to the right of where you want the new column to go. Choose 'Insert.'3. Enter in the following: =CONCATENATE(XX,' ',YY) Make 'XX' equal to the first cell that you want to merge, and make 'YY' equal to the cell in the second...
How to Make a Bar Line Graph in Excel
1. Launch Excel. Click the 'File' tab and select the 'New' option from the File menu to create a new Excel spreadsheet.2. Enter the data for the graph into the cells. Enter the data you want to appear in the bar graph in one row and the data you want to appear in the line graph in an entirely separate row. Each piece of data must be entered into its own cell. For the bar graph, each cell will become its own bar. For the line graph, each cell will represent a specific point on the line.3. Click on the first data-containing cell (upper-left corner) and drag the mouse cursor to the last data-containing...
How to Copy Column Values to Rows in Excel 2007
1. Copy all of the blocks in the column that you want to convert to a row. You can copy multiple blocks at once by holding down the 'CTRL' button as you click on each block in the column.2. Right click anywhere within the highlighted blocks and select 'Copy.' Another method for copying the information in the columns is to press the 'CTRL' and 'C' buttons at the same time.3. Choose the row where you want to paste the information, right click on the first block and select 'Paste Special.' A menu with several options will pop up.4. Place a mark next to 'Transpose' in the bottom right section...
How to Use the LINEST Function in Vista and Excel
1. Highlight a set empty cells in your spreadsheet which composes 5 rows and 3 columns. Click on the 'Formulas' tab, and then select the 'More Functions' menu. Select 'Statistical' sub-menu, and then select the 'LINEST' function. The LINEST 'Function Arguments' dialog will open.2. Click on the red arrow next to the 'Known_y's' argument, and highlight the column or row of data on your spreadsheet that represent the y-axis of your argument. Click on the downward pointing red arrow in the 'Function Arguments' dialog when done.3. Click on the red arrow next to the 'Known_x's' argument, and highlight...
Thursday, July 26, 2012
How to Print the Gridlines of an Excel Worksheet
1. Start Microsoft Excel, and open a spreadsheet that you would like to print, having the gridlines appear on the printed copy of the spreadsheet.2. Choose the 'File' menu and click on 'Page Setup...' to open the 'Page Setup' dialog box. Within the 'Page Setup' dialog box you can choose settings that apply to your whole Excel spreadsheet.3. Click on the 'Sheet' tab at the top of the 'Page Setup' dialog box to display the sheet settings for the Excel spreadsheet you have open.4. Find the 'Print' section of the 'Sheet' tab in the 'Page Setup' dialog box, located in the middle of the dialog...
How to Chart Cells From Two Different Worksheets in Microsoft Excel
Create the Chart1. Enter data in rows by using row headers with data under them. You can also choose to enter data in columns by using column headers with data under them.2. Select the cells containing the data you will use for the chart.3. Select the 'Insert' tab on the top menu, and click the arrow in the bottom-right corner of the Charts group to open the Insert Chart window.4. Choose the type of chart in the Templates column of the Insert Chart window.5. Select the chart using the images in the right box in the Insert Chart window, and click the 'OK' button.6. Move your embedded...
How to Rotate a Pie Chart in Excel
1. Open the Excel worksheet containing the pie chart you wish to rotate.2. Click on the pie chart. Doing so will display the 'Chart Tools' menu at the top of the Excel window. Within this menu you will see tabs labeled 'Design,' 'Layout' and 'Format.'3. Select the 'Format' tab. Navigate to the 'Current Selection' group. Locate the 'Chart Elements' box and click on the arrow next to it. Click on the desired data point or series.4. Return to the 'Format' tab. Select 'Format Selection' from the 'Current Selection' group.5. Locate the 'Angle of First Slice' box. Move the slider to the position...
How to Change to R1C1 Cell Reference in Excel 2007
1. Open Microsoft Excel 2007.2. Click on the 'Office' button in Excel.3. Click on 'Excel Options.'4. Click on 'Formulas,' in the left-hand pane of the 'Excel Options' box.5. Check the 'R1C1 reference style' check box under 'Working with formulas' to enable 'R1C1' style referencing. Clear this check box to use 'A1' style referenci...
Wednesday, July 25, 2012
How to Insert a Check Box on an Excel Spreadsheet
1. Click the 'Developer' tab.2. Click 'Insert.'3. Select 'Check Box' under the Active X controls.4. Click where you want the check boxes to appear on the spreadsheet.5. Go back to the 'Developer' tab and select 'Design Mode.' This allows you to design the check boxes.6. Go back to the 'Developer' tab and select 'Properties' to change any properties for the check box...
How to Enable Excel Data Analysis in Office 2007
1. Open the Microsoft Excel 2007 application on your computer and then click on the 'Microsoft Office' button.2. Click on the 'Excel Options' button from the bottom of the application and then click on the 'Add-ins' button.3. Select the 'Excel Add-ins' option from the 'Manage' box and then click on the 'Go' button.4. Click on the box next to the 'Analysis ToolPak' field so that it's selected and then click on the 'OK' button.5. Click on the 'Yes' button if you are prompted to install the add-in for your computer. Once the add-in is loaded, click the 'Data Analysis' button from the 'Data'...
How to Enter 17 Digits Into Microsoft Excel 2003
1. Enter your 17-digit number in a cell in an open spreadsheet. If the number displays in scientific notation, you have to adjust the cell format to have it display all 17 digits.2. Click on the 'Format' pull-down menu at the top of the spread sheet, and select 'Cells' to format how numbers will appear within an individual cell.3. In the 'Format Cells' box that appears, select 'Numbers' from the tabs. You'll be presented with a series of choices for formatting numbers, such as currency, dates or percentage. Select 'Numbers' from the list. This will change the appearance of numbers in cells,...
Tuesday, July 24, 2012
How to Make a Series the Same Color Across Multiple Charts in Excel 2010
1. Launch Excel and open the spreadsheet that contains the charts for which you want to create a common color for a data series. Click the chart to display the “Chart Tools” menu in the ribbon.2. Click one of the data series in a chart for which you want to create a common color. Click the “Format” tab and locate the “Current Selection” section. Click the “Chart Elements” menu and select the “Data Series” option. Click the “Format Selection” button, which will open a separate window.3. Click the “Patterns” tab and click a color in the “Area” section. The series will automatically take on...
How to Move Columns in Excel 2007
1. Open the Excel 2007 spreadsheet you wish to modify.2. Select the top of the column you wish to move, such as column A. This highlights the entire column.3. Right-click the column and click 'Cut.'4. Place the column in the location you prefer. Right-click the location and click 'Paste.' Repeat these steps for other columns you wish to change. You have now moved columns in Excel 20...
Monday, July 23, 2012
How to Remove Print Preview Lines in Excel 2007
1. Click the 'Page Layout' tab on the Ribbon at the top of the Excel screen.2. Find the 'Gridlines' section inside the larger 'Sheet Options' section.3. Uncheck the 'View' box to remove gridlines from the Print Preview screen. If you want to prevent the gridlines from appearing on a printed sheet as well, uncheck the 'Print' b...
How to Move Columns in Excel
1. Choose the column heading (1, 2, 3 and so forth) that you want to move and highlight it. You can also select several columns by holding down your left mouse button and sliding it across the columns which you would like to select, in case you want to move more than one column at a time.2. Leave your mouse placed on the highlighted column(s) and right click your mouse button. This will bring up a pop up menu with several options to choose from.3. Pick 'Cut' from the menu that pops up. This will make the column you want to move disappear, but your computer will store it in its temporary...
How to Sort Columns in Excel 2007
1. Open your spreadsheet file in Microsoft Excel.2. Press 'Ctrl' and 'A' to select all the columns in your spreadsheet.3. Click 'Sort Filter' near the right side of the ribbon.4. Choose 'Custom Sort.'5. Select the first column you want to sort on, such as Column A, in the first drop-down box.6. Select the criterion on which you want to sort this column, such as the value of the cell or the cell color, in the second drop-down box.7. Select your desired sort order, such as A-to-Z or smallest-to-largest, in the third drop-down box.8. Click the 'Add Level' button if you want to sort...
How to Make a Double Bar Graph on Microsoft Excel
1. Open a new Excel 2010 document.2. Enter the names of the items you want on your double bar chart, starting in cell B1 and continuing to the right of that cell. These headers will become the labels printed at the base of each of the bar sets on the graph.3. Type the names of the two categories that the double bar graph will track into cells A2 and A3. These labels will be located to the right of the bar graph. Each of these two categories will be assigned a color bar, which will appear next to each of the items on the graph.4. Input your numerical data into the cells, starting with cell...
How to Create a Frequency Table in Excel Using Pivot Tables
1. Highlight the cells containing the data, if you’re starting the pivot table from scratch.2. Open the Data drop-down menu and select “PivotTable Report…”. A new frame will open. Click the two “Next” buttons that you see. Click “Existing Worksheet” and select a cell to be the upper-left corner of your pivot table.3. Click “Layout.” A new panel will open. Drag the field name to the square center of the table that you want to know the frequency of. Double-click it and select “Count.” Drag the same field from the far right into the left column where the row labels go. Click “OK” and then click...
Sunday, July 22, 2012
How to Change the Text Box Size in Excel 2003
Inserting a Text Box1. Click on 'View' on the main toolbar, then 'Toolbars' and ensure there is a check mark next to the 'Drawing' toolbar.2. Locate the 'Drawing' toolbar on your screen.3. Click on either the 'Text Box' or 'Vertical Text Box' commands on the 'Drawing' toolbar. Your cursor changes to the 'Text Box' tool.4. Click and drag your mouse to create a text box.Resizing a Text Box5. Locate the text box that you wish to resize, then click anywhere in the text box to select it.6. Change the width of the text box by clicking and dragging from the central anchor point on either...
How to Format an 'If Statement' in Excel for a Blank or Filled Reference
1. Open the Microsoft Excel 2010 worksheet. Press 'Alt F11' to open the VBA console.2. Select a module from the list on the left side. If no module exists, right-click on a worksheet in the list, move the mouse pointer over 'Insert' and choose 'Module.' Select the module that appears.3. Type 'sub testing()' into the first line of the module and press 'Enter.' Change 'testing' to whatever word you want as it is simply the name of the subroutine. Excel VBA automatically adds the 'End Sub' command to the last row of the module.4. Type 'Dim x as range' into the next line. This will establish...
Friday, July 20, 2012
How to Determine an MS Excel File Version
1. Click on the Windows “Start” button then on “Computer” to open Windows Explorer.2. Browse your computer to find the Excel file.3. Check the file extension. If it is '.xlsx' then the file was created with Excel 2007. The extension '.xls' indicates older program versions.4. Right-click on the Excel file and choose “Properties” from the pop-up menu.5. Select the tab “Details” (in Windows Vista or 7) or “Summary” then “Advanced” (in Windows XP).6. Read the Excel version in the line “Type,” for example, 'Microsoft Office Excel 200...
How Can I See Excel 2003 Commands in Excel 2007?
1. Click the 'Office' button, and select 'Customize Quick Access Toolbar.' This action opens a pop-up window.2. Select 'All Commands' from the 'Choose' menu. This action displays a list of available Excel commands that can be added to the Quick Access Toolbar.3. Select a command from Excel 2003 that you want to view independent of the ribbon interface. Click the 'Add' button. Repeat this process for each Excel 2003 command that you want to view in the Quick Access toolbar. Click the 'OK' button when you have finished adding Excel 2003 comman...
How to Remove Hyperlinks in Microsoft Excel 2007
1. Open Excel using the 'Start' menu or another shortcut. You may also type 'Excel' in the Start menu search box and click on the resulting link.2. Click the 'Open' option and select the spreadsheet you want to work with. Locate the cell containing the link.3. Right-click the link you want to delete and click the 'Remove Hyperlink' option. Repeat for each link you want to remo...
How to Calculate Percentage Increases in Excel 2007
1. Type 'Beginning Number' in cell A1, and then type your beginning number in cell B1. For example, type '50' in cell B1.2. Type 'Ending Number' in cell A2, and then type your ending number in cell B2. In the example, type '100' in cell B2.3. Type 'Percent Increase' in cell A3.4. Type '=((B2-B1)/B1)' in cell B3. In the example, your result will be 1, or 10...
How Can I Disable All Double Clicking in Excel 2003?
1. Open the document you wish to disable double-clicking on in Microsoft Excel 2003.2. Press the 'Alt' key on your keyboard and 'F11' key simultaneously. The VBA editor will appear.3. Double-click 'Workbook' in the upper left window pane in the VBA editor. A new window will appear.4. Copy and paste the following code into the new window:Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ByVal Target As Range, ByVal Cancel As Boolean)Cancel = TrueEnd Sub5. Press 'Alt' and 'F11' simultaneously again to save the code and return to Excel. The string of code disables all double-click...
How to Create a Stacked Bar Graph in Excel
1. Click 'Start,' then 'All programs,' then 'Microsoft Office,' then 'Microsoft Excel.'2. Press 'Ctrl' 'O,' then locate and open the Excel document which contains the data to be turned into a stacked bar chart.3. Highlight the data that you want to create the chart from.4. Click 'Charts,' then 'Bar,' then 'Stacked Bar.' The stacked bar chart will automatically appear in the worksheet. You have now inserted a stacked bar chart into your Excel docume...
Thursday, July 19, 2012
How to Allow a Shared Workbook
Microsoft Excel 20031. Launch Excel 2003, then open the Excel workbook you want to share with other users on the network.2. Click the 'Tools' option on the menu bar, then click 'Share Workbook.' Wait for the 'Share Workbook' options dialog window to appear.3. Click and enable the checkbox next to the label that reads 'Allow Changes by More Than One User at the Same Time. This Allows Workbook Merging.' Click the 'OK' button.4. Click the 'File' > 'Save' option on the menu bar to save the permission changes to the workbook.Microsoft Excel 2007 or 20105. Open Microsoft Excel, then open...
How to Keep Track of Changes in Excel 2003
1. Open Excel from the Start menu on your toolbar.2. Open the file for which you'd like to track changes.3. Go to Tools > Track Changes.4. If you want to see the changes while you are editing, select 'Highlight Changes.' Then select the boxes labeled 'Track Changes While Editing' and 'Highlight Changes on Screen.' When making a lot of changes, you might not want this option on because it can be distracting.5. Decide who gets to make changes on your worksheet. Select the 'Who' tab and choose the users who can make changes. If you choose 'Everyone,' your file will be shared on the network.6....
Wednesday, July 18, 2012
How to Convert XML Files Into an Excel Spreadsheet
1. Open Excel.2. Click the Microsoft Office symbol in the upper left-hand corner. Select 'Open' from the menu.3. Find the XML document you want to convert and click 'Open.' If the document does not seem to be present where it should be, you may need to select 'XML Files(*.xml)' from the 'Files of type:' drop-down box.4. A small dialog box will open titled 'Open XML.' In this box select the radio button titled 'As an XML table,' then click 'OK.'5. If another dialog pops up that has a message relating to creating a 'XML schema,' simply click 'OK.' The XML file should be opened as a spreadsheet.6....
How to Print Grid Lines in MS Word
Show Gridlines1. Open the Word document that contains your table or labels.2. Select 'Table' and 'Show Gridlines' from the toolbar to make gridlines visible. You will see the grid lines on your page.3. Select 'File' and 'Print Preview' from the toolbar. The grid lines will not be visible.4. Press the 'Escape' key to return to your page.Add and Print Borders5. Select the table or labels containing the grid lines you want to print.6. Click on 'View,' 'Toolbars' and 'Tables and Borders' in the toolbar to open the 'Tables and Borders' toolbar.7. Expand the 'Border Color' button by clicking...
Tuesday, July 17, 2012
How to Use Paste Special in Microsoft Excel 2007
1. Select the data you would like to work with and choose 'Copy.' Copying can be performed by right clicking your mouse on the area or clicking the copy icon in the Home Tab of the Ribbon. The Ribbon is the group of icons and tools located directly above the work area.2. Click the 'Paste' drop-down menu located in the Home tab on the Ribbon. Select 'Paste Special.'3. Click on the cell or range of cells you would like your data to affect and click the'Paste' drop-down menu again. Choose 'Paste Special.'4. Choose the function you would like to perform from the 'Paste Special' dialog box...
How to Change Positive Data to Negative on Microsoft Excel
1. Click 'Start,' 'All Programs,' 'Microsoft Office' and then 'Microsoft Excel 2010.' Click 'File' and 'Open.' Locate your file and click 'Open.'2. Double-click the cell containing the number you wish to change from positive to negative. Enter the negative symbol before the first number and press 'Enter.'3. Select 'Number' from the drop-down list in the Number group of the Home tab of the ribbon bar. Click the expander arrow in the lower right of the Number group or 'Alt,' 'H,' 'F' then 'M' to show all the formatting options.4. Select one of four options for displaying negative numbers...
How to Add Numbers in Microsoft Excel if they Meet Certain Criteria
1. Select 'More Functions' from the 'Sum' drop-down menu in the 'Editing' section on the 'Home' tab. This opens the 'Insert Function' window.2. Type 'sumif' in the 'Search For A Function' field and click the 'Go' button. Verify that 'SUMIF' is highlighted on the 'Select a Function' pane and click the 'OK' button. This opens the 'Function Arguments' window.3. Click in the 'Range' field and enter the range of the data that needs to be tested; for example A1:A25 will test each cell starting with A1 and ending with A25 to see if it matches the criteria. The ':' represents 'through.'4. Click...
Monday, July 16, 2012
Animate Objects in Microsoft Excel
Download and Install Excel 3-D Animation Engine1. Open a browser and go to Microsoft's Download Center.2. Locate the 3-D Animation Engine under Office products add-ons.3. Click 'Download' and 'Run' to begin the program download.4. Click 'Yes' to download and run the program on your computer.5. Click 'Unzip' to open the file. Save the file to your desktop for easy access.6. Click 'OK' to complete the program's installation.Open and Configure Excel 3-D Animation Engine7. Double-click on the 3-D Animation Engine file to open it. The program will automatically open in Excel 2007.8. ...
How to Create Footers in Microsoft Excel 2003
1. Open the view menu. Scroll to the “View” tab on the command bar.2. Access the page header/footer menu. Select “Header And Footer” from the “View” menu. A header /ooter properties box will open.3. Select the footer. To add text to the footer, click on the “Custom Footer” button.4. Create text for the footer. Enter the desired text for all sections: left, right and center.5. Insert text into the footer. Click on the “Okay” button when you have finished entering the desired text. This will bring you back to the header/footer properties box.6. Insert the footer into the spreadsheet....
How to Unhide Columns in Microsoft Project
Unhide Columns in Microsoft Project1. 'Click' the column header to the right of the position in which the new column should appear, as in Image 1. This action will select and darken the entire column.2. 'Click' the 'Insert' option from the menu bar, as displayed in Image 2. This will cause a drop-down menu to appear.3. Click 'Column' from the drop-down menu. This will bring up a dialogue box entitled 'Column Definition,' as in Image 3. This feature allows you to select the column to be displayed.4. Click the down arrow to the right of the 'Field Name' text box. This will cause a list...
How to Crop Images in Excel
1. Start Microsoft Excel 2007 and open a workbook from your files that contains an image that you want to crop.2. Select the image you want to crop by clicking on it. The image will be surrounded by a thin, black border and light blue sizing handles once it is selected.3. Choose the 'Format' tab at the top of the Excel 2007 screen to display the Format ribbon. Locate the 'Size' section of the Format ribbon. This section is on the right side of the Format ribbon.4. Click the 'Crop' button and thick, black lines should appear on the corners and sides of the selected image.5. Hover your...
How to Remove Fractions in Excel
1. Launch Microsoft Excel 2010 and open the spreadsheet that contains or will contain the numbers that need to be displayed without fractions.2. Click a cell or highlight (Select) a range of cells by clicking on one cell and holding the mouse button down while dragging the cursor across a range of cells. Release the mouse button to select the highlighted range. You can also click directly on a column letter or row number to select the entire column or row. Add or remove from the current selection by holding down the 'CTRL' key while selecting the cells you do not want changed.3. Right-click...
How to Change Interval of Category Axis in an Excel Chart
1. Start Microsoft Excel 2007 and open a spreadsheet from your files that contains a chart to which you want to change the interval of the category axis.2. Click in the white area of the chart so the whole chart is selected. You can tell the entire chart is selected because a light blue outline will surround the entire chart.3. Select the 'Format' tab at the top of the Excel screen to display the 'Format' ribbon. Locate the 'Current Selection' group-it is the first group in the 'Format' ribbon.4. Click the 'Chart Elements' arrow to display the drop-down list. This is the top option in...
How to Create a Bar Chart in Microsoft Excel 2003
1. Make a table that contains all of the data you want represented in your bar chart. Be sure to include column and row labels and numerical quantities when making your table.2. Highlight all of the data in your table--including the column and row labels--and click the “Chart Wizard” button on the toolbar. The “Chart Wizard” button looks like a tiny column chart.3. When the Chart Wizard appears, select “Bar” from the “Chart Type” menu, then select the subcategory of bar chart you would like to create. Click the “Next” button.4. Use the “Data Range” box if you need to modify the data set....
How to Synchronize Spreadsheets in Excel
Linking Separate Excel Workbooks Together1. Open the workbook with the data you expect to be shared in Excel. This is the source workbook.2. Open the workbook you want the data to be replicated in. This is the destination workbook.3. Select the cell in the source workbook you want the data to come from, and enter 'Ctrl C' to copy it.4. Switch to the target workbook, and select the cell you want the data to appear in.5. Enter 'Ctrl V' to paste the reference in. If you examine the formula, you'll see that prior to the row and column entry, there will be a reference to the drive location...
How to Add an Analysis Toolpack in Excel 2003 GPO
1. Launch Excel 2003. To install the Analysis Toolpak on a version of Excel that was installed through a GPO (Group Policy Organization), you need a privileged account, or you must talk to your network administrator to unlock the procedure.2. Click on the 'Tools' menu item. Navigate through the drop-down menu and select 'Add-Ins'. A dialog box will appear; this will show all the add-ons currently available.3. Check the check box that says 'Analysis Toolpak.' You may need to click on the 'Browse' button to load it; it will be listed as Analysis.xla in the Add Ons folder for Excel. If...
How to Insert a Dynamic Date in Excel
1. Start Microsoft Excel 2007, and open a spreadsheet from your files into which you want to insert a dynamic date. Or, you can start a new, blank spreadsheet.2. Select the cell you want to insert the dynamic date into by clicking on it. The cell will be surrounded by a thick, black outline once it is selected.3. Type the formula '=today()' (without the quotation marks) into the selected cell. This formula will instruct Excel to insert the current date into the cell where the formula is entered.4. Press the 'Enter' key on your keyboard or the green check mark on the formula bar to input...
Sunday, July 15, 2012
How to Adjust the Color of Excel Bars in a Bar Chart
1. Open the formatted workbook and double-click the specific bar element you wish to change.2. On the 'Patterns' tab in the 'Area' section of the 'Format Data Series' dialogue box, click your preferred color. You can preview it in the 'Sample' box in the lower left corner.3. Click the 'OK' button. The chart will show the new color choice.4. Save the changes to the chart and workbo...
How to Import a Word Doc Into an Excel Spreadsheet
1. Open Excel and click the 'Insert' tab in the toolbar at the top of the page. You can import a picture, chart or file here.2. Click the 'Object' button and then click the 'Create from File' tab.3. Click 'Browse.' A list of your folders and files will open. Select the Word document you want to import and click 'Insert.'4. Click 'OK.' The Word document will now appear in your Excel spreadsheet. Click on the newly created object to move it to the desired position in the spreadshe...
How To Use the List Feature In Excel
Excel 20031. Open Excel 2003. Create a new spreadsheet or open an existing spreadsheet.2. Enter data headers in the first row. Enter the data below them.3. Click and drag to select your data including the headers.4. Open the 'Data' menu. Point to the 'List' menu and click 'Create List.'5. Check the 'My list has headers' check box. Click 'OK.'6. Click a header to sort the data based on that column.7. Type in the bottom row containing an asterisk to add a row to your list.8. Click and drag the handle at the bottom right corner of your list to expand the range.9. Click outside the...
Saturday, July 14, 2012
How to Make a Time Sheet
Microsoft Excel 20101. Select a Microsoft Excel 2010 template. Open Excel 2010 and select the 'File' tab. Click 'New.' Select the 'Time Sheets' category. Review the available time sheets. Preview the time sheet by clicking the image. A small preview and description appears in the right task pane. Download the template by clicking the 'Download' icon beneath the image and preview.2. Customize the template by adding personal details. These details include the time worked, your company name, address, manager name and the current date.3. Save the changes to the time sheet by clicking the...
How to Open a Wb3 File in Excel 2003
1. Open Excel 2003, and select 'Open' from the 'File' menu.2. Locate the WB3 file that you wish to open in the 'Look in' list.3. Click the name of the file, and click 'Ope...
How to Send Email Using Excel 2007 VBA
1. Start Microsoft Office 2007 and select the 'Developer' tab. Click on 'Visual Basic' and select 'Tools.' Click on 'References' check 'Microsoft Outlook 12.0 Object Library' and select 'OK.'2. Type the following to create a new sub:Sub sendEmail()Press 'Enter.'3. Type the following to create your variables:Dim oLookApp As Outlook.ApplicationDim oLookMail As Outlook.MailItem4. Type the following to assign the outlook object and create a new email:Set oLookApp = New Outlook.ApplicationSet oLookMail = oLookApp.CreateItem(0)5. Type the following to define the variables for your email:With...
How to Insert Hatching in Microsoft Excel Shapes
1. Right-click on the Excel 2010 spreadsheet shape in which you want to insert hatching.2. Click 'Format Shape' to display an options box.3. Click 'Fill' to display fill options.4. Click 'Pattern Fill' from the list to display available fill patterns.5. Click the hatching pattern you want to insert in the shape and then click 'Clos...
Friday, July 13, 2012
How to Create Rounded Borders in Excel
1. Open the Excel worksheet.2. Click the “Insert” tab on the command ribbon.3. Click the arrow under the “Shapes” button in the “Illustrations” group.4. Click the preferred shape, such as “Rounded Rectangle.” The cursor will convert to a “ ” sign.5. Click and drag the cursor on the worksheet where you wish to insert the rounded border. For example, click and drag inside a cell to create a row or column header.6. Click the sizing handles on the shape to resize the rectang...
How to Remove Protection From Cells in Excel 2003
1. Click 'Start,' then 'All Programs,' then 'Microsoft Office,' then 'Microsoft Excel 2003.'2. Click 'File,' then 'Open.' Locate, select and open your workbook that has the cells you wish to unprotect.3. Locate the protected cell.4. Click the cell, then click 'Format,' followed by 'Cells.'5. Click 'Protection.' Click the checkbox next to 'Locked' to remove the cell protection. Click 'OK' to close the Format Cells dialog. You have now removed protection from the ce...
Thursday, July 12, 2012
How to Insert and Size Diagrams in Microsoft Excel 2003
1. Access the diagram menu. Scroll to the “Insert” tab on the command bar and select “Diagram.”2. A diagram selection box will open where you can select six different types of diagrams: organization chart, cycle diagram, radial diagram, pyramid diagram, Venn diagram and target diagram. Choose the desired diagram by left-clicking on it and then click the “OK” button to insert the diagram.3. Place the diagram. To move the diagram around, left-click on the gray border and hold as you drag the diagram where desired on the spreadsheet.4. Set diagram attributes. You can add levels to your diagram...
How to Merge Data From Several Excel Workbooks
1. Click the Microsoft Office button on the screen's top-left corner, and select 'Excel options.'2. Under 'Customize,' click 'All Commands' in the 'Choose commands from' list.3. Check the box next to 'Compare and Merge Workbooks.' Click 'Add,' and then click 'OK.' The 'Compare and Merge Workbooks' icon will now appear in the quick access bar at the top of the window,4. Move all workbooks to a single folder. If they share a name, rename them as necessary.5. Open one workbook. Click the 'Compare and Merge Workbooks' icon.6. Select the other workbooks whose date you want to merge. To...
How to Calculate Negative Times in Excel
1. Open your Excel 2010 worksheet. Click on an empty cell anywhere on the worksheet.2. Type '-'1:30'' into the cell. The number '-.0625' should appear. Right-click the cell and choose 'Format Cells.' Choose 'Time' from the list on the left and click 'OK.' The number changes to '#####.'3. Click on the 'File' tab at the top of the screen. Choose 'Options' from the list that appears.4. Click 'Advanced' on the list on the left side of the Options window. Scroll down to the bottom of the 'Advanced' screen and look for the 'When calculating this workbook' heading. Click the box next to 'Use...
How to Make an Exponent on Excel
Insert Exponent into Formula1. Open the Microsoft Excel spreadsheet that contains the formula where you want to add an exponent.2. Click on the cell that contains your formula. The formula will appear in the bar above the Excel spreadsheet. Click on this bar and place your cursor where you want to add the exponential number.3. Enter the following into the formula bar, without quotes: 'power(x,y)' where 'x' is the desired number and 'y' is the exponent. If needed, you can wrap the entire 'power' function in parentheses. Additionally, you can write this in shorthand as 'x^y', without quotes.Have...
Wednesday, July 11, 2012
How to Open a Password Protected Microsoft Office Excel Sheet
1. Browse to the location where the password-protected Excel document is saved using Windows Explorer.2. Double-click the document you wish to open. Excel will launch and display a dialog box explaining that the document is protected and requires a password.3. Enter the password in the space provided, and hit 'OK.' This will open the document and allow you to view and edit the conten...
How to Do an Excel Chart With Multiple Legends
1. Open your Microsoft Excel spreadsheet that already has your data entered into the cells.2. Click on cell A1, followed by 'Control' and 'A.' This is a shortcut that highlights your complete set of data.3. Click 'Insert' on your toolbar, followed by 'Bar,' 'Pie' or another form of chart that you like. The chart will appear in your spreadsheet.4. Click on the chart. Click 'Layout' and then the Legend icon. Click the button that specifies the your legend's orientation such as 'Show Legend at Right.' The legend will appear in your chart.5. Click on the legend table. Click 'Control' and...
How to Make a Graph From CSV
1. Launch Excel 2010, click 'File' in the top menu and select 'Open.' Click on the file types drop-down menu at the bottom right of the File Open dialog box. You will need to select either 'All files (*.*)' or 'Text files (*.prn, *.txt, *.csv)' because by default, Excel filters your file choices to show only its native file types. Navigate to your file and click 'Open.'2. Find an empty spot in your spreadsheet and click a cell. The exact location is not important, as you are selecting a place to put your chart while you work on it.3. Click 'Insert' in the top menu and select any one of the...
How to Make a Watermark in Excel
1. Open the Excel workbook in which you want to create a watermark.2. Click View on the Excel toolbar, or use the keyboard shortcut 'Alt' plus 'V.'3. Click the View menu and select Header and Footer.4. Choose Custom Header in the Page Setup dialog box that appears. Click the 'Center Section' so your cursor is active in the corresponding field.5. Click the 'Insert Picture' button on the right; this button has a picture of mountains and the sun. Navigate to the picture that you wish to use as a watermark and double-click the graphic to insert it.6. Click the 'Format Picture' button on...
Subscribe to:
Posts (Atom)