February 2011 - Excel

Browse » Home » Archives for February 2011

Monday, February 28, 2011

How to Use Excel's Concatenate Function

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') in a cell. This formula will return a result of 'Stream population for salmon species is 25/mile'.
4. Study the use of cell ranges for Concatenate. Enter =Concatenate(A2:A3) in cell B2. Notice that Concatenate only displays salmon in cell B2 because it only uses text strings that are in the results row. You would need to use an appropriate Concatenate formula on each row in order to use fields on those rows.
5. Enter a Concatenate formula automatically. Position the cursor in the cell you wish to use, select the Insert menu on the menu bar and select 'Function' from the 'Insert Sub-menu.' Select the 'Text' category and then the 'Concatenate' function. Supply the arguments as prompted and click the 'OK' button to display the results

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 'Print' dialog box.
6. View the formatting specifics of your spreadsheet by clicking the 'Setup,' 'Margins' and 'Page Break Preview' buttons. The 'Setup' button will open the 'Page Setup' dialog box, the 'Margins' button will show the current margins of the spreadsheet and the 'Page Break Preview' will show the location of each page break.
7. Use the 'Help' button if you need more help and then click the 'Close' button to exit 'Print Preview' and return you to your spreadsheet.

How to Modify Pivot Table Data

Change Data
1. 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 Data
5. Locate and navigate to your source data.
6. Add the necessary information to your source data. Just like the original data, all information must have column headers in the top row and cannot contain subtotal cells.
7. Navigate back to your pivot table and select any cell in the table. Click the 'Options' tab at the top of the screen and then click the 'Change Data Source' button in the middle of the toolbar. A small window will appear.
8. Navigate back to your source data, the 'Move PivotTable' window will remain in place, even if you have to change Excel worksheets.
9. Click and hold on the top-left cell in your source data, then drag your mouse to the bottom-right cell and release the mouse button. Click 'OK' on the 'Move PivotTable' window to complete the process. Your pivot table will change any data that you updated, and you will see any new fields appear in the field list.

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 with red text but you can quickly change it to yellow, green or you can create a custom look by choosing 'Custom Format.' Click 'OK' when you are done and all the duplicate cells will be highlighted.

How to Customize Columns and Rows in Excel

Customize the Columns
1. 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 to insert a design.
4. Click the 'Type' or 'Border Type' and choose the line, color and style that define the outline of the column area.
5. Save your workbook.
Customize the Rows
6. Select rows by repeating the process in Step 1, or click the numbers to the left of the cell.
7. Choose the 'Row' option under the 'Format' menu, then click the 'Height' title to enter a number for this dimension.
8. Repeat Steps 3 and 4 from Section 1 to format the background color and border of the row(s).
9. Save your Excel file.

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 2007.

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 open.

Saturday, February 26, 2011

How to Insert Page Numbers in Excel 2007

Adding Page Numbers
1. 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' appear, press the space bar once and then type the word 'of' followed by a space. This will allow you to add the total number of pages to the document. Next, click 'Number of Pages' from the 'Header and Footer Elements' group.
Completing the Process
6. Once the placeholder '[Page] of [Pages]' appears, click anywhere outside of the header or footer to display the actual page numbers in your 'Page Layout View.'
7. You can choose to start with a different number by clicking 'Page Setup,' and then 'Page Layout.'
8. From there, you can click the dialog launcher beside 'Page Setup.'
9. On the 'Page' tab, you would simply change the 'First Page Number' option to whatever you prefer.

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 you.

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 amount of annual interest will be displayed. In this example, after entering the formula, Excel would display 35.56695, meaning you would earn $35.57 in interest for the year.

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 box, and 'TRUE' indicates a checked box.
3. Type '=IF(A1=TRUE,'checked','unchecked')' in the cell where you want to enter your formula.Replace 'A1' with the cell linked to the check box. This cell now responds as you wish to your check box.
4. Replace 'checked' (including quotation marks) with the formula you want to use if the check box is ticked. Replace 'unchecked' with the formula you want to use if the check box is not ticked. You now have a formula that will respond to the check box.

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 easily be added to the drop-down box menu.
3. Go back to the worksheet where you want the drop-down box. Click in the cell where it should be inserted.
4. Click on the 'Data' tab on the top toolbar. Click on the drop-down arrow next to 'Data Validation' in the 'Data Tools' section. Select 'Data Validation' and a box will open up.
5. Select the 'Settings' tab in the 'Data Validation' box. This is where you will enter the information to set up the drop-down box and menu.
6. Choose 'List' from the menu under the 'Allow' section. Additional fields will be displayed in the box. Make sure that the 'Ignore' blank and 'In-cell' drop-down boxes are checked.
7. Enter a source for the drop-down box. In the 'Source' box, enter the equal symbol (=) and the name of the list that you created. The name should be entered just as you entered it when you named the list. For example: enter =Animals.
8. Click 'OK.' You will see an arrow out to the side of the cell where you added the drop-down box. Click on the arrow to access items from the list. To add a specific item to the cell, click on it and the cell will be populated with that item.

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 print.

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 that appears every time you open Excel, asking if you want to update your links.

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.
Click 'Remove Arrows' to delete the arrows the auditing function added to your worksheet.

