1. Learn the syntax for Concatenate. It is Concatenate (text_1,text_2,...text_n) where text_1,text_2,...text_n are 1 to 30 text items that will be joined together. These values may be numbers, text strings or references to single cells.2. Use the ampersand () as the calculation operator. This method also may be used to join text items instead of the Concatenate function. For example, =A1A2 will return the same result as =Concatenate (A1,A2).3. Look at an example for Concatenate. Set A2=salmon, A3=species, A4=25 and enter =Concatenate('Stream population for ',A2,' ',A3,' is ',A4,'/mile')...
Browse » Home » Archives for February 2011
Monday, February 28, 2011
How to Preview an Excel Worksheet
1. Start Microsoft Excel and open a spreadsheet that you are ready to print.2. Choose the 'File' menu. Select 'Print Preview' to enter 'Print Preview' view.3. Navigate through your Excel spreadsheet pages by using the 'Next' and 'Preview' buttons at the top of the 'Print Preview' view.4. Zoom on your Excel spreadsheet by clicking the 'Zoom' button while in the 'Print Preview' view. Click on 'Zoom' once to magnify the spreadsheet page you are on. Click it again to return to full-page view.5. Print the spreadsheet by clicking the 'Print' button to exit 'Print Preview' and bring up the...
How to Modify Pivot Table Data
Change Data1. Locate the source data that your pivot table is based on. This is normally at the top left corner of the worksheet, or on a separate worksheet that you can access from the tabs at the bottom left corner of the Excel window.2. Make the required modifications to your data.3. Navigate back to the Excel pivot table and click on any cell within the table.4. Click the 'Options' tab at the top of the screen, then press the 'Refresh' button in the middle of the toolbar. Your pivot table will now reflect the changes you made in the source data.Add New Data5. Locate and navigate...
Sunday, February 27, 2011
How to Identify Duplicate Items in Excel
1. Click on the column name that you want to check for duplicates. You can also select a range across several columns by selecting the top-left cell in the range and then holding 'Shift' while selecting the bottom-right cell.2. Select the 'Home' tab at the top of the Excel window. Click on the 'Conditional Formatting' button in the 'Styles' area of the ribbon.3. Move your mouse over 'Highlight Cell Rules' in the drop-down menu and then choose 'Duplicate Values.'4. Choose the type of color to apply to duplicate cells by using the drop-down box on the right. It defaults to a light red color...
How to Customize Columns and Rows in Excel
Customize the Columns1. Click and drag your mouse over the columns you want to customize. Alternately, click the letter directly above the column to automatically select all the cells in this area.2. Select the 'Column' option under the 'Format' drop-down menu, then click the 'Width' option to enter a distinct value for this measurement.3. Use the 'Formatting Palette' (Mac), the 'Formatting Toolbar' (Windows) or the 'Font' group on the 'Home' tab (Excel 2007) to personalize the column. Click the 'Fill Color' option to apply a background color, or click the 'Pattern' or 'Pattern Color' box...
How to Convert Quattro Pro for Windows to Excel 2007
1. Download the Quattro Pro file converter from Microsoft. The link is in the Resources section below.2. Double-click on the file you download (qp7conv.exe). Follow the download instructions on the screen.3. Open Excel 2007. Click on the Office Button (the round icon at the upper left with the Microsoft Office logo on it) and select 'Open.' On the drop-down menu for 'file type,' select Quattro Pro. This will allow you to open any Quattro Pro file (with extensions *.wb3 or *.wb2) into Excel 20...
How to Open to a Specific Directory in Excel 2007
1. Click the 'Office' button in the upper left of Excel.2. Select 'Excel Options.'3. Click 'Save' on the left hand menu.4. Change 'Default file location' to the specific directory you want to use. Click 'OK.' Now if you open or save a file, your new directory is the one that will op...
Saturday, February 26, 2011
How to Insert Page Numbers in Excel 2007
Adding Page Numbers1. Open the worksheet to which you would like to add sequential numbering, and select the 'Insert' tab from the text group.2. Choose 'Header and Footer' and then select 'Click to add header,' or 'Click to add footer,' depending on where you would like the numbers to be located. This will display the 'Header and Footer Tools' option.3. In order to select the location of the page number, you will choose the left, center or right section of the header or footer you have decided on.4. Click 'Page Number' from the 'Header and Footer Elements group.'5. Once you see 'Page'...
How to Link Cell Sheets in Excel 2007
1. Type '=' in the cell that you want the link to occur in.2. Click the worksheet, located at the bottom of the page, that contains the cell you want to link.3. Select the cell you want to link and press 'Enter.' Excel then takes you back to the page where you created the link. By selecting the cell, the Excel automatically formats the 'sheetname!celladdress' for y...
How to Calculate Compound Annual Interest in Excel 2003
1. Label cell A1 'Original Principal,' cell A2 'Compounding Periods Per Year,' A3 'Annual Interest Rate (as a percentage)' and cell A4 'Annual Interest.'2. Enter the amount of money you put into the account in cell B1, the number of times per year into B2 and the annual interest rate expressed as a percentage in cell B3. For example, if you started with $1,000 and your bank pays 3.5 percent interest, compounded on a monthly basis, you would enter '1000' in cell A1, '12' in cell A2 and '3.5' in cell A3.3. Enter the formula '=B1*(1 B3/1200)^B2-B1' in cell A4. When you enter this formula, the...
How to Use Checkboxes to Control Formulas in Excel
1. Open your Microsoft Excel spreadsheet. Locate the check box that you want to use, if you already have one in place. Alternatively, click 'Developer,' 'Insert,' then click the check box to insert one. Right click the check box and click 'Format Control.' Click the cell button next to 'Cell link,' and designate a cell, then press 'Enter.' Choose a cell in a row or column that doesn't contain any other data as you can hide this row or column if you wish. Click 'OK.'2. Click the check box, and you will see the words 'TRUE' or 'FALSE' appear in the designated cell. 'FALSE' indicates an unchecked...
How to Set Up a Drop
1. Make a list of the items that you want to be listed in the drop-down menu. It is best to do this in a separate worksheet from where you want to set up the drop-down menu box, so that it doesn't clutter up the spreadsheet. To access different worksheets, click on the tabs at the bottom of the page. Beginning in cell A1 of the new worksheet, enter each item for the list in a separate cell.2. Name the list by first highlighting the list of items. Then click in the 'Name Box' just above column A. Enter in a name for the list--Animals, for example. Once the list is created and named, it can...
How to Create Labels Using Microsoft Word
1. Open a new document in Microsoft Word.2. Click on “Tools.”3. Place the cursor on “Letters and Mailings” and click.4. Drag the cursor to “Envelopes and Labels…” and click.5. Open the “Labels” tab.6. Type the information that you wish to appear on the label.7. Check your printer to ensure that it is loaded with blank labels.8. Click pri...
Friday, February 25, 2011
How to Update the Links in Excel 2007
1. Open the Microsoft Excel 2007 spreadsheet file on your computer that you want to update links for.2. Click on the 'Data' tab and then click on the 'Edit Links' option from the 'Connections' group.3. Click on the 'Update Values' option and the links will automatically be updated. Click on the 'Close' button.4. Click on the 'Data' tab and then click on the 'Edit Links' option again. Click on the 'Startup Prompt' option.5. Click on the box next to the 'Ask to update automatic links' field so that it's selected. Close out of the dialog box, and now you will receive an automatic message...
How to Use Formula Auditing in Microsoft Excel 2007
1. Click in the cell to which you would like to trace the connections. For example, if you would like to know which cells are part of a particular formula, click in the cell containing the formula.2. Click on the 'Formula' tab in the Ribbon. The Ribbon is the group of icons and tools located directly above the work area.3. Select the auditing option you want to appear on your worksheet. Trace Precedents will show cells included within a formula. Trace Dependents will show which formula a cell is included in. The arrows that Excel adds to your work sheet will print on your document.4. ...
How to Check for Duplicate Text in Excel
Excel 20071. Go to Microsoft Excel 2007 and open the worksheet you want to check.2. Insert a column header for the column you’re checking. Then select the column and click on the “Data” tab.3. Click on “Remove Duplicates” in the “Data Tools” group.4. Place check marks by “My data has headers” and the columns you want to verify. Then click on “OK” to remove the duplicates.Excel 20035. Open your document in Excel 2003. Make sure the column has a column header, then select the column you want to verify.6. Select “Data,” “Filter” and “Advanced Filter” from the toolbar.7. Accept the selected...
How to Convert Xlsb to Xls
1. Double-click the XLSB file on your computer. This opens the file and loads Microsoft Excel on your desktop.2. Click the 'File' menu button and then click 'Save As'. This opens your 'Save' dialog window. Select 'XLS' from the 'File type' drop-down box.3. Click 'Save'. The file is saved to your hard drive and converted to the XLS file form...
How to Install Microsoft Excel Features in Excel 2003
1. Click the 'Start' or Windows button in the lower left corner of the screen, then select the 'Control Panel' option.2. Choose the 'Programs and Features' or 'Add/Remove Programs' option, depending on which version of Windows is installed on your system.3. Scroll to Microsoft Office 2003, then select the 'Change' option.4. Choose the option to 'Add or Remove Features', then click the 'Next' button.5. Check the box next to Microsoft Excel, as well as the box next to 'Choose advanced customization of applications'. Click the 'Next' button.6. Click the ' ' sign next to Microsoft Office...
Thursday, February 24, 2011
How to Disable the Automatic Updates for MS Office
1. Click the Windows 'Start' button and type 'Windows Update' in the search bar. Click on 'Windows Update' (it should be the first item listed).2. Click 'Change Settings' located on the left hand column.3. Click on the option list under 'Important Updates.' It will currently be set to 'Install Updates Automatically' but you can change this. There are a few options such as not downloading updates at all, or having the computer only download them automatically (but not install them without you choosing to do so). Pick the option that you feel most comfortable with; as long as you don't select...
How to Automatically Number Rows for an Excel Spreadsheet
1. Open the Microsoft Excel application on your computer. Click on the first cell where you want to establish a numbered pattern.2. Type the beginning of the pattern in the first cell, like a “1.” Click on the next cell to continue the sequence by typing a following number in the sequence, like “2.”3. Highlight the cells that you put numbers in and click on the fill handle in the lower right corner of the highlighted selection. Drag the handle down to complete as many rows as you want and the rows will be automatically numbered.4. Click on the “Tools” option from the top tool bar menu...
How to Type a Negative Number in MS Excel
1. Launch Microsoft Excel 2010.2. Click on a blank cell.3. Press the 'Minus' sign ('-') on the keyboard and immediately press the desired numbers on the keyboard. Press 'Enter,' 'Tab' or an arrow key to save the number, and move to an adjacent cell.4. Right click on the cell with the negative number displayed. Click 'Format Cells' on the context menu.5. Click the 'Number' tab and click the desired format name in the left column to display options for that particular number format. Click the desired options for the number on the right. The options will vary by the type of number format...
Wednesday, February 23, 2011
How to Calculate Covariance in Excel 2007
1. Run Microsoft Excel 2007 and open the workbook that contains your data arrays.2. Determine the cell addresses for your two arrays. For example, if your arrays are contained in the first 10 columns of rows A and B, then the cell addresses for those arrays are A1:A10 and B1:B10.3. Click a blank cell that you would like to use to display the covariance between your data arrays.4. Type the following in the cell:=COVAR(A1:A10,B1:B10)Substitute the cell addresses for your two data arrays in place of the sample addresses in the example.5. Press 'Enter.' The cell displays the covariance between...
How to Calculate Pecentage Using Excel
1. Browse to the Excel file containing the data for which you wish to calculate a percentage, and then double-click that file to open it in Excel 2010.2. Locate the cells containing the data that represents your 100 percent value and the cell to which you are comparing it.3. Note the cell names for those two particular cells. For example, if one piece of data is in the upper-leftmost cell, then the cell name would be 'A1.'4. Click inside the cell in which you want to display the percentage.5. Type '=AA/BB,' where 'BB' is the name of the cell containing your 100 percent value, and 'AA'...
How to Upgrade to Excel 2007
1. Insert the Microsoft Office 2007 setup disc into the computer. The installer should automatically launch. If it does not, open the 'Computer' folder, open the Microsoft Office disc and double-click the 'setup.exe' file.2. Enter your Product Key when prompted. Accept the license agreement.3. Click 'Custom.' Select 'Remove all previous versions' or 'Remove only the following applications: > Microsoft Office Excel.' The former will remove all applications in the currently installed Microsoft Office suite.4. Select 'Installation Options.' Select 'Microsoft Office Excel > Run from...
Tuesday, February 22, 2011
How to Find the Regression Equation in Excel 2007
From Graphing1. Select all the x (independent variable) and y (dependent variable) data.2. Click on 'Insert' in the top menu bar, then on 'Scatter.' Click on any of the five chart options.3. Click once on the graphed line. Right-click the line and choose 'Add Trendline...' from the menu that appears.4. Click the check-box for 'Display Equation on chart' at the bottom of the window that appears. Click 'Close.' The regression equation for the data appears on the chart. With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the equation that appears is 'y = 2.4*x 7.2.'Built-in...
How to Hide Gridlines in Excel
1. Open Microsoft Excel.2. Click on the 'Office' button. Select 'Open' to load your saved spreadsheet, or select 'New' to start a new project.3. Click on the 'View' tab. Tabs are located at the top of the main Excel window, and clicking on a tab does not take you away from your spreadsheet.4. Click on the 'Gridlines' check box to deselect it. The box is checked by default, and deselecting the box turns off gridlines for the spreadsheet. Changes appear immediately within your Excel spreadshe...
Monday, February 21, 2011
How to Duplicate Sheets in Excel 2007
1. Open Excel 2007 and click the 'Office' button. Select the 'Open' option. Browse the files and locate the Excel workbook. Click the workbook and the 'Open' button. The workbook opens.2. Right-click on the spreadsheet and click the 'Copy' option. The 'Move Or Copy' dialog box appears. Select the workbook to duplicate the worksheet to in the 'To Book' drop down list. Click the worksheet where this copied worksheet will appear before in the 'Before Sheet' drop down list.3. Click the 'Create A Copy' check box. Click 'Ok' to complete the co...
How to Convert a Number to Text in Excel 2007
1. Open the Excel spreadsheet that has the numbers you want to convert to text.2. Use your mouse to highlight the cell(s) you want to convert to text.3. Click the 'Home' tab located in the Excel ribbon at the top of the page and look to the 'Number' section of the ribbon.4. Click the arrow next to 'Number' on the ribbon to open the 'Format Cells' dialog box.5. Click the 'Number' tab when the 'Format Cells' box opens. A list of options you can convert the numbers to is located in the left pane of the dialog box. Some options you can convert the numbers you selected to include percentages,...
Sunday, February 20, 2011
How to Delete Every Other Row Automatically in Excel 2003
1. Open a new workbook in Excel and type the number '1' in cell A1. Skip cell A2 and type '2' in cell A3. Repeat this process until you have the numbers 1 through 5 entered in cells A1 through A9. The following cells should have the following numbers: '1' for A1, '2' for A3, '3' for A5, '4' for A7 and '5' for A9.2. Click 'Tools' and select 'Macro.' Click on 'Visual Basic Editor.'3. Select the 'Insert' tab and click 'Module.'4. Copy the following macro and paste it in the Visual Basic sheet:Sub Delete_Every_Other_Row()' Dimension variables.Y = False ' Change this to True if you want to'...
Saturday, February 19, 2011
How to Delete Multiple Overlapped Checkboxes in Excel
1. Start Microsoft Excel. Press 'CTRL O,' select your workbook and then click on 'OK.'2. Click 'Find Select' under the Home tab on the ribbon toolbar. Choose 'Selection Pane' from the menu. This opens a list panel at the right side of the Excel window.3. Click 'Show All' near the bottom of the Selection Pane.4. Hold down 'CTRL' and then click on the items on the list that correspond to the overlapped checkboxes. To de-select an item, click on its name again.5. Press 'DELETE' to remove the checkboxes from your she...
How to Insert Information in an Excel Spreadsheet
Direct Data Entry1. Launch Microsoft Excel 2010.2. Click in one of the cells of the spreadsheet and type a number or word, and then press 'Enter.'3. Type another number or word and press 'Enter' again to continue to fill in the current column. Alternately move to a different cell by clicking the mouse on that cell or by using the 'Tab' key or one of the arrows on the keyboard to navigate to a different cell.Link to Existing Data4. Launch Microsoft Excel 2010. Open the spreadsheet that contains the cells where data from other spreadsheets will be displayed.5. Open the Excel spreadsheet...
How to Use Forms for Data Update in Excel
Use Forms in Excel 20031. Click on a cell in the data table you want to update.2. Go to the 'Data' menu and select 'Form.' A data form opens.3. Scroll through the records to find the data you want to update. You can also search for a specific entry by typing in specific criteria such as a name or record number. Click 'Find Next' to go to that record.4. Press the 'Tab' key to move through the fields in the form. Update or edit any fields that you want to change.5. Press 'Enter' to apply the changes and move to the next record. Close the form window when you are done updating the data...
How to Perform Linear Regression in an Excel Spreadsheet
Excel 20031. Highlight your chart data by clicking at the top left of the data and then dragging the mouse to the bottom right.2. Graph a scatter plot from your data. Click on the 'Insert' menu, then click on 'Chart' to start the Chart Wizard. Click on 'XY (Scatter)' in the Chart Type box.3. Add a trend line to the graph. Click on 'Chart' and then click on 'Add Trendline.' A pop-up window will appear.4. Click on the 'Linear' Trend/Regression Type icon.5. Click on the 'Options' tab, then check the 'Display equation on chart' box.6. Click on 'OK' to display the regression line on your...
Friday, February 18, 2011
How to Create Free Dashboards
Excel Dashboards1. Upload data into an Excel spreadsheet. The first row should contain content headings, and the following rows are for data. Be sure to name the spreadsheet without using spaces or other characters; instead replace spaces with a '_' symbol.2. Create an outline of how you want the dashboard to look. This will depend on what data you are tracking and who will be looking at the dashboard. You will use this later to place the charts and graphs in the final product.3. Write formulas in Excel to turn your data into charts. The formula you need will depend on what you are charting...
How to Make Excel 2003 Not 2007 Your Default Program
1. Open the Windows registry by clicking 'Start,' selecting 'Run,' typing 'regedit' into the 'Open' box and clicking 'OK.'2. Navigate to 'HKEY_CURRENT_USER>Software>Microsoft>Office>12.0>Excel' in the registry by clicking the arrow to the right of each of these folders.3. Right-click the 'Options' key inside the 'Excel' folder, point to 'New' and select 'DWORD Value.' Type 'NoReReg' over 'New Value' to rename it.4. Right-click 'NoReReg' and select 'Modify.' Type '1' into the 'Data Value' box. Click 'OK.' Close the Windows registry.5. Open Microsoft Excel 2003. Click 'Help'...
How to Format in Microsoft Excel 2007
1. Open your Microsoft Excel file.2. Select the cells you want to format. Click and drag to select multiple cells. Click the top letter to select an entire column. Select the side number to select an entire row.3. Choose the 'Home' tab on the top menu. Click the drop-down box under the 'Number' box. Click the option for 'More Number Formats.'4. Click the tabs to change the number, alignment, font, border, fill and protection. The 'Number' tab is where you change the type of number used in the cell such as currency, date or time. The 'Alignment' tab is where you change you text direction...
How to Create a Survey in Excel
Enable Developer Tab1. Open Microsoft Excel and click 'File' tab.2. Click 'Options' and click 'Customize Ribbon.'3. Click 'Main Tabs' and check the 'Developer' box. Click 'OK' to close the dialog box. The Developer tab appears in your Excel ribbon.Insert Check Box4. Open the Excel spreadsheet that you want to create a survey.5. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.6. Click 'Insert' and a drop-down list appears on your screen. Click 'Check Box' under the 'ActiveX Controls' heading.7. Click the cell where you want the upper-left corner of the check...
Thursday, February 17, 2011
How to Enable Autosave in Excel 2007
1. Click the 'Office Button' on the top left side of your spreadsheet. Click 'Excel Options.'2. Click 'Save' on the left side of the 'Excel Options' dialog box.3. Check the box labeled 'Save AutoRecover information every x minutes.'4. Select the time increment you wish to have Excel autosave your worksheets. This number ranges from one to 120 minutes.5. Click 'OK' at the bottom of the 'Excel Options' box. This procedure enables autosave on this worksheet and every worksheet you create in the futu...
How to Sort by Date on Excel 2010
1. Open the Excel 2010 workbook to the worksheet that contains the data you want to sort.2. Click the column letter (A, B, C, ...) that holds date values, to highlight that range of cells.3. Select the 'Data' tab in the Ribbon at the top of Excel. Look for the Sort Filter group.4. Click the 'AZ' icon to sort dates from earliest to latest (January, February, March). Otherwise, click the 'ZA' icon to sort dates from latest to earliest (March, February, Januar...
How to Disable Addins in Excel 2007
1. Click the 'Microsoft Office' button in the top left corner of the Excel screen, select 'Excel Options' and choose 'Add-Ins.'2. Scroll down the list of add-ins displayed in the new window until you locate the add-in you wish to disable. Make a note of the name in the Type row next to the add-in's name.3. Select the type you noted in the previous step in the Manage box, then click 'Go.'4. Uncheck the check box next to the name of the add-in in the new window that pops up and click 'OK.' This will disable the add-in. Repeat this process as necessary until you have disabled all the add-ins...
How to Password Protect Excel 2007
1. Open your Excel document if it's not open already.2. Click the 'Office' button in the upper left corner of the window.3. Highlight 'Prepare.' A list of options will appear on the right.4. Click 'Encrypt Document.'5. Type any password of your choosing into the 'Password' text box. The password can be any length up to 255 characters. Longer passwords are more secure because they're harder for people (and computers) to guess, but a more complex password may be more difficult to type and remember.6. Click 'OK.' Retype the password in the confirmation window that appears.7. Click 'OK'...
How to Convert MS Excel 2003 Files to PDF Format
1. Install a PDF writing program to your computer. While most PDF writing programs are suitable for converting Excel files to PDF, some are available for free and others have a fee associated with the download. Adobe Acrobat programs (see Resources section) are popular, but also expensive ($199 to $799). Free programs that work just as well as Acrobat for conversion purposes are also available. PDFill or CutePDF, both of which may be downloaded for free from their respective Web sites, are two options.2. Open the Excel 2003 document that you want to convert to PDF. Click the 'File' menu and...
How to Expand Collapse in Excel 2007
Hide Rows and Columns1. Open the saved Excel document.2. Click on the rows or columns you wish to hide from view.3. Click on the 'Home' tab on the Ribbon.4. Click on the 'Format' button in the 'Cells' group. A drop-down list will appear.5. Point to the 'Hide Unhide' section to display a list.6. Click on the 'Hide Rows' or 'Hide Columns' option. The selected rows or columns and the respective headers are temporarily removed. A short, bold line between the row or column headers indicates where the rows or columns are hidden. For example, a line between headers A and C indicates column...
Wednesday, February 16, 2011
How do I Recover a Microsoft Excel Spreadsheet That I Accidentally Saved Over?
1. Try pressing the 'Ctrl' and 'Z' keys together on your keyboard to undo the latest keyboard strokes. If you've just made the change, this keyboard combination may work for you.2. Check the folder where you have saved the Excel spreadsheet to for a temporary file. There's a small chance that the save process got interrupted and the original file still exists in the folder. For example, if you saved your document in your 'My Documents' folder, check that folder for a duplicate file name with the file extension .tmp. If you find it, double-click on the icon to open the original file and save...
How to Look Up a Date in MS Excel 2003
1. Click an empty cell where you want the date to be displayed, for example if the first row is empty, click 'B2.'2. Type '=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).'Type the location such as 'A1' instead of 'lookup_value.' This will be the cell where you type your search term. Type the coordinates of your table, such as 'A2:D6' instead of 'table_array.' Type the column number you wish to see such as '3' instead of 'col_index_num.'Type 'FALSE' instead of 'range_lookup' to find an exact result.3. Type a search term into cell A1. The VLOOKUP function in cell B2 will then...
How to Group Worksheets in Excel
1. Start Microsoft Excel 2007, and open a workbook that contains multiple worksheets that you would like to group together.2. Locate the sheet tabs for the worksheets. They are on the bottom, left side of the Excel screen. You may have named them or they may be named as their default names: 'Sheet1,' 'Sheet2' and so on.3. Select the first tab of the sheet that you would like to include in the group of worksheets.4. Press down the CTRL key on your keyboard and use your mouse to click on the next sheet that you would like to be include in the group. Continue to do this until all the sheet...
How to Use Excel's Solver
1. Make sure that the Microsoft Excel solver add-in is available. If not select 'Options' from the main Menu and choose 'Solver Add-in' from the options menu. Restart Microsoft Excel and the Solver option should be available under the data menu.2. Construct a model that contains and specifies variables, constraints and an objective.3. Determine the variables on the solver construct model. These numbers can change given a specified constraint or constraints. For example, the variable cells or changing cells could be the amount of money spent by each department of a company. These cells do...
How to Scan Documents Into Excel
1. Click on the 'Start' button on the taskbar and select 'All Programs' from the next menu. Select 'Microsoft Office' and 'Office Tools' from the subsequent page.2. Click the 'Microsoft Office Document Imaging' option from the screen. Click 'File' at the top of the page and click the 'Open' tab. Select the file that you want to scan into Excel and click 'Open' once more. Select the icon with an eyeball on it from the lower-right corner of the screen to run the document through the OCR (Optical Character Recognition) process. This process is required prior to putting the document into an Excel...
Tuesday, February 15, 2011
How to Make One Checkbox Uncheck Another in Excel 2003
1. Open Excel 2003, and launch your Excel spreadsheet. Make sure the Forms toolbar is switched on by clicking 'Views > Toolbars > Forms.'2. Click the 'Combo Box' form control, and drag it to your worksheet.3. Right-click the box, and click 'Format Control.' Set the 'Input range' to the range of cells that contains the check box values.4. Set the 'Cell link' to the cell that will return the result of your check box. Click 'OK' to insert the combo check b...
How to Change the Password on a Shared Excel Document
1. Run Excel and open the shared document.2. Click the 'Review' tab and click 'Unprotect Shared Workbook' under the 'Changes' group.3. Type the workbook's password and click 'OK' to remove password protection.4. Click the 'Share Workbook' link located on the 'Changes' tab.5. Uncheck 'Allow changes by more than one user' on the 'Editing' tab. Click 'Yes' to unshare your Excel document.6. Click 'File' in the menu bar, then click 'Info' and 'Encrypt with Password.'7. Type a new password in the 'Password' box and click 'OK' to save your new password.8. Click the 'Review' tab and click...
How to Write Formulas For Excel 2003
1. Launch Excel 2003 and open the worksheet that you would like to add a formula to. Click to highlight (put a black box around) the cell that you would like to display the result of your formula in when it is complete, and press the equal sign (=) key. You will notice that the equal sign appears both in the cell that you have clicked and in the formula ('fx') bar at the top of the screen.2. Click another cell to reference it in your formula, type a number to enter a constant value, and type math signs on the keyboard to manipulate those values. For example, if you wanted the cell that you...
How to Use Subtotals in Excel
How to Use Subtotals in Excel1. Open the Excel workbook in which you want to subtotal data.2. Select data that you want subtotaled by left-clicking with your mouse in the upper left-hand corner of the data and then using your mouse to select to the right and down to highlight all of the data.3. Select 'Data' from the main menu. Select 'Sort' from the drop-down menu.Data must be sorted to perform the subtotals function. Sort based on what you want the subtotals categories, not what you want subtotaled. For example, if you want to subtotal purchases by state, sort by state, not the purchase...
How to Put Images in Cells on Excel 2007
1. Click the 'Insert' tab on the Office Ribbon.2. Select a cell on your spreadsheet by clicking it with the cursor. The picture you insert will appear at this location.3. Click the 'Picture' button on the 'Illustrations' panel of the Office Ribbon to insert a picture into your spreadsheet. Use the 'Open Picture' dialog to select a picture from your computer and click 'Open' to insert the picture onto your spreadsheet.4. Resize the cells around your image. Click and drag the row and column headers so that your image is contained within the border of a single cell. If your image is large,...
How to Show Duplicate Records in Excel
Formatting1. Click your first data (not heading) cell and select 'Conditional Formatting' from the Format menu. These cells are identifiable by location. A cell in the first column and first row is 'A1.' Frequently. Excel users may reserve the top row ('1') to create headings for their data. If your 'A1' cell includes the heading 'Year' and your 'A2' cell includes the data '2010,' you will want to click 'A2.'2. Select 'Formula Is' from the drop-down menu on the 'Conditional Formatting' box and type '=COUNTIF(A:A,A2)>1' into the field that appears.3. Click the 'Format' button in the Conditional...
Monday, February 14, 2011
How to Create a Consolidated Spreadsheet
1. Log on to your computer and open Microsoft Excel. Create a new spreadsheet and give it a descriptive name like 'Consolidated Budget' or 'Consolidated Spreadsheet.'2. Add another sheet if you need to. Make sure you have enough sheets to hold the data you will be consolidating. For instance, if you want to use your spreadsheet to add up quarterly data, you would need a total of five sheets—one for each quarter and fifth sheet to hold the consolidated data.To add a sheet in Excel 2003, click on the 'Insert' menu and choose 'Worksheet' from the menu. To add a sheet in Excel 2007, click on the...
How to Print to 24 X 36 Paper in Excel
1. Open up your spreadsheet in Microsoft Excel. Then click on the 'File' drop down menu and choose 'Page Set-Up' and another menu will open.2. Select 'Landscape' if you want the document printed horizontally or 'Portrait' if you would the document printed vertically.3. Choose to have your document fit on '1' page wide by '1' page tall under the 'Scaling' option.4. Select the 'ARCH D' paper size and then press 'OK.'5. Click the 'File' drop down menu again. Select 'Print' and another menu will open. Choose to print as an Adobe PDF from the drop down menu.6. Click on the 'Properties'...
How to Protect and Share a Workbook in Microsoft Excel 2003
1. Open the workbook by running your application and then scrolling to the “File” tab and selecting “Open.”2. Access the security properties menu. Scroll to the “Tools” tab on the command bar and then select “Protection.” A submenu will open.3. Select “Protect and Share Workbook.”4. Set protect and share properties. Make sure you check the box labeled “Sharing With Track Changes.” This will allow you to see all changes made by users who work on the book. Finally, enter the desired password in the “Password” field and then click 'OK' to protect and share the workbo...
Sunday, February 13, 2011
How to Disable the Functions of a PivotTable
1. Open Excel and then open the workbook that contains the PivotTable you would like to disable.2. Right-click on any cell within the PivotTable and then click 'PivotTable Options.'3. Click the 'Display' tab and then uncheck the selections for 'Show expand/collapse buttons,' 'Show contextual tooltips,' ' Display field captions and filter drop downs,' 'Classic PivotTable layout (enables dragging of fields in the grid)' and 'Display item labels when no fields are in the values area.'4. Click 'O...
How to Create Pivot Tables Using Excel 2007
1. Double-click the Excel 2007 file that contains your source data.2. Edit your source data in Excel 2007 so there are no blank rows or columns in the data that you want to use to make a Pivot Table. Also ensure that the top row contains column headers. If there are no column headers, right-click on the first row of the data and choose 'Insert' to insert a new row. Type in column headers in this row.3. Click on the top-left cell in the data source and hold down the mouse button. Drag the mouse to the bottom-right cell and release the button.4. Click the 'Insert' tab on the toolbar and...
How to Use Microsoft Excel 2003
1. Open Excel 2003. Click 'Start,' 'All Programs,' 'Microsoft Office' and then 'Microsoft Office Excel 2003.'2. Select a cell by left-clicking on it. You can select any cell to edit the value on the cell with the left mouse button. After selecting a cell, you can navigate to other cells using the arrow keys.3. Enter a value into a cell. After selecting a cell, type words, numbers or both and then press the enter key. The value will appear in the cell; you can continue selecting cells and entering data to create large tables of data.4. Switch and edit worksheets. Click the different worksheet...
How to Make a Frequency Table on Microsoft Excel
1. Open Microsoft Excel by going to 'Start' > 'Programs' > 'Microsoft Office' > 'Microsoft Excel.'2. Input your data into the spreadsheet by simply typing in each value, or open your existing spreadsheet where you want to make a frequency table.3. Type in your bins, which help you to organize the data in your Frequency table. You should have at least 5 to 20 bins in order to get a good Frequency table, and the bins must match the range of your sample values. (For more information on bins, please see the link in the Resources section.)4. Click the 'Office Orb,' located at the top...
How to Save Word Docs to PDF in VBA
Enable PDF Support in Word1. Open Microsoft Word 2007. Click the blue help icon in the top, right corner of the program. Type 'pdf and xps file formats' in the Word Help search box and press 'Enter.' Click the 'Print, share and protect files in the PDF and XPS file formats' search result.2. Click 'Install and use the Save as PDF or XPS add-in from Microsoft' in the 'What do you want to do' section. Click the blue-formatted Internet link that reads 'Microsoft Save as PDF or XPS Add-in for 2007 Microsoft Office programs' to be taken to the add-in download page. Close the Microsoft Word program.3....
MS Excel Instructions
Putting In Titles1. Start up Excel. A blank spreadsheet page will appear. Click on the top left cell (rectangle) and type 'Quarterly Expenses.' This cell is cell A1. To the right is cell B1 and below it is cell A2. Note that when you click on a cell, the column letter and row number are highlighted and the cell number shows in the area above column A.2. Click on cell B2 to create a column title. Type 'Jan.' Click on cell B3 and type 'Feb.' Click on cell B4 and type 'Mar.' Click on cell B5 and type 'Total.' Click on cell B6 and type 'Monthly Average.'3. Click on cell A3 to create a row title....
How to Lock Objects in an Excel Chart
1. Open Excel and the worksheet that contains the chart you want to lock.2. Select any cells you don't want locked. In this case, highlight all of the cells because you only want to lock elements in your chart. Click-and-drag your mouse to highlight, or hold down 'Ctrl' on your keyboard while you click on each cell you don't want locked.3. Click 'Format' in the 'Cells' group on the 'Home' tab and choose 'Format Cells.'4. Click the 'Protection' tab, click the box next to 'Locked' to clear the check mark and click 'OK.'5. Hold down 'Ctrl' on your keyboard and click each graphic object,...
Saturday, February 12, 2011
How to Succeed in Business Using Microsoft Excel 2007
1. Solve problems with statistical analysis tools. Businesses can identify trends, make forecasts and find out what is required to reach goals by using some of Excel's statistical functions or tools. The Goal Seek tool assists businesses in finding out what is needed to achieve a numerical goal. The regression analysis and histogram are valuable when looking for trends or producing forecasts.2. Apply logic in decision making. Microsoft Excel makes use of Boolean logical values, such as 'True' and 'False,' to allow businesses to compare data under multiple criteria. These tools allow businesses...
How to Graph a Function in Excel 2007
1. Start Microsoft Excel and type the following:In cell 'A1' type 'X.' In cell 'A2' type '1.'Type '2' in cell 'A3.'2. Select cells 'A2' and 'A3' by clicking and dragging the bottom right corner then drag down column 'A' until you have the values 1 through 10.In cell 'B1' type 'Y.' In cell 'B2' type '=A2^2.' Press 'Enter.'3. Select 'B2' and click the lower right corner and drag down column 'B' until you have the values 1 through 100.4. Highlight 'A1' to 'B11,' click the 'Insert' menu, select 'Charts' and click 'Scatter.' Click 'Scatter with smooth lines' cha...
How to Plot Data Points in Excel 2007
1. Type your data points into two columns. One column will be the x-values and the other column will be the corresponding y-values.2. Click on a corner of the data plot cells and drag your mouse, so that all the data plot cells are highlighted.3. Click on the 'Chart Wizard' icon in the toolbar and then select 'XY(Scatter).' Click on the type of graph you want and then you will see your data points graphed.4. Click 'Next' to label your graph and the x and y values.5. Click 'Finish' when you are done labeling. Your graph will appear in the spreadshe...
How to Transfer 2010 Excel Spreadsheet to Access 2010 Using VBA
1. Start Access. Click 'Blank Database' and click the 'Create' button to create a new database. Click the 'Database Tools' tab and click 'Visual Basic' to open the Microsoft Visual Basic Window. Click the 'Insert' menu and click 'Module' to insert a new code module.2. Copy and paste the following code to create a new sub procedure:Private Sub importExcelSpreadsheet()3. Press 'Enter' on your keyboard to create the 'End Sub' for the procedure.4. Copy and paste the following code inside the 'Private Sub importExcelSpreadsheet()' procedure to import an Excel spreadsheet to Access:DoCmd.TransferSpreadsheet...
Friday, February 11, 2011
How to Set Up Excel Templates
1. Open Excel and click the 'Office Button' at the top left.2. Click 'New' in the menu that appears.3. Click the 'Invoices' category on the left side of the window that appears.4. Click the template you want to use, then click 'Download' to open ...
Subscribe to:
Posts (Atom)