How to Check for Duplicate Text in Excel

Excel 2007
1. 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 2003
5. 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 range or use your own. To change the list range, click on the “List Range” button and select your range on the spreadsheet.
8. Select “Copy to another location” under “Action” and place a check mark by “Unique records only.” Click on the “Copy to” button and choose the location where the updated data is placed. Then click on 'OK' to apply the update.
9. Double-check that the updated list looks correct, then select the old column and delete it.

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 format.

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 Excel, then click the feature that you would like to install. Select the 'Run from my computer' option. Repeat for any additional features you would like to install, then click the 'Update' button.

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 the automatic install option you will be fine.
4. Click 'OK' to save the option.

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 if you don’t see the fill handle appear. Click the “Options” button and then click on the “Edit” tab.
5. Check the box next to the “Allow cell drag and drop” field. Every time you start to perform a function in a new row, or if you add a row, then a sequential number will automatically be updated.

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 being used.

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 the two data arrays.

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' is the name of the cell containing the amount to which you are comparing it.
6. Press 'Enter' to perform the calculation.
7. Right-click the cell into which you just typed your formula, and then click 'Format Cells.'
8. Click the 'Number' tab at the top of the window.
9. Click 'Percentage' in the column at the left side of the window, and then click 'OK.'

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 Computer.' If you wish to upgrade other Office applications during this installation, select those as well.
5. Click 'Install Now.' Click 'Close' when the upgrade completes.

Tuesday, February 22, 2011

How to Find the Regression Equation in Excel 2007

From Graphing
1. 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 Excel Functions
5. Find the slope of the regression equation, 'm,' by typing in any empty cell '=slope(known_ys, known_xs).' For example, if the known y-values are the cell range B1:B5 and the known x-values are in the cell range A1:A5, then type '=slope(B1:B5,A1:A5).' With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the result is a slope of two and four tenths.
6. Find the y-intercept of the regression equation, 'b,' by typing in any empty cell '=intercept(known_ys, known_xs).' For example, if the known y-values are the range B1:B5 and the known x-values are in the range A1:A5, then type '=intercept(B1:B5,A1:A5).' With the same examples values as before, the result is a y-intercept of seven and two tenths.
7. Write out the regression equation now as 'y = m*x b.' In our example, the regression line equation is 'y = 2.4*x 7.2.'
Brute Force Calculations
8. Calculate the sum of all the x-values, denoted (x). Do this by typing in an empty cell '=sum(A1:A5),' where A1:A5 is the range of x-values. Perform the same operation to find the sum of all the y-values, denoted (y), which may for example be in the range B1:B5.
9. Calculate the sum of the product of each x and y pair, denoted (xy). Do this by summing together A1*B1, A2*B2, etc.. in the same fashion as step one. Also, denote the number of x-y pairs as 'n.'
10. Calculate the sum of the square of each x-value, denoted (x^2). Do this by summing together A1^2, A2^2, etc.. in the same fashion as step one.
11. Calculate the slope, 'm,' of the regression equation by performing the following calculation: n(xy) - (x)*(y). Then calculate n(x^2) - (x)^2. Lastly, divide the first result by the second result. With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the result is a slope of two and four tenths.
12. Calculate the y-intercept, 'b,' of the regression equation by performing the following calculation: (y)-m*(x). Lastly, divide the result by 'n.' With the same example data as the previous step, the result is a y-intercept of seven and two tenths.

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 spreadsheet.

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 copy.

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, fractions and text.
6. Select 'Text' from the options and click 'OK.' The numbers you selected on your spreadsheet will now be formatted as text.

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' delete rows 1, 3, 5, and so on.I = 1Set xRng = Selection' Loop once for every row in the selection.For xCounter = 1 To xRng.Rows.Count' If Y is True, then...If Y = True Then' ...delete an entire row of cells.xRng.Cells(I).EntireRow.Delete' Otherwise...Else' ...increment I by one so we can cycle through range.I = I 1End If' If Y is True, make it False; if Y is False, make it True.Y = Not YNext xCounterEnd Sub
5. Click 'Close and Return to Microsoft Excel' to exit, then test the macro by highlighting rows A1 through A9 on the Excel spreadsheet you made earlier. Click 'Tools,' then 'Macro' and then click 'Macros.'
6. Select 'Delete_Every_Other_Row' from the Macro menu and click 'Run.' The alternate rows will be deleted and numbers 1 through 5 will now be displayed in cells A1 through A5.

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 sheet.

How to Insert Information in an Excel Spreadsheet

Direct Data Entry
1. 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 Data
4. Launch Microsoft Excel 2010. Open the spreadsheet that contains the cells where data from other spreadsheets will be displayed.
5. Open the Excel spreadsheet that contains data that will be used in a new spreadsheet.
6. Switch back to the spreadsheet that will display the data and click on the cell that will display the data. Press the 'Equal' sign ('=') on the keyboard. Switch to the spreadsheet that contains the data and click once on the cell that contains the data and press 'Enter.' Notice that the display spreadsheet now displays the data from the other spreadsheet.
Import Data
7. Launch Microsoft Excel and open the spreadsheet that will contain the imported data.
8. Click on the cell where the imported data will be displayed.
9. Click the 'Data' tab on the toolbar and click the appropriate button in the 'Get External Data' group to launch the 'Open File' or 'Data Connection' dialog.
10. Locate the source data file or location and click 'OK' to connect to the new data source. Specify additional options depending upon the type of connection made and the amount of data being used.

How to Use Forms for Data Update in Excel

Use Forms in Excel 2003
1. 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 and save the changes to the worksheet.
Use Forms in Excel 2007
6. Add the 'Form' button to the 'Quick Access Toolbar' the first time you want to use a form, as it is not included in the Excel 2007 ribbon by default. Click the arrow at the right end of the 'Quick Access Toolbar' and select 'More Commands.' Select 'All Commands' under 'Choose Commands From.' Click on 'Form Button' in the list and click 'Add.'
7. Click on a cell within the data table that you want to update. Click the 'Form' button on the 'Quick Access Toolbar.' A data form opens.
8. 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.
9. Press the 'Tab' key to move through the fields in the form. Update or edit any fields that you want to change.
10. Press 'Enter' to apply the changes and move to the next record. Close the form window when you are done updating the data and save the changes to the worksheet.

How to Perform Linear Regression in an Excel Spreadsheet

Excel 2003
1. 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 chart.
Excel 2007 or Excel 2010
7. Highlight your chart data by clicking at the top left of the data and then dragging the mouse to the bottom right.
8. Click on 'Insert,' then click on 'Scatter.' Click on the first scatter graph icon on the left.
9. Click on 'More Trendline Options.'
10. Click the 'Linear' radio button, then click the 'Display Equation on Chart' check box.
11. Click on 'Close.' Excel will insert the regression line into your chart.

Friday, February 18, 2011

How to Create Free Dashboards

Excel Dashboards
1. 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 and how you need to arrange it; You will find a link to a collection of Excel formulas in the Resources section.
4. Use Excel tools such as formatting, form control, objects and graphs to create the dashboard. You can find all these tools in Excel by selecting Menu > View > Tool Bars. It is possible to create a dashboard using only the tools available in Excel, but if you have an understanding of other programming software or languages, those may be helpful for creating more visually appealing or interactive dashboards.
5. Update your data to update the dashboard. You can choose how frequently you need to do this.
Dashboard Software
6. Upload data into Excel spreadsheet or other database. Sometimes you can simple copy and paste, but it is easier to connect Excel to the database, or enter it manually. Some software may only be compatible with certain data storage software, so check to see what you need.
7. Install the dashboard software of your choice.
8. Import the data from the database into the software. You will have to choose the specific file name and file path. Again, be sure that your document title does not contain any spaces or unusual characters.
9. Open the data file in the dashboard software. From here, you can use the various tools in the software to create the dashboard you need.

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' in the toolbar. Select 'Detect and Repair' and click 'Start.' After 'Detect and Repair' is complete, click 'OK.' Close Excel 2003.

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 and merge cells. The 'Font' tab changes your font type, size and color. The 'Border' tab changes the border surrounding your cells. The 'Fill' tab adds color to the background of the cells. The 'Protection' tab locks cells.
5. Click 'OK.'

How to Create a Survey in Excel

Enable Developer Tab
1. 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 Box
4. 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 box to appear in your spreadsheet.
8. Highlight the words 'Check Box' and press 'Delete.' Type a property for your check box and press 'Enter.' This property might include 'Yes' or 'No,' 'Rent' or 'Own' or 'Full-Time' or 'Part-Time,' depending on the type of survey you are creating.
9. Right-click the check box and click 'Format Control.' Edit the check box's color, size, format and value. Click 'OK' to close the Format Control dialog box.
Insert Option Button
10. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
11. Click 'Insert' and a drop-down list appears on your screen. Click 'Option Button' under the 'ActiveX Controls' heading.
12. Click the cell where you want the upper-left corner of the option button to appear in your spreadsheet.
13. Highlight the words 'Option Button' and press 'Delete.' Type a property for your option button and press 'Enter.' This property might include 'Yes' or 'No,' 'Rent' or 'Own' or 'Full-Time' or 'Part-Time,' depending on the type of survey you are creating.
14. Right-click the option button and click 'Format Control.' Edit the option button's color, size, format and value. Click 'OK' to close the Format Control dialog box.
Insert List Box
15. Create a list of entries for your drop-down list, which must not contain blank cells and must be in a single column or row. This list can include data on the same or another worksheet within your workbook.
16. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
17. Click 'Insert' and a drop-down list appears on your screen. Click 'List Box' under the 'Form Controls' heading.
18. Click the cell where you want the upper-left corner of the list box to appear in your spreadsheet.
19. Right-click the option button and click 'Format Control.' Click the 'Control' tab.
20. Click the 'Input Range' button select the list of entries for your list box. Press 'Enter.'
21. Edit the list box's size, format and value. Click 'OK' to close the Format Control dialog box.
Insert Text Box
22. Click the 'Insert' tab and click 'Text Box' from the 'Text' group.
23. Click the cell where you want the text box to appear in your worksheet.
24. Hover your mouse over a corner of the text box and your mouse changes to an arrow. This indicates that you can change the size of the box.
25. Click your mouse on the corner of the text box and drag it diagonally to the desired size.
26. Click inside the text box and type the information you want the text box to include. Highlight the text in the text box and click the 'Home' tab. Use the settings in the 'Font' group to format the information in the text box.

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 future.

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, January).

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 you wish removed from Excel.

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' and save your password-protected Excel file.

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 select 'Open.' This action will open the 'Open' pop up window. Use the 'Look in' menu to locate the folder in which the Excel file is saved. Highlight the file and click the 'Open' button.
3. Click the 'File' menu and select the 'Print' option. This action will open the 'Print' dialogue window. Use the 'Name' drop down menu to select the PDF writing program you chose in Step 1; for example 'CutePDF.' Click the 'Print' button. This action will open the 'Save As' dialogue window.
4. Click the 'Save in' menu to locate a folder in which to save the PDF file. Enter a name for the PDF file into the 'File name' field and click the 'Save' button to complete the process of converting an Excel 2003 file to PDF format.

How to Expand Collapse in Excel 2007

Hide Rows and Columns
1. 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 B is hidden.
Unhide Rows and Columns
7. Open the saved document.
8. Click on the two headers adjacent to the hidden rows or columns. For example, if rows 10 to 15 are hidden, click on row headers nine and 16.
9. Click on the 'Home' tab.
10. Click the 'Format' button in the 'Cells' group. A drop-down list will appear.
11. Point to the 'Hide Unhide' section. A list of commands will appear.
12. Click either 'Unhide Rows' or 'Unhide Columns'. The hidden rows or columns will appear. The spreadsheet will expand.

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 it to a new location.
3. Download software that recovers your data for you. There are a variety of options to choose from, including Active Undelete, WinUndelete and File Scavenger (see Resources for links). Although these all offer free trial versions, you may have to purchase the full version to successfully recover your data.

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 find that term in the table array specified, and then display the information from the cell specified as 'col_index_num.' If you had a table of names in column A, hair colors in column B, and dates of birth in column C, then this VLOOKUP function would display for you the date of birth in cell B2 (as it is the third column of the specified table array).

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 tabs are highlighted that you want to include in the group. The selected sheet tabs will appear white.
5. Release the CTRL key when you are finished selecting the worksheets that are to be included in the group. The worksheets are automatically grouped when you select them. Notice that '(group)' is beside the name of the file that appears in the workbook title bar.

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 not need to be defined, just labeled and designated as changing cells.
4. Establish the constraints for the Excel solver model. These are the specific constraints placed on cells within the Solver model. These constraints can apply to the variable cells or the objective cells. For instance, in budget model it would be the overall budget limit or specific limits that are placed on each department.
5. Ascertain the objective of the solver model. The objective is what the model is intended to 'Solve.' Again using the budget example, it would be the overall budget limit. Essentially, it is the answer to the original question posed when using Solver.
6. Input the information into the Excel Solver model. Select 'Data' from the main menu and then choose the 'Solver' add-in. The Solver dialogue box should appear. First, input the information into the target cell or objective cell. Then set limits of that cell; in the budget case it would be the maximum amount of money to be spent. Next, select the cell ranges that are the changing cells. Finally, enter any constraints that will limit the changing cells.
7. Solve the model with Microsoft Excel Solver. After all the data is correctly entered click the 'Solve' button on the Solver dialogue box.

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 file.
3. Highlight the entire document with the mouse once the OCR process is complete. Click 'Edit' from the top menu and select 'Copy' from the subsequent menu.
4. Open Microsoft Excel and open the workbook that you want the data added into. Click 'Edit' from the top menu and select 'Paste' from the available options. The document will now appear in the Excel workbook.

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 box.

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 'Share Workbook' in the 'Changes' group.
9. Click the 'Editing' tab and check 'Allow changes by more than one user' to re-enable the Excel document sharing feature.

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 clicked to begin the formula to display what the total would be if you subtracted 20 from the value displayed in cell A2, you would first click cell A2. The cell will now have a moving dotted line going around it. After clicking the cell, type the minus sign (-) then type the value that you want to subtract (20).
3. Press the 'Enter' key when your formula is complete. The cell that you clicked to begin the formula will automatically change to display the result of the formula that you typed. If you referenced another cell when typing the formula (such as in the example), the result of your formula will change automatically if the value in the referenced cell ever changes.
4. Click the original cell again if you need to make changes to your formula. Note that the formula bar at the top of the screen shows the full formula that you entered; after clicking the cell, click in the formula bar, back over any mistakes that you have made using the 'Backspace' key, and enter the desired values. Press 'Enter' to recalculate the formula when you are done. Alternatively, you can click the cell that you want to change, press the 'Delete' key, and start the formula over.

How to Use Subtotals in Excel

How to Use Subtotals in Excel
1. 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 amounts.
4. In the 'Sort' pop-up window select the appropriate column heading in the 'Sort by' field. Select 'Ascending' or 'Descending' radio buttons, depending on whether you want the data sorted ascending or descending. Select 'Ok'.
5. Left-click with your mouse in the upper left hand corner of the data that you want subtotaled and then, using your mouse, highlight all of the data by moving your mouse to the right and down.
6. In the 'Subtotal' pop-up window, 'At each change in:' field select the category by which you want data subtotaled.In the example given above, this would be: 'State'.
7. In the 'Subtotal' pop-up window, select the function that you want performed on the data. The options are: Sum, Count, Average, Max, Min, Product, Count Nums (numbers), StdDev (standard deviation), StdDevp (standard deviation population), Var (variance), Varp (variance population).In the example given above, this would be: 'Sum'.
8. In the 'Subtotal' pop-up window, select the data that you want subtotaled (or the other functions available within this tool). The options will be all of the column headers in the data that was selected in step 5.In the example give above, this would be: 'Purchases'.
9. In the 'Subtotal' pop-up window, indicate if you want the current subtotals replaced (useful to keep data clean if you are re-subtotaling information), page break between groups (useful for disseminating specific data to specific individuals or departments), summary below data (useful to keep an overall number, whether it is sum, average, count, etc...).Select 'Ok.'
10. Notice that additional rows have been inserted into the spreadsheet for each change in category.
To the right of the data you will notice a small '1', '2' and '3'. The view automatically defaults to '3,' which lists all of the data that was subtotaled and the inserted rows mentioned above. View '2' lists all of the subtotals. View '1' lists only the grand total.

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, click on the image and use the 'Resize' buttons along its border to scale it to a smaller size.
5. Right-click on your image and select 'Size and Properties.' Click the 'Properties' tab in the dialog window and choose 'Move and size with cells' from the 'Object Positioning' options. Click 'Close' to apply the changes.
6. Resize the cell containing the picture. The image will scale with the cell as you adjust the row height and column length.

How to Show Duplicate Records in Excel

Formatting
1. 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 Formatting box.
4. Click the 'Patterns' tab and choose a color, such as pink, to highlight duplicate entries by clicking on any of the available the color swatches.
5. Click 'Ok' on the 'Format' and then the 'Conditionally Formatting boxes' to close both. Your cell will become highlighted because it contains the data you are highlighting.
Application
6. Click with the left mouse button to open the Edit menu and select 'Copy' with your cursor still in the same cell.
7. Highlight the entire column by pressing 'CTRL' 'Spacebar.'
8. Open the Edit menu. Select 'Paste Special.'
9. Select the radio button (circle) next to 'Formats' on the 'Paste Special' box and click 'Ok.' Duplicate cells will become highlighted.

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 'Insert Sheet' option.
3. Give each sheet a descriptive name. In Excel 2003, click on the 'Format' menu and choose 'Sheet' from the drop-down box. Choose the 'Rename' option and type a descriptive name. Repeat this process for each of the sheets. In Excel 2007, right-click on the worksheet tab at the bottom of the screen and select 'Rename' and type in the new name.
4. Enter your data into each of the sheets, then move to the sheet that will house your consolidated data.
5. Place your cursor in the field where you want the consolidated data to appear. Type the equal sign in that field, then click your cursor on the sheet containing the first number you want to add. Highlight the cell containing that information then type the plus sign. Move to the next cell you want to add to your consolidated spreadsheet, highlight it and press the plus sign. Continue this process until all cells have been added, then hit the 'Enter' key.

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' button. Make sure you are in the 'Adobe PDF Settings' tab and choose ARCH D from 'Adobe PDF Page Size' drop down menu and press 'OK.'
7. Select 'OK' again in the main Print menu. This will save the document to your computer as a PDF.
8. Print the PDF to a hard copy with your printer if it is capable of using 24'x36' paper; otherwise, upload your document to the website of a commercial printing store or company.

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 workbook.

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 'OK.'

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 then press the 'Pivot Table' button on the left side of the ribbon across the top of the window. This will launch the 'Create Pivot Table' wizard. Press the 'OK' button to create the Pivot Table on a new worksheet. If you want to use the same worksheet, select the 'Existing Worksheet' option and enter in the top-left cell where the Pivot Table will go.
5. Place checkmarks next to the fields, which correspond to the columns in your source data, that you want to include in the Pivot Table. As you place the checkmarks, you will see the fields appear in one of four boxes on the screen. And you will see the data in those fields populate the Pivot Table.
6. Drag and drop the fields to move them from one Pivot Table area to another. Each area corresponds with a different section of the Pivot Table. You can have each field be a column on the Pivot Table, a row on the Pivot Table, a filter above the Pivot Table or part of the data set that makes up the bulk of the Pivot Table.

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 tabs at the bottom of the Excel window to switch to a different worksheet. Each Excel 2003 workbook can have many worksheets with unique sets of data. You can rename worksheets by double-clicking on the worksheet name and then typing a new name. You can add more worksheets by clicking on the 'Insert' menu and then selecting 'Worksheet.'
5. Print spreadsheet data. Click on the tab of the worksheet you want to print, then click 'File,' then 'Print,' select the appropriate option for the print job you want to perform under 'Print what,' (print the active worksheet, selection, the whole workbook, etc.) and click 'OK.'
6. Save your spreadsheet. To save your workbook in Excel at any time, click 'File' and then 'Save.' You can also hold down the 'control' key and then press 'S' to save the file.

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 left of the screen, and select 'Excel Options.' Click 'Add-Ins.'
5. Click the 'Go' button located next to 'Manage: Excel Add-ins.' Click the box located next to 'Analysis Toolpak' to select it, and then click 'OK.' Click 'Yes' when prompted to by Excel, and the Analysis pack will begin installing.
6. Click the 'Data' tab in Excel and select 'Data Analysis,' located on the right-hand side of the screen. Select 'Histogram' and click 'OK.'
7. Click the button located next to 'Input Range,' and select all the numbers whose frequency you want to find. Click the button located next to 'Bin Range,' and select the bin numbers you specified in Step 3.
8. Select 'New Worksheet Ply' for the output and then click 'OK'. Your Frequency table will appear on the new worksheet.
9. Click the New Worksheet Ply to make sure the Frequency table has been successfully created. You've now made a Frequency table in Microsoft Excel.

How to Save Word Docs to PDF in VBA

Enable PDF Support in Word
1. 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. Click the 'Download' button on the add-in page. Save the file to your computer. Double-click the file to open it. Click the check-box to accept the Microsoft Software License Terms. Click 'Continue' and wait for the installation to complete.
Create and Run VBA Code
4. Open a Microsoft Word document that you want to convert to PDF. Click the Office button and select 'Word Options.' Click the 'Show Developer tab in the Ribbon' check box to enable it. Click 'OK.'
5. Click the 'Developer' tab in the Ribbon. Click 'Macros' in the 'Code' group. Type 'SaveToPDF' or another name of your choice in the 'Macro name' box. Click the 'Create' button.
6. Type the following VBA code lines, or copy and paste them, in the 'New Macros (Code)' window in the line directly above the 'End Sub' line:ActiveDocument.ExportAsFixedFormat OutputFileName:= _ActiveDocument.Path '\' ActiveDocument.Name '.pdf', ExportFormat:= _wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _wdExportOptimizeForPrint, Range:=wdExportAllDocument, _Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _BitmapMissingFonts:=True, UseISO19005_1:=False
7. Click the 'File' menu and click 'Close and Return to Microsoft Word.'
8. Click 'Macros' in the 'Code' group in the 'Developer' tab. Click the name of your macro to highlight it if it is not already selected. Click 'Run' to run the VBA code and automatically save the Word document as a PDF file. Navigate to the folder location on your computer in which your Word document is saved to find the PDF version of the file.
9. Open any other Word documents that you want to save as a PDF. Click the 'Developer' tab, click 'Macros,' select the name of the macro you created and click 'Run.'

MS Excel Instructions

Putting In Titles
1. 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. Type 'Medical.' Click on cell A4 and type 'Utilities.' Click on cell A5 and type 'Other.' Click on cell A6 and type 'Total.'
Adding Data
4. Click on cell B3 and type in the amount of January medical expenses. You do not have to use a dollar sign, just type in an amount like 123.56. Click on cells C3 and D3 and add medical expenses for February and March.
5. Click on cells B4, C4 and D4 and enter utility expenses for January, February and March.
6. Click on cells B5, C5 and D5 and enter Other expenses for January, February and March.
Adding Formulas
7. Click on cell B6 and type the following: =sum(B3:B5) and hit 'Enter.' The total for the January expenses should now show in the cell. If you click on the cell, the formula will show in the white area above the spreadsheet.
8. Click cell B6 and drag your mouse across to cell D6 and type CTRL-R (copy right). This will copy the formula to the other two cells, adjusting the cell references automatically. The totals for February and March should appear.
9. Click cell E3 and type =sum(B3:D3) and hit 'Enter.' The quarterly sum for medical expenses should appear.
10. Click cell E3 and drag the mouse down to cell E6 and type CTRL-D (copy down). The formula will be copied down to the other cells including cell D6 which now shows total quarterly expenses.
11. Click on cell F3 and type =average(B3:D3) and hit 'Enter.' This cell will now show the average monthly medical expenses for the quarter. Click on cell F3 and drag the mouse down to cell F6 and type CTRL-D to copy the average formula down to the other rows.
Formatting Cells
12. Click on cell A1 and drag the mouse to cell F6. This will highlight the whole spreadsheet. From the Format menu select 'Cells...' On the Number tab select 'Currency.' In the Symbol: box select '$.' Click 'OK.' All your numeric cells will now show with a dollar sign and two decimal places.
13. Click the 'A' at the top of Column A to highlight the entire column. From the Format menu select 'Cells...' Select the Font tab. Select 'Bold' under font style and click 'OK.'
14. Click on the '2' at the left of row 2 to highlight the entire row. From the Format menu select 'Cells...' Select the Font tab. Select 'Bold' under font style and click 'OK.' Now your column and row titles are in bold. You can select cell A1 and change the font size and style for the title if you wish.

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, including any parts of your chart, that you don't want locked.
6. Click the 'Format' tab and click the icon for the Dialog Box Launcher, which is located in the 'Size' group next to 'Size.'
7. Select the 'Properties' tab and click the check box next to 'Locked' to clear the check mark, then click 'OK.'
8. Click the 'Review' tab and click 'Protect Sheet' from the 'Changes' group.
9. Click the boxes next to 'Edit objects' under 'Worksheet Elements,' and 'Contents' and 'Objects' under 'Chart Sheet Elements' in the 'Allow all users of this worksheet to.' This prevents anyone from making changes to charts that you did not unlock.
10. Type a password in the box next to 'Password to unprotect sheet' and click 'OK.' Retype the password to confirm your entry.

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 to analyze complex data by converting it into simple terms for decision making. For instance, the business can create a set the criteria, and then Excel will quickly determine if the data meets all of the criteria, some of the criteria or none of the criteria.
3. Retrieve data for computation, analysis and reference. Most programs used by businesses allows exportation of data in a file format that is recognized by Excel, such as CSV. The data can then be viewed, manipulated and analyzed within the software. A helpful tool in Excel for arranging and sorting data is the Pivot Table, in which columns and rows can be arranged by clicking and dragging from a menu. In addition, the Reference and Lookup functions can expand a business's ability to look at values based on specific criteria or find values that produce a pre-determined result.
4. Use data tables and scenarios to calculate a 'what-if' analysis. A 'what-if' analysis allows businesses to change specific variable to see the possible outcomes. The Scenario Manager within Excel was developed to allow businesses not only to create these scenarios but also conveniently present multiple scenarios on one spreadsheet.
5. Make decisions by using Excel's Solver. Businesses can set up complex business models, then find the optimal sets of decision inputs to meet their objective, such as minimizing costs or maximizing profits. This tool is much more powerful than Goal Seek, as Goal Seek allows you to change only one cell or decision input.

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' chart.

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 spreadsheet.

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 acImport, acSpreadsheetTypeExcel9, _'Employees', 'C:\Employees.xlsx', True
5. Edit 'Employees ' and type the name of the table for the targeted spreadsheet. Edit 'C:\Employees.xlsx' and type the path and the name of your Excel spreadsheet to be imported.
6. Press 'F5' to run your procedure and import the Excel spreadsheet into Access.

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 it.

Subscribe to: Posts (Atom)