2013 - Excel

Browse » Home » Archives for 2013

Saturday, December 28, 2013

How to Set Excel 2003 as Your Default if You Have 2007 Installed

1. Close any open Excel windows.
2. Click 'Start' and then click 'Run' from Windows XP; click the 'Microsoft' button and then click 'Run' from Vista; or click the 'Microsoft' button, then type 'Run,' then click 'Run' from Windows 7.
3. Type 'excel.exe /unregserver' into the text box and then click 'OK.'
4. Type 'excel.exe /regserver' into the textbox and then click 'OK.'

How to Calculate the P

Calculate Correlation
1. Open the workbook that contains your data in Excel 2007.
2. Determine the cell addresses that contain the two ranges of data that you want to compare. For example, if your data is contained in the first 20 rows of columns 'A' and 'B,' then the two cell address ranges for your data are 'A1:A20' and 'B1:B20.'
3. Click a cell that you want to use to calculate the Pearson correlation coefficient 'r.'
4. Type '=PEARSON(A1:A20,B1:B20)' -- without the quotes -- in the cell. Substitute the addresses of your data ranges in place of these sample ranges.
5. Press 'Enter.' Excel calculates the Pearson correlation coefficient for your data range and displays it in the cell.
Calculate P-Value
6. Click a different cell that you want to use to calculate the p-value that is associated with your correlation.
7. Copy and paste the following formula into the cell:=TDIST((pearson_cell*sqrt(N-2)/sqrt(1-(pearson_cell*pearson_cell))), N, 2)The cell returns an error message; don't worry, you'll fix the formula to work with your data array in the next steps.
8. Click the formula bar to edit the formula that you just pasted. Type the address of the cell that contains your Pearson correlation formula in place of all three instances of 'pearson_cell' in the above example. For instance, if you calculated the Pearson correlation in cell 'A22,' then the formula looks like this:=TDIST((A22*sqrt(N-2)/sqrt(1-(A22*A22))), N, 2)
9. Type the number of observations that you have for each variable in place of each instance of 'N' in the formula. For instance, if you have 20 observations in each column, then the formula now looks like this:=TDIST((A22*sqrt(20-2)/sqrt(1-(A22*A22))), 20, 2)
10. Press 'Enter.' The cell calculates and displays the p-value that is associated with your correlation.

How to Send a Microsoft Excel 2007 Worksheet As an Email

Sending an Excel File Via E-mail
1. Power on your computer and open your e-mail server or log in to your online e-mail.
2. Click the button 'New' to create a new message. Enter the recipient's e-mail address in the 'To' field. Enter a subject line and any other text, if desired.
3. Click on the 'Attach' button or in some cases, the button that looks like a paper clip. A window appears with a button marked 'Browse.'
4. Click on the 'Browse' button and navigate to the folder where your Excel file is located. Once selected, the e-mail client will store it as part of the e-mail. Some e-mail clients require that you click an 'Attach' button after selecting the file.
5. Click on the 'Send' button to deliver the e-mail. Your recipient will receive the e-mail, and will be able to open or save the Excel file to his or her hard drive.

How to Insert a Row in Microsoft Excel 2003

How to Add a Single Row
1. Open an Excel Worksheet or create a new one.
2.
Highlight the row header immediately below where the new row should appear.
3.
Select 'Insert' on the menu bar.
4. Click 'Rows' from the drop down list. The new row is now found above your highlighted selection.
How to Add Multiple Rows
5. Open an Excel Worksheet or create a new one.
6. Highlight the number of row headers immediately below where the new rows should appear.
7. Select 'Insert' on the Menu Bar and click 'Rows' from the drop down list. The new rows will be inserted above your highlighted selection.

Friday, December 27, 2013

How to Use Correlation on Excel 2007

1. Open Microsoft Excel 2007. Click on the 'File' option, then select 'Open.'
2. Locate the Excel file that contains the different variables for which you want to calculate the correlation coefficient, then click 'Open.'
3. Select the data you want to compare by highlighting the data with your mouse. Click on the 'Data' tab, then click the 'Data Analysis' option from the 'Analysis' group.
4. Click the 'Correlation' option, and the Correlation dialog box will appear. Click on the button in the 'Input Range' field to make sure the correct fields are selected.
5. Select the 'Columns' or 'Rows' option from the 'Grouped By' field and then select the 'Labels in first row' option. Choose the output range from the 'Output options' fields, then click 'OK.'

How to Use Microsoft Excel to Draw Control Charts and Graphs

Excel 2007
1. Open a new file by clicking on the “Office” button and choosing “New.”
2. Type “control chart” into the ‘Search Microsoft Office Online for Templates’ box.
3. Click on the control chart that appears, then click the “Download” button. The file will download and automatically open. At this stage, you can tailor the control sheet to fit your needs, perhaps by changing the titles in the columns or by placing different totals in the boxes.
4. Graph the control chart. Highlight the text by left clicking at the top left corner of the data and then dragging the cursor to the bottom right. Choose “Insert->Chart” from the toolbar and choose your chart type. For example, click on “2D column graph.” Excel will insert your chart automatically.
Excel 2003
5. Go to the Microsoft Office Templates website and then type in 'Control Sheet' into the search box.
6. Click the 'Download' button. The template will download and install.
7. Click on 'File->Open' from Excel and locate the file you just downloaded. Press 'OK.' The control sheet template will open up. You can adjust the template to suit your needs (for example, different numbers in the rows).
8. Click on 'Insert->Chart' and follow the directions in the Chart Wizard to create a chart (for example, choose a column chart to display your data).

How to Insert a Watermarked Picture in Excel 2007

1. Open your Excel document. Select the 'Insert' tab.
2. Click the 'Picture' icon on the ribbons bar. Search for the file or picture on your computer. A new window will appear; select the file. Select 'Insert' and the picture will appear in the document.
3. Drag and click the picture into the position you would like.
4. Double-click on the photo for the picture toolbar to appear. On the toolbar, click 'Format Picture.'
5. Select 'Watermark' under the 'Image Control/Color' tab. Lighten or darkened your watermark as necessary.

Thursday, December 26, 2013

How to Get Microsoft Excel Cells to Shade Gray When Highlighted

1. Open the Microsoft Excel program on your computer. It is usually found in the 'Start' menu under 'Microsoft Office' in most computers.
2. Open a new spreadsheet or select an existing spreadsheet to edit. To open a new spreadsheet, click 'File' and 'New' at the top left-hand side of the program. To open an existing spreadsheet, click 'File' and 'Open.' You can then browse your hard drive for an existing spreadsheet.
3. Select the cell or cells you wish to fill. Click on the cell once to highlight it. If you wish to choose a group of cells, hold down the 'Shift' button while selecting cells.
4. Select your fill color. Either click the paintbucket icon in the top toolbar or right-click within the selected cells and select 'Format Cells.'
5. Select the 'Patterns' or 'Fill' tab. Several shades of gray are available to choose from. Pick your preferable shade from the existing options or click 'More Colors' to customize a shade of gray. Click 'OK' to choose your color.

How to Use the Paste Special Command in Excel

Switching Rows of Cells to Columns or Columns to Rows
1. Select the cells that you want to switch.
2. Open the Edit menu and select Copy.
3. Select the upper left cell of the paste area. The paste area must be outside the copy area.
4. Open the Edit menu and select Paste Special.
5. Select the Transpose check box.
6. Click OK.
Pasting Only Values, Formulas, Comments, or Cell Formats
7. Select the cell or range of cells you want to copy.
8. Open the Edit menu and select Copy.
9. Click the cell you want to paste the information into, or click the upper left boundary of the cell range you want to paste the information into.
10. Open the Edit menu and select Paste Special.
11. Select Formula to copy only the formula of the cell.
12. Highlight Values to copy only the outcome of the formula you copied into the cell and not the formula itself.
13. Select Format to copy just the format of the cell (font, alignment, and so on).
14. Choose Comments to copy only annotations for the cell.
15. Click OK to accept the option you selected.

How to Copy an Array to a Range in Excel 2007

1. Open Excel and load the worksheet with the data sets you want to use to feed an array formula.
2. Select a cell and enter the array formula. For example, if you wanted to have the formula multiply the number in B1 by the number in C1, you'd enter the formula =B1*C1. Hit 'Ctrl'-'Shift'-'Enter' to make sure it's entered as an array formula.
3. Select the cell with the newly entered array formula. Hit 'Ctrl'-'C' to copy it.
4. Select the other cells in the range you want in the array formula. Hit 'Ctrl'-'V' to copy the array formula throughout the range.

How to Create Named Ranges in Excel 2007

1. Launch the Microsoft Excel program by double-clicking the program's shortcut on the desktop or by selecting 'Microsoft Excel' from the 'Start', 'Programs' or 'All Programs' menu.
2. Click the 'Office Button' in the upper left corner of the program window and select 'Open.' Click the down arrow to the right of the 'Look in' text box to browse to the folder that contains the file you want to work with. Select the file, and click 'Open.' If you are creating a new spreadsheet, Click the 'Office Button' and select 'New.' Select 'Workbook' and click 'OK.' A blank spreadsheet should be displayed. Enter your data.
3. Select the cells you are interested in using in the named range. Click the 'Formulas' menu on the menu bar. Click the 'Define Name' button on the ribbon to open the 'New Name' dialog box. Click in the 'Name' text box and type a name of your choice. Click 'OK' to define the name and return to your spreadsheet. The name of your range should appear in the 'Name Box' in the upper left corner of the program window.
4. Click somewhere outside the named range to deselect it. Look at the 'Name Box.' The name you created has been replaced by the location of the cell you are in.
5. Click the down arrow to the right of the 'Name Box' and select the name you created. The name reappears in the 'Name Box,' and the name range becomes selected.
6. Click the 'Name Manager' button on the ribbon to view all of the names in your Workbook. Select a name to view the cells it references. Notice that the range of cells that the name refers to can be changed here. Click 'OK.'
7. Click the 'Office Button' and select 'Save' to save the spreadsheet. If you are prompted with the 'Save As' dialog box, navigate to the location you want to save the file in, enter a name for the file in the 'File Name' text box, and click 'Save.'

Wednesday, December 25, 2013

How to Make a Striped Line on a Bar Graph in Excel on a Mac

1. Double-click the Excel icon on the Dock or find the program in your Applications folder and double-click it. Open the Excel graph through the File menu. Alternatively, find the graph on your hard drive and double-click it.
2. Click on the a bar in your graph and click the 'Format' tab on your toolbar.
3. Click the down arrow on the 'Shape Fill' button. Click 'Texture' and then 'More Textures.' Click the circle by the label 'Picture or texture fill.' Click the 'ClipArt' button. In the search box type 'lines' (without quotes). Press 'Enter.' A selection of graphic lines will appear on your menu. Double-click one and hit the 'Close' button. Your bar graph will appear striped.

How to Print or Page Setup in Excel 2007

1. Click the Office Button at the top left-hand corner. Select 'Print' from the options available and then select 'Print Preview.' In the Print Preview view you will see how the printed copy will look, as well as view and adjust the margins and view page setup options.
2. Select 'Page Setup.' In the dialog box that appears you can set the spreadsheet to print in Landscape mode instead of Portrait. Click the 'Margins' tab to adjust the margins of your spreadsheet as well as vertical and horizontal alignment.
3. Click the 'Header/Footer' tab. Enter any information you want to appear in the header or footer. Click on the drop-down menus below to insert special fields, such as 'Page x of y.'
4. Click on the rightmost tab, labeled 'Sheet.' You can select whether to print gridlines and/or row and column headings here. Click 'OK' after making changes to the page setup.
5. Click the Office Button at the top left-hand corner, then click 'Print,' and 'Print' again. There are several Print options you can choose from.
6. Choose the printer you want to print to by clicking the arrow beside the printer name to view a drop-down list of printer options. Select the printer you want to use.
7. Select the number of copies you want to print. You can also choose whether to print all of your workbook or just part of it under 'Print what.' Define which pages you want to print under 'Print range.' For example, you can choose to print pages 2 to 7 by entering '2-7'. After you make any necessary changes to print options, click 'OK' and your workbook will begin printing.

How to Make Drop Down Fields in Microsoft Excel

1.
Start Microsoft Excel. Open a new blank spreadsheet, or open the spreadsheet to which you want to add a drop down list.
2.
Select the cell or the range of cells in which you want to make a drop down list.
3.
Open the Data Validation dialog by going to the 'Data' menu in Excel 2003 and selecting 'Validation,' or by clicking the 'Data' tab of the ribbon in Excel 2007 and selecting 'Data Validation.'
4.
Go to the 'Settings' tab. Click the drop down arrow under 'Allow' and select 'List.'
5.
Type each list item into the 'Source' box, placing a comma between each word in the list. Click 'OK' to apply the list.

Tuesday, December 24, 2013

How to Find Duplicate Entries on Excel

1. Launch Microsoft Office Excel 2007 on your computer.
2. Select the rows or columns where you want to find the duplicate entries. Point your mouse in the first cell and continue to hold down the mouse while you drag over the cells you wish to search.
3. Click the 'Home' tab and then click 'Conditional Formatting' under 'Styles' in the top menu.
4. Select 'Highlight Cells Rules' from the drop-down menu. Click on 'Duplicate Values.'
5. Select 'Duplicate' under 'Format cells that contain:' in the Duplicate Values dialog box.
6. Select a highlighting style from the drop-down menu in the Duplicate Values dialog box. Styles include a variety of fill and text-color options.
7. Click 'OK.' All duplicate values will be highlighted in your Excel spreadsheet.

How to Have Three Secondary Axes on Excel 2007

1. Open the chart you want to work with by clicking the Office button, 'Open,' and selecting the file.
2. Click the chart you want to add the additional axes to. This brings up the 'Chart Tools' tab in the top right corner of Excel.
3. Click the 'Format' tab. Select the arrow under 'Chart Area' (in the 'Current Selection' group on the left side of the toolbar). This brings down a selection box. Choose the series you want to add an additional axis to.
4. Click 'Format Selection.' Format Selection is directly below the 'Chart Area' tab.
5. From the 'Series Options' tab (this should be the first one displayed), click 'Secondary Axis' and then click the 'Close' button.
6. Click the 'Layout' tab and choose 'Axes' from within the Axes group. Click on 'Secondary Vertical Axis' and choose a display option.
7. Repeat Steps 2 to 6 to add another axis.

How to Convert Excel 2003 to PDF

1. Open the Microsoft Excel 2003 spreadsheet that you want to convert to PDF.
2. Click the 'File' option from the top navigation bar.
3. Select 'Print' from the context menu.
4. Select 'Adobe PDF' from the 'Printer Name' drop-down box. A file dialog box will appear.
5. Type a new name for the new PDF file.
6. Specify a new location for the file, if desired, using the drive and folder navigators.
7. Click the 'Save' button. The Excel 2003 file has now been saved as a PDF.
8. Open the new PDF file in Adobe Acrobat or Reader and verify that it has was converted correctly.

How to Create a UserForm in Excel 2007

1. Start Excel 2007 and open a new workbook. Click the Microsoft Office Button (on the upper left corner of your task bar), and select Excel Options. In the new pop-up window, click Popular, and check the box titled 'Show Developer tab in the Ribbon.' In Microsoft Office Excel 2007, click Visual Basic in the Code group on the Developer tab. You should now see the Developer Tab on the top of your screen.
2. Click Visual Basic on the Developer tab. Visual basic can be found under Code group on the Developer tab. Now go the Insert menu and select User Form. You can now customize your User Form according to the task you want it to accomplish. If necessary, draw frame controls and label controls. Labels make data entry easier by naming columns and are often needed for data-driven forms.
3. Type the macro code. Double-click the user form to bring up the code window, where you will insert the macro code.
4. Save the user form. When you are finished with the macro, save it and return to Excel. Now go to the Developer tab and click on Macros. In the new pop-up window, click on Show User Form, and press Run to activate your User Form.

How to Calculate Variance in Excel 2007

1. Enter your data into one row in Microsoft Excel.
2. Type '=VAR(' in a cell that does not have data. For example, if all of your data is in column A, then use cell B1 for the variance function.
3. Highlight your data by clicking on the top of the data and dragging the mouse down to the bottom of the data.
4. Type ')' in the cell with your variance function to close the function and then press Enter. The cell now displays the variance of your data.

How to Use Microsoft Excel COUNTIF

1. Open or create a Microsoft Excel worksheet with columns or rows of text in which you want to tally the number of cells containing a single word and nothing else. For example, you could have a column labeled 'Trip Date' and another column labeled 'City.' Let's say you want to find out how many times you went to Detroit in a month.
2. Click the cell at the bottom of the City column. Although you can place the Countif formula in any location, for this example, we will be placing it beneath the City column to count the occurrences of the word Detroit.
3. Type '=COUNTIF(range,criteria),' where the range equals the cell range in which you want to count and the criteria equals the word. For example, you could type the following to count the number of times Detroit appears between cells B2 and B12:=COUNTIF(B2:B12,'Detroit')
4. Press 'Enter' to get your result.

Monday, December 23, 2013

How to Format Excel Margins

1. Click the 'Page Layout' button on the menu ribbon at the top of the screen.
2. Click the 'Margins' button.
3. Click 'Custom Margins' at the bottom of the menu.
4. Click in each text box, and enter the amount you want the margins to be. Do the same for the header and footer.
5. Check the two 'Center on Page' boxes to center the image horizontally and vertically, then click 'OK' to finalize the formatting.

Sunday, December 22, 2013

How to Adjust the Right Margin in Excel

1. Open Microsoft Excel.
2. Click the 'Page Layout' tab.
3. Click 'Margins' and select 'Custom Margins.'
4. Change the value of the right margin to whatever you need.
5. Click 'OK' to close the 'Custom Margins' box.

How to Make a Line Graph From a Data Table

1. Open the Excel worksheet.
2. Click and drag to select the data and categories on the worksheet. A dark outline appears around the range of cells.
3. Click the 'Insert' tab on the command Ribbon.
4. Click the 'Line' arrow in the 'Charts' group. A list of chart thumbnails appears, such as '2-D Line' and '3-D Line.'
5. Click the 'Line' button from the 'Line' section. The data converts to an embedded line graph that hovers over the worksheet. The 'Chart Tools' ribbon appears.
6. Edit the chart using the buttons in the 'Design,' 'Layout' or 'Format' tabs, if preferred. The 'Design' commands affect the line colors. The 'Layout' commands can insert picture files, text and titles. The 'Format' commands change the chart's outline and text formats.

How to Use Ticks in Excel

1. Open the Excel file.
2. Click 'Developer Tools' on the command ribbon.
3. Click the down arrow on the 'Insert' button in the 'Controls' group. A list of controls appears.
4. Click the 'Check Box' button. This button displays a check mark inside of a square. The pointer changes to a ' ' symbol.
5. Click on the cell to place the check box. A square and the text 'Check Box 1' display on the worksheet.
6. Edit the text if desired. For example, delete 'Check Box 1' and type an item or a step to compile a list.
7. Click the text to insert a check or tick in the check box. To remove the check, click again on the text.
8. Press 'Ctrl' and 'S' simultaneously to save the file.

Friday, December 20, 2013

How to Plot a Secondary Axis on Microsoft Excel

Secondary Vertical Axis
1. Open the spreadsheet in Excel that contains the chart to which you want to add a secondary axis. Click the 'Microsoft Office' button, then click 'Open' and then locate the file on your computer. Click 'Open' to open the file. Your existing chart should pop up on the screen when you open the spreadsheet. Depending on the type of graph you created, it could be a bar chart, line graph or other graph type.
2. Click the data series in the chart you want to plot on a secondary axis.
3. Click the 'Format' tab, then click 'Format Selection' in the 'Current Selection' group.
4. Click the 'Series Options' tab, then click 'Secondary Axis' below 'Plot Series On.' The secondary axis will be display in the chart.
Secondary Horizontal Axis
5. Click anywhere on the chart to display Chart Tools.
6. Click the 'Layout' tab, then click on 'Axes' from the 'Axes' group.
7. Click 'Secondary Horizontal Axis,' then click the type of display you want. For example, if you want an unlabeled axis, click 'Show Axis Without Labeling.'

How to Open Excel 2007 in an Earlier Version of Excel

1. Go to the Microsoft Download Center online.
2. Click on the Microsoft Compatibility Pack. Click 'Download.'
3. Click 'Run' in the box that appears on the screen.
4. Click 'Run' in the next box that appears on the screen.
5. Check the box 'Accept' to accept the terms.
6. Click 'OK' in the next box to appear on the screen. Wait for the loading to finish.
7. Open Excel and double-click on the Excel 2007 file that needs to be opened. Wait for the file conversion to complete.
8. Makes changes and work in the file, saving it when done.

Thursday, December 19, 2013

How to Insert a Tab Name in a Cell

1. Open Microsoft Excel. Choose the spreadsheet you want to work with.
2. Click on the cell where you want to insert the tab name.
3. Enter the following formula into the cell:=MID(CELL('filename',A1),FIND(']',CELL('filename',A1)) 1,256)

How to Convert Excel to DAT

1. Double click to open your Microsoft Excel 2010 file.
2. Click the 'File' tab in the upper-left corner, then select 'Save As.'
3. Click the drop-down menu to the right of 'Save As Type' and select 'CSV (Comma Delimited).'
4. Click the 'Save' button, click 'OK' to save the active sheet, then click 'Yes' to keep the worksheet in CSV format.
5. Right click the CSV file you just created, click 'Open With' and select 'Notepad.'
6. Click 'File' at the top of the window, then click 'Save As.'
7. Click the drop-down menu to the right of 'Save As Type' and choose 'All Files.'
8. Click inside the 'File Name' field and add '.dat' to the end. For example, if your filename is 'xxxxxx,' modify it so it reads, 'xxxxxx.dat.'
9. Click the 'Save' button.

How to Change the Macro Security Levels on Excel 2003

1. Open the Microsoft Excel 2003 application on your computer, and then click the 'File' option from the top toolbar menu.
2. Click the 'Open' option, and then select the Excel 2003 document you want to work with. Click the 'Open' button.
3. Click the 'Tools' option from the top Excel toolbar menu, and then click the 'Options' button.
4. Click the 'Security' tab in the new dialog box that appears on the screen. Click the 'Macro Security' option.
5. Select the 'Security Level' tab and then select the 'Very High', 'High', 'Medium' or 'Low' option. Click the 'OK' button and close out of the dialog box.

How to Delete the First Five Characters in Excel 2007

1. Open your spreadsheet in Microsoft Excel 2007.
2. Locate the cell that contains the text you need to truncate.
3. Enter the following text in a new cell, replacing 'old_text' with the cell reference of the text you need to truncate.=REPLACE(old_text,1,5,'')As an example, to delete the first five characters from cell A1, you would enter:=REPLACE(A1,1,5,'')Alternatively, you can replace 'old_text' with the actual text encased in quotes. As an example, to delete the first five characters from the text 'truncate me!,' you would enter:=REPLACE('truncate me!',1,5,'')

Wednesday, December 18, 2013

How to Calculate Business Quarters in Excel Not Based on Calendar Year

1. Open a new workbook in Excel and enter a list of random dates that can be used for testing.You can copy these example dates to your sheet beginning in cell A1:1/4/2010
2/11/2010
3/21/2010
4/28/2010
6/5/2010
7/13/2010
8/20/2010
9/27/2010
11/4/2010
12/12/2010
2. Determine the number value of the month in which your 'year' starts (e.g. January is 1 and November is 11). For this example, the year will start in October (month 8).
3. Subtract one from your month value. In this example, the result would equal seven (7).
4. Substitute your new value into the following equation:=MOD(CEILING(22 MONTH(
) -
- 1,3)/3,4) 1For this example that would be:
=MOD(CEILING(22 MONTH(A1)-7-1,3)/3,4) 1
5. Copy and paste the equation into cell B1 in your Excel worksheet.
6. Click and hold the mouse button down on cell B1 and drag your mouse cursor down to highlight all the cells from there to B10.
7. Hit the keys 'Control' ('Ctrl') and D at the same time to copy the formula down to all the cells.Your worksheet should now look like the following data:01/04/20102
02/11/20103
03/21/20103
04/28/20103
06/05/20104
07/13/20104
08/20/20101
09/27/20101
11/04/20102
12/12/20102

How to Copy Formulas Without Changing Them in Excel

1. Open your Excel document.
2. Click on the cell containing the formula you wish to work on.
3. Locate your cell references in the formula shown in the Formula Bar. These will be a letter followed by a number, such as A3 or D14.
4. Place a dollar sign ($) in front of the letter and number of the cell reference you don't want to change. Examples would be $A$3 or $D$14.
5. Copy your formula to the other parts of your spreadsheet--you'll see that Excel does not change your cell references.

Tuesday, December 17, 2013

How to Center Horizontally and Vertically in Excel

Center Horizontally
1. Click the cell or cells in which you want to center data horizontally.
2. Click the 'Home' tab.
3. Click the 'Center' button located in the 'Alignment' group. The Center button centers the text horizontally in the selected cell.
Center Vertically
4. Click the cell or cells in which you want to center data vertically.
5. Click the 'Home' tab.
6. Click the 'Middle Align' button located in the 'Alignment' group. The Middle Align button centers the text vertically, between the top and bottom of the cell.

Monday, December 16, 2013

How to Lock a Formula in Excel 2007

1. Select the cell containing the formula you wish to lock by highlighting it.
2. Click on the 'Home' tab, which is located on the top of the Excel window.
3. Navigate to the 'Cells' module, which is on the right half of the Excel window. Select 'Format.' A menu displaying cell formatting options will appear.
4. Navigate to the 'Protection' section of the format menu. Click on 'Lock Cell,' highlighting the lock icon.
5. Protect the sheet; until you do so, locking the formula cell will not take effect. Repeat steps 2 and 3. Select 'Protect Sheet' from the cell formatting menu. A dialog box titled 'Protect Sheet' will appear.
6. Select the 'Protect worksheet and contents of locked cells' option. Type in a password that users must enter to unprotect the sheet into the password field; this is not a required step. In the 'Allow all users of this worksheet to' section, choose privileges that you wish users to have when working with the sheet. Finish by clicking 'OK.'

How to Do Descriptive Statistics in MS Excel 2007

Analysis ToolPak
1. Install the Microsoft Office Analysis ToolPak. Click the 'Microsoft Office Button' and then 'Excel Options.'
2. Click 'Add-ins' and then 'Excel Add-ins' located in the Manage box. Click 'Go.'
3. Select 'Analysis ToolPak' in the Add-ins Available box and click 'OK.' If you do not see the Analysis ToolPak option, click 'Browse' to locate it. Click 'Yes' to install it if a prompt signifies that it is not installed on your computer.
Descriptive Statistics
4. Collect the data you would like to analyze. The descriptive statistics tool will be used later to organize and interpret the data for you.
5. Open Excel to generate a new spreadsheet.
6. Type the label of your first column in cell 1A. Enter the data values that you are evaluating in the cells directly below your label. For example, a real estate professional wishing to analyze prices of homes might label column 1A Sale Price. Thereafter, the various sale prices will be keyed into cells 2A, 3A, 4A and 5A.
7. Save the file so that you do not lose your data. Click on 'File,' 'Save.' Type in the file name and click 'Save.'
8. Select 'Tools,' 'Data Analysis.' This can be found under the Data tab. Click 'OK' to open the dialog box.
9. Choose 'Descriptive Statistics' in the dialog box. This is the tool you want to use. Click 'OK.'
10. Click on the small chart box located to the right of the input range. Highlight the spreadsheet column containing the data that you want to summarize. Do this by holding down the left mouse button and highlighting all of the data in the column you want to select. This data will be placed in your input range. Click on the small chart box again to return to the Descriptive Statistics box. If you also highlight the label, click on 'Labels in First Row' on this screen.
11. Click 'Output Range' and indicate to which cell you want the results to go. For example, choose cell H1 by typing 'H1' in the output range.
12. Select 'Summary Statistics' and click 'OK.' The descriptive statistics are now generated. Double-check the count in the results to make sure it included the correct number of items from your list of data.

How To Calculate Mortgage Payments in Excel

1. Start a new, blank workbook in Excel. In Excel 2007, click the 'Office' button and click 'New'; then click 'Microsoft Online.' In Excel 2003, go to the 'File' menu and click 'New'; then go to the 'Templates' section of the task pane.
2. Type 'mortgage calculator' in the 'Search Microsoft Online' box and click 'Go.' A list of possible templates will appear.
3. Select the 'Mortgage Payment Calculator' template and click the 'Download' button. The Mortgage Payment Calculator template will open as a new Excel spreadsheet.
4. Enter the mortgage loan amount in cell C7. Enter the interest rate in C8. Type the number of years of the mortgage loan in cell C9. The worksheet will calculate the monthly payment amount, as well as other data, such as the number of payments and equity.
5. Enter up to five different mortgage-loan amounts into columns C through G. This will make it easier to compare options, such as a 15-year mortgage vs. a 30-year mortgage, or different interest rates or loan amounts.

Sunday, December 15, 2013

How to Prepare an Excel PivotTable

Using Excel Data
1. Open the Excel file that has the information that you want to make into a PivotTable.
2. Edit the range of data that you will use to remove any blank rows. Also place a column name in the top row, for any column that doesn't have one.
3. Select any cell in the range of data that you want to use.
4. Click on the 'Insert' tab at the top of the screen, and then click the 'PivotTable' button that appears on the toolbar. A small window will appear, and Excel will automatically select the entire range of data. Press 'OK' to continue, and the PivotTable will be created on a new worksheet.
Using External Data
5. Open the Excel 2010 program by double-clicking the Excel icon.
6. Click the 'Insert' tab at the top of the screen and then click the 'PivotTable' button on the left side of the toolbar.
7. Select the 'Use an External Data Source' radio button and then press the 'Choose Connection' button just below the radio button. A list of available connections will show up.
8. Click the connection you want to use and then click 'Open.' If you do not see the connection that you need, click on 'Browse for more' and then navigate to the connection file and click 'Open.'
9. Select 'New Worksheet' to place the PivotTable on a new worksheet, or select 'Existing Worksheet' and then input the cell where you want the PivotTable to go. Click 'OK' when you are done.
Placing Information in the PivotTable
10. Select a cell anywhere in the PivotTable placeholder graphic to bring up the field list on the right side of the screen.
11. Place checkmarks on the field list to the right side of the window, next to the fields you want to add to the PivotTable. Each field that you see is equal to one column of data from your original data set. When you place a checkmark, you will see the field appear both on the PivotTable in the main window, and in one of four boxes to the right of the field list. Each box corresponds to a different area on the PivotTable.
12. Drag and drop fields between the boxes on the right of the screen to place them where you want them on the PivotTable. Fields in the 'Column Labels' box will appear at the top of the PivotTable, while fields in the 'Row Labels' box will appear along the left side. Any field in the 'Values' box will have its data make up the body of the PivotTable, and any field in the 'Report Filter' will appear in a small drop-down box above the PivotTable, where you can filter the entire table.

How to Turn Off Sharing in Excel 2007

1. Launch Microsoft Excel and locate the 'Review' tab on the ribbon located on the top of the screen.
2. Click the 'Share Workbook' tool located in the 'Changes' group to launch the 'Share Workbook' dialog box. You should see a check mark next to the 'Allow Changes' check box.
3. Click the 'Allow Changes' check box to clear the check mark. Click 'OK' to save your changes and disable sharing.

How to Interpret the Linear Regression Summary in Microsoft Excel 2003

1. Right-click on the regression line in your chart, and choose Properties. Check 'Display equation on chart' and 'Display R-squared value on chart'. Click OK.
2. Look at the R-squared value displayed next to the regression line. The R-squared value represents the amount of variability in the data that is explained by the linear regression analysis. If all the data lies exactly on the regression line, the R-squared value will be 1. If the R-squared value is 0, that means there is no correlation between the two datasets.
3. Turn your attention to the equation listed above the R-squared value. It will be of the form 'y = m x b', where m and b have been replaced by numbers. This equation describes the linear regression line. The 'm' value is the slope of the line, and the 'b' value is the location where the line crosses the vertical axis. You can use this equation to predict values in the dataset based on their value on the horizontal axis; just multiply their horizontal location by the 'm' value and then add the 'b' value to the result; this will give you the best estimate of the location of that point based on the linear regression analysis.
4. Look at the slope of the line. If it slopes downwards to the right, the data is 'negatively correlated,' if it slopes upward, the data is 'positively correlated.' Positive correlation means that the datasets tend to agree with or reinforce each other; negative correlation means that they tend to be at odds or mutually exclusive.

How to Create an Excel Spreadsheet to Figure Out Sick Leave

1. Type 'Vacation and Sick Leave Record' in cell E1. In cell E2, write the period that the sick leave worksheet is applicable to. For example, write '1/1/2010 to 12/30/2010.'
2. Type the employee's name in cell A4. Optionally, type their maximum sick leave accumulation on the next line in days or hours.
3. Type the word 'Month' in cell A6. Type the months January through December directly underneath, in the same column, with one month per row.
4. Type the word 'Amount' in cell B6, the word 'Used' in cell C6 and the word 'Balance' in the cell D6.
5. Enter the total number of the employee's available sick leave hours in cell B7. For example, if the employee has 120 hours, write '120.'
6. Type the following formula into cell D7:=B7-C7.Copy the formula to cells D8 to D17 by dragging the fill handle (the little black square in the bottom right corner of the cell) to cell D17.
7. Click on cell B8, type '=', then click on cell D7. This transfers the balance from the previous month to the 'amount' column for the beginning of the second month. Drag the fill handle of cell B8 to cell B17.

How to Password Protect Excel 2003

1.
In order to protect your Microsoft excel worksheet go to TOOLs menu select PROTECTION. From there you should see a menu like the picture on your left.
2.
From there you will be given a series of options that will allow you to protect a Microsoft excel worksheet, workbook or a range on your specific file. As you can see from the image on your left you can get very granular with this.
3. Make your appropriate adjustments and then click OK. You should be prompted to retype the password. Once that is done make sure you save your changes and close the document and reopen it to see if your password took.

Saturday, December 14, 2013

How to Create a Box Plot in Microsoft Excel 2007

Set Up the Plot Data
1. Create a table with a column for each data set. In the table rows, add formulas for the calculations of (in order) the minimum, first quartile, median, third quartile and maximum for each data set using the Excel functions MIN, MAX, MEDIAN and QUARTILE (or PERCENTILE). This is the 'summary table.'
2. Create a second table with the same rows and columns as above. This table will contain the values used for the plot. This is the 'plot data table.'
3. Add a formula for each data set's maximum value in the plot data table that is the maximum minus the third quartile values from the summary table.
4. Add a formula for each data set's third quartile value in the plot data table that is the third quartile minus the median values from the summary table.
5. Add a formula for each data set's median value in the plot data table that is the median minus the first quartile values from the summary table.
6. Add a formula for each data set's first quartile value in the plot data table copying the first quartile value from the summary table.
7. Add a formula for each data set's minimum value in the data table table that is the first quartile minus the minimum values from the summary table.
Create the Plot
8. Select the range containing the third quartile, median and first quartile of all the data sets in the plot data table.
9. Open the Insert ribbon. Click 'Column' on the charts. Select 'Stacked Column' from the '2-D Column' type charts.
10. Click 'Select Data' under 'Design' on the 'Chart Tools' section of the ribbon. Modify the 'Series' order so 'Series 3' is the bottom segment on the chart and 'Series 1' is the top segment on the chart.
11. Click on the bottom segment of one of the columns in the chart. Select 'Layout' under 'Chart Tools' on the ribbon. Click 'Error Bars' and select 'More Error Bars Options.' The 'Format Error Bars' window appears.
12. Select 'Minus' for the 'Direction.' Select 'Custom' for the 'Error Amount.' Click 'Specify Value' and select the range for all the minimum values in the plot data table for the 'Negative Error Value' in the 'Custom Error Bars' window. Click 'OK' to exit this window and 'Close' on the next window to return to the chart.
13. Click on the top segment of one of the columns in the chart. Go to the 'Format Error Bars' window as above. Choose 'Plus' for the 'Direction' and use the maximum values in the plot data table for the 'Positive Error Value' range for the 'Custom Error Amount.'
14. Right-click on the bottom segment of one of the columns on the chart and select 'Format Data Series.' Set the 'Fill' to 'No fill.' Set the 'Border Color' to 'No line.' Close the window.
15. Delete the chart legend. Add polish by formatting the chart colors, adding a title and other finishing touches.
16. Select the rows containing the plot data table. Under 'Home' on the ribbon, click 'Format' and select 'Hide Rows' under the 'Hide Unhide' sub-menu.

How to Do Percentages With Excel 2003

1. Open Excel 2003, and open a workbook that contains a column with amounts and another column with totals. Click 'File' on the menu bar, and click 'Open.' Browse your files, and locate the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Click in the next available column in your workbook. Type '=.' Click in the first cell that contains the first amount. Type '/.' Click in the first cell that contains the first total, and press the 'Enter' key. A value is generated.
3. Highlight the column containing this new formula. Click the '%' symbol on the standard toolbar. Your value is transformed into a percentage.

How to Maximize a Sheet in Excel 2007

1. Open an Excel worksheet by clicking on the round 'Office' button in the top left-hand corner of Excel 2007. Select 'Open' from the drop-down menu on the left. Choose the file name of the Excel document that you wish to open.
2. Click on the 'maximize' button in your Excel work window. You'll find it just to the left of the 'close' button in the top right corner. An 'X' identifies the close button, and a square, the maximize button. The maximize function changes your worksheet to maximum size within your open Excel document.
3. Click on the 'restore down' button to make your worksheet smaller. The restore down function changes your worksheet to smaller size within your open Excel document. You'll find the restore down button in the same location as the maximize button. The two buttons function as a toggle: When you have maximized the sheet, the button represents restore down.

Friday, December 13, 2013

How to Sort and Filter in Microsoft Excel

1. Open Microsoft Excel and go to 'File' and 'Open' to open the document containing data you want to sort and filter. Highlight the document and click 'Open.' The document will open in a new window.
2. Highlight the data you want to sort. You may select an entire worksheet, a column, multiple columns or specific data within a column.
3. Complete a simple sort by selecting the 'Home' tab from the ribbon and from the 'Editing' group selecting 'Sort and Filter.'
4. Click the appropriate option that matches your preferred sort. For example, if your data is a group of dates, click 'Sort Oldest to Newest' or 'Sort Newest to Oldest.' If your data is text, select either 'Sort A to Z' or 'Sort Z to A.' If your data consists of numbers, select 'Sort Smallest to Largest' or 'Sort Largest to Smallest.' The sort will occur upon clicking this command.
5. Complete an advanced custom sort by selecting 'Editing', 'Sort and Filter' and 'Custom Sort.' Select the appropriate options for your sort from options that include columns, value or order---with or without the column headers. You may also sort by multiple levels. For example, your data may be sorted alphabetically at the first level and by date at the second level.
6. Filter your data by selecting 'Home,' the 'Editing' group and 'Filter.' A drop-down arrow appears next to the first cell of data. Click the drop-down and uncheck any data that you want to filter out. You may perform a sort based on the data that remains.

How to Use Absolute References in Microsoft Excel

1. Enter a value into the cell you want to keep constant.
2. Select another cell in the Excel spreadsheet for the formula that will use the absolute reference.
3. Enter the formula. Use the dollar sign ($) in front of the row reference to keep to keep the row absolute or in front column reference to keep it absolute. For instance, '$C$21' is an absolute reference for cell 'C21,' while 'C$21' an absolute reference to row '21' but but a relative reference column 'C.'

Thursday, December 12, 2013

How to Convert Word to Excel 2007

1. Launch Microsoft Word and open the document that you want to convert to Excel. Click once on the Microsoft Office button and select the 'Open' option. Locate the folder in which the document is saved in the 'Look in' section of the 'Navigation Pane.' Double click on the file name to open it. If you do not have either program, a free trial may be downloaded from the Microsoft site.
2. Save the Word document as a text (TXT) file. Click once on the Microsoft Office button and select the 'Save As' option. Select a folder in which to save the text file using the 'Save in' menu. Use the 'Save as type' menu to select the 'Text (TXT)' option. Type a name for the text file in the 'File name' field and click once on the 'Save' button.
3. Launch Microsoft Excel 2007 and import the text file. Click once on the 'Data' tab and locate the 'Get External Data' section. Click once on the 'From Text' option. Use the 'Look in' menu to locate the folder in which the text file is saved. Double click on the file name to open it.
4. Use the 'Text Import Wizard' to convert the information from the Word document to Excel. Select the 'Delimited text files (.txt)' option. Click once on the 'Next' button. Depending on the type of data that was in the Word document, select the appropriate separator, for example commas or tabs, to separate the text into Excel fields. Click once on the 'Finish' button to complete the process of converting the Word document to Excel 2007. Remember to save the Excel 2007 file by clicking once on the Microsoft Office button and once on the 'Save As' option.

How to Unlock Grayed Out Menus in Excel 2007

1. Position your cursor so that it is on one of the sheet tabs at the bottom of the screen. Make sure the sheet tab is highlighted.
2. Right-click the sheet tab. Choose 'Ungroup Sheets' from the drop-down menu.
3. Click one of the menus. All of the options should now be visible. If the options are still grayed out, right-click the sheet tab again and choose 'Ungroup Sheets.'

How to Remove a Contribute Toolbar From Excel 2003

1. Open Excel. Customizations to the toolbar interface are always accomplished within the program. However, it does not matter what file is opened within Excel, or if the program window is open without any files loaded.
2. Locate the 'Contribute' toolbar. It is important to see the toolbar when it is active so you can easily verify if the removal process was successful. As there are many toolbars, removing a single toolbar is not always obvious unless you are aware of its precise location in the program window.
3. Click the 'View' menu. Select the 'Toolbars' submenu. If the 'Toolbars' submenu is not listed, the menu is set to automatically collapse and show only the most frequently used features. Click the double arrow at the bottom of the 'View' menu to fully expand the list of items and select the 'Toolbars' submenu.
4. Locate the 'Contribute' item in the 'Toolbars' submenu. If the 'Contribute' toolbar is turned on, it will show a check mark next to its listing. Click on the 'Contribute' item in the list and the check mark will disappear. The toolbar is no longer active and it is removed from the Excel 2003 screen.
5. Right-click on any toolbar area or button in the Excel 2003 window to display the toolbars context menu as an alternative to using the 'View' menu. Remove the 'Contribute' toolbar in the same fashion using this pop-up menu.

How to Open WB3 Files in Excel 2003

1. Launch 'Excel.'
2. Go to 'File.' Select 'Open,' choose 'Quattro Pro/DOS' from the 'Files of Type' drop-down menu. Navigate to the folder where your WB3 file is located, and double-click to select the file.
3. Go to 'File,' and select 'Save As.' Choose 'Excel 97-2003 Workbook (*.xls)' from the 'Save as Type' options. Click 'Save.'

How to Create Mailing Labels From an Excel Database

1. Open Microsoft Word. Click the 'Mailings' tab and click 'Start Mail Merge.' Select 'Labels.' The 'Label Options' dialog box will open, where you can set up the labels.
2. Select the type of printer you are going to use under 'Printer Information.' Click the 'Label Vendors' list and select the manufacturer of your label sheets. Select the product number listed on your label sheet packaging from the 'Product Number' list. Click 'OK.' The sheet of labels is set up as a table in your document.
3. Click the 'Mailings' tab, then 'Select Recipients' in the 'Start Mail Merge' group. Click 'Use Existing List.' In the dialog box, browse through your computer files to select the Excel database file containing your address list. Double click the file.
4. Select particular recipients if you don't want to use your whole Excel list. To do so, click 'Edit Recipient List' in the 'Start Mail Merge' group on the 'Mailings' tab. Choose individual records by checking the box next to each record you want and unchecking the ones you don't want to use.
5. Set up the mail-merge fields, which will match each address component from your list to a placeholder on your label document. Click 'Match Fields' in the 'Write Insert Fields' group on the 'Mailings' tab. The dialog box will open, showing a list of address elements on the left side and corresponding column headings from your address list on the right side. Click each drop-down menu and select the correct column heading you want to use for each address element. Only select the address elements you want to use in your labels.
6. Click the first label on your Word document. Add any content, such as text, picture or logo, that you want to appear on each label. To insert an image, click the 'Insert' tab, then 'Picture' in the 'Illustrations' group. Select an image file from your computer, then click 'Insert.'
7. Insert the mail-merge fields, which serve as placeholders until you merge the labels with your address list. Click where you want to insert the address on the first label. Click 'Address Block' in the 'Write Insert Fields' group on the 'Mailings' tab. Select the address elements you want to insert and how you want them formatted. Click 'OK' to insert the address block.
8. Click 'Update Labels' in the 'Write Insert Fields' group to duplicate the data from the first label onto all the other labels.
9. Preview the merge results before completing the labels. Click 'Preview Results' on the 'Mailings' tab. If you're satisfied with them and are ready to print, click 'Finish Merge' in the 'Finish' group on the 'Mailings' tab. Click 'Print Documents.' Specify whether you want to print the whole set of labels or just a portion of them.
10. Connect your printer to the computer and feed it with the labels sheets. Click 'Print' and wait while your labels print out.

Wednesday, December 11, 2013

How to Set Up Formulas in Excel 2007

1. Click the cell where you want to display the results.
2. Press '=' on your keyboard to start a formula.
3. Add a parenthesis and the name of the first cell you want to include in your formula. For example, your formula to this point should look something like this: =(A1
4. Add the operator -- ' ,' '-,' '*' or '/' -- you want this formula to perform. For example, =(A1
5. Type the location of the next cell for your formula and repeat Steps 3 and 4 -- minus the parenthesis -- until you have listed all the cells you want to include in your formula. For example, =(A1 A2 B1 B2 C1 C2
6. Add a closing parenthesis and press 'Enter.' Your results should appear.

How to Change Appearance in Excel 2007

1. Choose a document theme to unite all of your Office 2007 programs with the same look. A document theme shares the same colors, fonts, lines, fill effects and other style choices. Select a document theme from the available choices or create a new document theme by going to Page Layout and selecting 'Themes.' Click on a document theme from 'Built-in' or 'Custom.' When you create a custom theme, save it under a new name.
2. Click Page Layout View to adjust margins or add headers and footers. This feature is similar to the Print Layout view in Word.
3. Use the different style choices to change the look of tables, charts and diagrams. Start with the quick styles (pre-defined styles) and customize to your liking.
4. Change chart and table Layout options to introduce changes that include moving items around. Charts and tables also have different styles to select. Go to the Design Tab or Chart or Table Styles, and click 'More.'
5. Alter your color scheme with a click of the Microsoft Office button. Click 'Excel options,' and then 'Popular.' Choose from the large number of color schemes. Change tab color by right clicking the worksheet tab. Aim at Tab Color, and choose your color.
6. Add formatting to charts not only changes the appearance, but also emphasizes important data. Try borders, fonts, bubbles or 3-D effects. If you really want an eye-catching look, try the 'Exploding Pie' or 'Doughnut Slice.' Pull up the Format dialog box to make changes. You can also right click chart items to format.
7. Fill charts not only with color, but also texture and pictures with the 'Fill Effects' command.

How to Recover an Excel File That Was Saved Over

Using AutoRecover
1. Click 'Start' and then 'Search.'
2. Select 'All Files and Folders' when asked what you want to search for.
3. Type in '*.xls' (without the quotation marks) under 'All or Part of the File Name.'
4. Click on the arrow next to 'More advanced options' and make sure that the file type reads 'All Files and Folders.'
5. Click the boxes next to 'Search System Folders' and 'Search Hidden Files.'
6. Click 'Search.'
7. Look for an earlier version of your file (it should have a similar name). When it appears, open it to see if it is the version you want.

How to Freeze Rows Columns

1. Place the cursor in the cell directly below the row you want to remain visible (freeze) and directly to the right of the column you want to remain visible (freeze). For example, if you want Row 1 and Columns A through C to remain visible (freeze), place the cursor in cell D2.
2. Click on the 'Window' menu.
3. Select 'Freeze Panes.'
4. Move the cursor through the spreadsheet and notice that Row 1 and Columns A through C are frozen. In other words, they always remain visible while the rest of the data move.
5. Click on the 'Window' menu and select 'Unfreeze Panes' to remove the frozen rows and columns.

Thursday, November 28, 2013

How to Make a Chart in the Same Sheet in VBA

1. Open Excel 2010 and show the Developer tab if it isn't already showing. Click on 'File' and then 'Options.' Click on 'Customize Ribbon' on the Categories pane. Select 'Developer' from the list of main tabs and then click on 'OK.'
2. Click on the 'Developer' tab that now should be showing in the Ribbon. Click on 'Visual Basic' to open the Visual Basic editor.
3. Double-click on the worksheet in which you want to embed the chart from the Project pain. All of the worksheets that are currently in your project is listed in the pane. The code for the worksheet will open in the Code window. If you haven't already added code to the worksheet the Code window will be blank.
4. Click on 'Insert' and then 'Procedure.' Type in a name for the procedure in the name window, leave all the other options the same and then click on 'OK.' For the example in this article, name the procedure 'embedChart.'
5. Add the code that will embed a chart into the worksheet. Click between the 'Public Sub...' and 'End Sub' declarations that are now in the Code window. You will add the code for the procedure in between the two declarations. Microsoft has developed the code that will create a chart and embed it in the current spreadsheet. Copy this code into your procedure or write your own.'Sub embedChart()Dim chtNew As ChartSet chtNew = Charts.AddSet chtNew= chtNew.Location(Where:=xlLocationAsObject, Name:='Sheet1')With chtNew.ChartType = xl3DPie'Set the data range source for the chart..SetSourceData Source:=Sheets('Sheet1').Range('A1:H2'), PlotBy:= _xlRows.HasTitle = True.ChartTitle.Text = 'My Pie Chart'End WithEnd Sub'
6. Click 'Save' on the Visual Basic editor's toolbar and close. Go to the worksheet that you embedded the chart in and check to see that it is showing.

How to Capitalize Everything in a Row in Microsoft Excel 2003

Capitalizing a Row of Cells
1. Start Microsoft Excel 2003, and open your spreadsheet.
2. Click the row number, not the cell, of the row immediately beneath the one you want to capitalize.
3. Right-click and select 'Insert' to insert an empty new row.
4. Click the cell in the new row that is directly beneath the left-most cell of the row you want to capitalize.
5. Type '=UPPER(name of cell immediately above the one you are typing in)'. Press 'Enter.'
6. Click the cell you just typed in to select it, and then hold the cursor in the lower-right corner of the cell until a black plus sign appears.
7. Hold the left mouse button down, and drag the cursor to the right, highlighting the whole row you want to capitalize.

How to Add Buttons to an Excel Spreadsheet

1. Access the Button tool. In Excel 2003 and earlier versions, do this by clicking 'View,' 'Toolbars,' 'Forms' and then clicking the Button tool on the Forms toolbar. In Excel 2007 and later, select the 'Developer' tab, then click 'Insert' and select the Button tool.
2. Click where you want the top-left corner of your button to be, and drag to create a rectangular outline. Let go and your button will appear.
3. Right-click the button and click 'Assign Macro.' This will bring up a dialog box listing all of Excel's saved macros. (The box may appear as soon as you've created the button, without the need for the 'Assign Macro' menu selection.) Click on the macro you want and click 'OK.'
4. Right-click the button again. You can now click within the text on the button to change it as you would with any MS Office text. Give the button whatever name you want. You can also right-click on the button's outline and click 'Format Control' to change the font of the button's name.
5. Click the button to run the assigned macro.

How to Use Data Analysis for Random Number Generation in Excel

Activate the Data Analysis Tool Pack
1. Launch Excel from the 'Start' menu or shortcut icon.
2. Click on the Microsoft Office orb to open the menu.
3. Click the 'Excel Options' option at the bottom.
4. Click 'Add-Ins' in the left pane.
5. Verify that the 'Manage' field at the bottom is set to 'Excel Add-Ins' and click the 'Go' button.
6. Click the check box beside 'Analysis ToolPak' and click 'OK' to install the add-in.
Random Number Generation
7. Click the 'Data' tab and select the 'Data Analysis Tools' icon.
8. Select 'Random Number Generation' from the list and click 'OK.'
9. Enter the number of variables you wish to use for your random number output. This determines the number of columns used to display your output.
10. Enter the number of random numbers you wish to generate. This determines the number of rows used to display the output.
11. Select the distribution type to use in the random number generation. The choices are Uniform, Normal, Bernouli, Binomial, Poisson, Patterned and Discrete. Each one is used under a different circumstance depending on the type of data you wish to analyze or produce. For example, Uniform generates a set of random numbers within the range you specify while Normal is used to generate random numbers with a certain mean and standard deviation.
12. Enter the parameters for your chosen distribution type. For example, a Uniform distribution requires you to enter the upper an lower limits of a range of numbers.
13. Enter the cell you want to use as the upper-left point in your output table in the Output Range field.
14. Click 'OK' to generate a random number table based on your selections.

Wednesday, November 27, 2013

How to Convert Office 2007 to Excel 2003

1. Open your Web browser, then go to the Microsoft Office Compatibility Pack download page.
2. Click the blue 'Download' button at the top of the page.
3. Click 'Save' in the download confirmation pop-up box. This downloads the installation file to your computer. Note the save location to help you find the file when you come to install it. The default save location is usually the 'Downloads' library folder.
4. Wait for the file to download. Windows displays a progress bar during the download process.
5. Find the downloaded installation file -- named 'FileFormatConverters' -- on your computer.
6. Double-click the file to install the program. Click 'Yes' if the installation wizard prompts you to restart the computer.
7. Launch Microsoft Excel 2003. Click 'File,' then click 'Open' to open the Excel 2007 file that you want to view or edit. Excel will now convert the new spreadsheet or workbook into a format compatible with the older software version.

How to Scroll on Microsoft Excel

Turning on Scroll Bars in Excel 2010
1. Open Microsoft Excel 2010.
2. Select 'Open' from the 'File' menu.
3. Navigate to and double-click the spreadsheet in which you want to scroll.
4. Click on 'File' again, then select 'Options.' Click 'Advanced.'
5. Locate 'Display options for this workbook' in the right column. Check the boxes next to 'Show horizontal scroll bar' and 'Show vertical scroll bar.' Click 'OK.'
Turning on Scroll Bars in Excel 2007
6. Open Microsoft Excel 2007.
7. Click on the Microsoft Office button in the upper-left corner of Excel. Click 'Open.'
8. Navigate to and double-click the file in which you want to scroll.
9. Click the Microsoft Office button again, then select 'Excel Options.' Click 'Advanced' in the left column.
10. Locate 'Display options for this workbook' in the right column. Check the boxes next to 'Show horizontal scroll bar' and 'Show vertical scroll bar.' Click 'OK.'
Scrolling through Excel
11. Click on the horizontal scroll bar to scroll to the right and left. Click on the vertical scroll bar to scroll up and down. Hold down the mouse button at the screen edge for at least 10 seconds to increase the scroll speed. Hold down the 'Shift' button while scrolling to scroll long distances.
12. Press the 'Scroll Lock' button on the keyboard to scroll through columns and rows as opposed to moving to an adjacent cell. Press the up and down arrows on the keyboard to scroll vertically or the left and right buttons to scroll horizontally. Press 'Ctrl' while pressing an arrow button to scroll through large distances in the worksheet. Press 'Page Up' or 'Page Down' to scroll down a window.
13. Rotate your mouse wheel up or down to scroll. Click the wheel and move the mouse in the direction in which you wish to scroll to accelerate the scroll speed. Reclick the mouse to stop scrolling.

Tuesday, November 26, 2013

How to Convert Corel Quattro Pro to Microsoft Excel

1. Click 'File,' then 'Open' in Microsoft Excel 2003 or earlier. In Microsoft Excel 2007, click the 'Office' button, then 'Open.' The 'Open' dialog box appears.
2. Locate the Corel Quattro Pro file. Click it once and click 'Open' or double-click the file to open it.
3. Save the file as Microsoft Excel spreadsheet. Click on 'File,' then 'Save as' in Excel 2003 or earlier version. In Excel 2007, click the 'Office' button, then 'Save as.' Choose a file location. Leave the name the same or rename the file by typing in the new name in the 'Filename' box. Click 'Save.'

How to Merge Center in Excel

1. Open Outlook by double-clicking on an Excel file. Alternatively, click 'Start,' then 'All Programs,' then 'Microsoft Office' then select 'Microsoft Excel' from the list.
2. Click on the 'Home' tab on the menu screen.
3. Select the cells you want to merge and center by left clicking on a cell and dragging the mouse until all the desired cells are highlighted. You can tell they are selected by the black line surrounding the cells.
4. Click on 'Merge Center' located in the middle of the 'Home' tab menu (it will be under the 'Wrap Text' option).

How to Make Negative Numbers Red in Excel

1. Highlight the cells containing your data. Note that Excel allows you to apply special formatting for different types of numerical data, such as percentages and currency values. If your data contains numbers formatted as different types, for example, both percentages and currencies, make sure the cells you highlight contain numbers formatted as the same type. If all your numbers are formatted the same way, you can highlight all the cells on a worksheet with one command: press and hold the 'Ctrl' key on Windows (or the 'Command' key on a Mac), and then press the 'A' key.
2. Right-click a highlighted cell. In the menu that pops-up, click 'Format Cells' to open a formatting window.
3. Select the 'Number' tab in the 'Format Cells' window, then select 'Number' in the category box. If you have applied a specific format to your cells, such as Currency, Accounting, Percentage, Fraction, Scientific or Special, select that category instead of 'Number.'
4. Click on the red numbers in the 'Negative Numbers' box, then click 'OK.' All negative numbers in your selected cells will now display in red.

How to Create a Fillable Order Form

Microsoft Excel 2010
1. Open Excel 2010 and select the 'File' tab. Click 'New' and type 'order form' in the search box. Hit the 'Enter' key on your keyboard. Excel displays the available templates.
2. Preview a template by clicking on one. The preview is displayed in the right task pane. Download the template by clicking it and selecting the 'Download' button. The template downloads to your computer.
3. Add your products, sales staff and logo to the form template. Add your name, address and date. Save your template by clicking the 'Save' icon on the Quick Access Toolbar.
Google Documents
4. Access the Google Documents website. Type 'Order Form' and press 'Enter.' Review the available templates that appear.
5. Preview the templates by clicking the 'Preview' button. Download the template by clicking the 'Use This Template' button.
6. Edit the newly downloaded template by adding your customized information. This includes your name, address, products, sales staff, logo and date.
OpenOffice Calc
7. Access the OpenOffice website. Type 'Order Form' and press 'Enter.' Review the available templates that appear.
8. Download an order template by clicking the 'Use This' button. The template downloads to your computer.
9. Double-click the template file to open it in OpenOffice Calc. Edit the newly downloaded template by adding your customized information. This includes your name, address, products, sales staff, logo, and date.

How to Take the Label 'Sum of' Out of a Pivot Table

1. Click on a cell in the PivotTable in the column from which you want to remove 'Sum of.'
2. Click the 'Options' tab on the toolbar, then click the 'Field Settings' button under the 'Active Field' area of the toolbar. A small window will pop up.
3. Place your cursor in the 'Custom Name' field and erase the 'Sum of' from the name.
4. Move your cursor to the end of what is left and add a space to the name. This is because once you remove the 'Sum of,' the remaining name is that same as a recognized field in the field list, and if you try to make a second field, Excel will give you an error. The extra space lets Excel differentiate between the two names, but they will look the same to anyone who views your PivotTable.

Monday, November 25, 2013

How to Use Excel's VARP Function

1. Learn the syntax of VARP. It is VARP(number1,number2,...) where number1, number2,... are up to 30 arguments which evaluate to numbers and comprise a complete population. VARP requires at least one argument.
2. Use arrays, names or references that contain numbers as arguments for VARP, in addition to pure numbers. Only numbers in an array or reference will be counted. Empty cells, error values, logical values or text in an array or reference will be ignored.
3. Enter logical values and text that represents numbers directly into the argument list. Error values or text that does not evaluate to a number will cause an error.
4. Ensure VARP is the correct function to use. VAR should be used instead of VARP if the data represents only a population sample. Use the VARPA function instead if you wish to include a reference that contains logical values or numbers represented by text in the calculation.
5. Calculate the VARP as the sum of (number - AVERAGE(number1,number2,...))^2/n where number is each value in the population and n is the number of values in the population. For A2 = 3.5, A3 = 5, A4 = 7.23 and A5 = 2.99, VARP(A2,A3,A4,A5) would return 2.71335.

Sunday, November 24, 2013

How to Extract Tables From Multiple Word Documents Import Them to Excel

1. Use Windows Explorer to copy a Word file containing at least one table to the folder 'C:\.' Rename the file as 'table.docx.' You'll write an introductory program that will read a table cell from this file.
2. Click the 'Developer' tab, then click the 'Visual Basic' button to enter the Visual Basic programming environment. Paste the following program into VB code window. This program creates a link to Word, which allows excel to use Word's virtual objects just as though the program were running inside a Word document. This process is called 'Automation.'Public Sub LoadWordTablebak()Dim pgmWord As Word.ApplicationSet pgmWord = CreateObject('Word.Application')pgmWord.Documents.Open ('c:\table.docx')MsgBox pgmWord.ActiveDocument.Tables(1).Cell(1, 1)pgmWord.ActiveDocument.ClosepgmWord.QuitEnd Sub
3. Click the 'Tools' menu, then click the 'References' command. Scroll through the window that appears to locate the 'Microsoft Word' item, then click the checkbox for that item. This action lets Excel access the visual objects in a Word document.
4. Click any statement in the program, then click the 'Run' menu's 'Run' command. The program will display a message box showing the contents of a table cell in a Word document. You'll now expand the program to load in a complete table from any Word document.
5. Paste the following revised program below the 'End Sub' statement of the original program. This program prompts the user for the filename of a Word document. The user must also type in the number of a table within the document. The program uses the automation method of the first program to load copy the specified table cell by cell into an Excel spreadsheet.Public Sub LoadWordTable2()Dim docname As StringDim TableId As IntegerDim c, r, startRow As IntegerDim curCellDim pgmWord As Word.ApplicationSet curCell = ActiveCellSet pgmWord = CreateObject('Word.Application')docname = InputBox('Enter Word document name')docname = InputBox('Enter Word document name')While (docname
'')TableId = InputBox('Enter table number')pgmWord.Documents.Open ('c:\table.docx')With pgmWord.ActiveDocument.Tables(TableId)startRow = ActiveCell.RowFor c = 1 To .Columns.CountFor r = 1 To .Rows.CountcurCell.Value = .Cell(r, c)'Move to next rowSet curCell = curCell.Offset(1, 0)Next r'Move to next columnSet curCell = Cells(startRow, curCell.Column 1)Next cEnd WithpgmWord.ActiveDocument.Closedocname = InputBox('Enter Word document name')WendpgmWord.QuitEnd Sub
6. Click the 'Excel' application icon in the Windows taskbar to return to Excel, then click the 'Macros' button of the 'Developer' tab. Double-click the 'LoadWordTable' macro to run that macro.
7. Type the name of a Word document containing a table when the prompt to do so appears. Type the number of the table you want when the prompt for that table appears. For example, if you want the second table in the document, type '2.' Excel will load the table into the current spreadsheet.
8. Continue loading Word tables with the program as needed. Press 'Enter' at the 'document name' prompt to terminate the program.

How to Create a Form in Microsoft Excel 2007

1. Launch Excel from the start menu or from the desktop. Click the small drop-down arrow at the very top of the screen, to the right of the Save, Undo, and Re-do buttons. In the menu that appears, choose 'More commands.' The Excel Options screen should appear.
2. In Excel Options, click the drop-down at the top of the left-hand list of commands, labeled 'Choose commands from.' In the list that drops down, choose 'All commands,' which is the third item down from the top. In the list box below the drop-down, a long list of commands will appear, sorted alphabetically. Locate the command named 'Forms,' click on it, and click the 'Add' button in the center of the screen. Click 'OK' to close the Excel Options screen, and the Forms button will appear next to the Re-do button at the top of the screen.
3. Open the spreadsheet with the rows that are to be viewed in forms. Click on a cell that is in the list of data the form is to be for. It can be a heading cell or any cell within the data. Click on the Forms button that was added in step 1. Excel will automatically create the form.

How to Paste Special HTML in Microsoft Excel 2003

1. Create the desired HTML that you wish to paste special into Excel with.
2. Copy the HTML code. You can easily copy the HTML by highlighting it all and pressing the hotkeys “CTRL-A” and then by pressing the hotkeys “CTRL-C.”
3. Access the 'Paste special' menu. To access this menu, you will need to scroll to the “Edit” tab and select “Paste Special.”
4. Set the paste special options. Under the paste special properties menu, in the “As” box, you should left-click on “HTML” to select the format.
5. Paste special the HTML into Excel. Now click on the “OK” button to paste special HTML into Excel.

How to Speed Up an ODC Connection in Excel 2007

1. Open the Microsoft Excel 2007 program on your computer and then click the 'Microsoft Office' button.
2. Click the 'Open' option and then select the Excel 2007 worksheet you want to speed up an ODC connection for. Click the 'Open' button.
3. Click the 'Data' tab from the top of the application and then click the 'Connections' option from the 'Connections' group.
4. Select the 'Usage' tab in the Workbook Connections dialog box. Click on each box in the 'Refresh control' section so that they're deselected.
5. Enter a lower number of records to retrieve in the 'Maximum number of records to retrieve' field.
6. Click the 'Definition' tab and then click the 'Parameter' button. You can then edit the parameters for your ODC query. For example, you can make your query as short as possible to speed up the connectivity.
7. Click the 'Apply' button to save all of your changes and then click the 'OK' button to close out of the dialog box.

Saturday, November 23, 2013

How to Insert a Picture or Logo into an Excel Document

1. Open the Excel document that you want to add the logo or picture to.
2. Click Insert and select Header Footer from the Text group.
3. Click inside the header or footer where you wish the picture to appear.
4. Click Picture under Header Footer Tools in the Header Footer Elements group on the Design tab.
5. Select your picture and click 'Insert.' The picture will appear where you clicked.

How to Delete Characters Over 40 Character Length in Excel

1. Click the 'Start' button on the taskbar, type 'Microsoft Excel' in the Search box and then press 'Enter.'
2. Press 'Ctrl O' and then choose the Excel document from which you want to delete characters over 40 characters in length on the dialog box that opens.
3. Type the formula '=LEFT(cell_number,40)' in the cell in which you want to delete characters over 40 characters in length. Replace, cell_number with the unique number of the cell, for example, A1.

How to Generate a Random Number in Excel

1. Open Microsoft Excel 2007, and start a new, blank spreadsheet or open an existing spreadsheet from your files into which you want to generate a random number.
2. Click the cell you want to generate a random number into so it is selected. The cell will have a thick black line outlining it.
3. Type '=RAND()' (no quotations) into the 'Formula' text box near the top of the Excel screen. This is the function that instructs Excel to enter a random number between 0 and 1 into the selected cell.
4. Press the 'Enter' key on your keyboard. The random number will be generated into the selected cell and you will be taken to the cell beneath it.
5. Continue the process of generating random numbers into your Excel cells using the same method as outlined above.

How to Fill the Cells With the Names of the Successive Months in Excel

1. Open a new Microsoft Excel 2010 spreadsheet.
2. Click on cell “A1” and type in the name of the first month you want to use. Excel can begin to fill successive months from any month, so you don’t necessarily need to have “January” be first. You can also add the year, if necessary. Excel will automatically add to the year as it adds each successive month. Press “Enter” when you are done entering the month.
3. Click on cell “A1” again, as Excel will change the selected cell once you pressed “Enter.” Move your mouse over the lower-right corner of the cell. Your pointer will change into a “ ” sign.
4. Click and hold the mouse button. Drag the mouse down the spreadsheet. As you move the mouse, a small pop-up window will display the month that will appear in the last selected cell, so you can tell how far you need to drag the mouse.
5. Release the mouse button when you have reached the last cell that you want to fill with month names. Excel will automatically add the names of the successive months to each cell, starting from the first month that you entered.

How to Calculate Weighted Averages for Grades in Excel

1. Type 'Grade' in cell A1, 'Weight' in B1 and 'Total Worth' in C1.
2. Type your grades in column A. For example, if you received a 95 and an 80, then type '95' in A2 and '80' in A3.
3. Type the weight each grade has in column B, next to its corresponding grade. In the example, if the first grade is worth 60% and the second grade is worth 40%, then type 60% in B2 and 40% in B3.
4. Type '=A2*B2' in cell C2. This weights the grades. Copy and paste this formula next to each grade.
5. Type '=sum(' in column C after your last grade, then highlight all the cells that have a number in coumn C and hit the 'Enter' key. The result that appears is your weighted average.

How to Use the AutoFill Function in Excel 2003

1. Open Excel.
2. Open your worksheet by clicking 'File > Open.'
3. If you don't have an existing worksheet, create a new one. Go to 'File > New.'
4. Enter your information. The AutoFill function will be able to fill in information in a series. When making a calendar, for example, you can type 'January' into the first box and use AutoFill to put the subsequent months in the next cells.
5. Place your mouse over the bottom right hand corner of the cell with 'January' (or whatever the first cell in your series is). When you see a small 'x' appear where your mouse arrow was, click and drag the 'x' to the cell where your series will end. The rest of the series should be filled in for you.

How to Reduce the Size of an Excel Workbook

1. Delete the extra default worksheets in a new Excel workbook. Each new workbook that Excel creates has three worksheets (Sheet1, Sheet2 and Sheet3). These sheets require additional memory and space on the hard drive when you save your workbook. Right click on the name tab (at the bottom of the workbook) for the second worksheet. Then choose Delete from the pop-up context menu. Repeat these steps to delete the third worksheet.
2. Reduce the size of graphics, line art or smart art that you have inserted or imported into the workbook. Graphics quickly increase the size of every saved Excel file. Click on any embedded graphic object to activate the Picture Tools menu. Compress, crop or decrease the color saturation and resolution of jpeg, gif, bmp, tiff and png files. You can also use an external graphics program to resize clipart and other file formats, and then insert the smaller sized object into your workbook.
3. Use TrueType fonts as the default font for your Excel worksheet labels. Custom fonts require additional memory allocations. Excel stores the instructions for your printer in the workbook. Therefore, your file size will increase with each custom font.
4. Apply text formats such as bold, italics and colors to individual cells only. If you select a row or column when adding formats, Excel applies the formatting to all cells in the selection. Since Excel has more than 200 columns and thousands of rows, the formatting will use Random Access Memory to manage the formats for the rows and columns.
5. Eliminate unnecessary comments, text boxes and other enhancements such as watermarks, cell shading and borders. All of them require additional space when you save the file. Headers, footers, page numbers, charts, hyperlinks, tables and range names in formulas also increase the size of an Excel workbook. Printing options such as gridline displays and repeated row and column headings can also be eliminated to reduce the size of your workbook.
6. Save your workbook in PDF format. This option is included in Excel 2007; most earlier versions require an add-in that you must download. Choose Save As in the File menu in Excel versions through 2003. It is under the Microsoft Office Button menu (left of Home) in Excel 2007. Select PDF from the menu. Then select the Optimize for minimum size radio button in the file Save dialog box. This creates a smaller PDF file that is ready to email or publish online.

How to Use a Data Form in Microsoft Excel 2007

1. Open the Microsoft Excel 2007 program on your computer and then click on any cell where you want the form to start.
2. Click on the arrow next to the Quick Access toolbar and then click on the 'More Commands' option. Select 'All Commands' and then click on the 'Form' button from the list.
3. Click on the 'Add' button and then 'OK.' Click 'Form' from the Quick Access toolbar, and then you can enter your data into the first row of the form.
4. Click on the 'New' button to add a new row and then enter your data. Press the 'Enter' key on your keyboard, and the row of data will be added to the bottom of the form.
5. Select any row that you want to remove and then click on the 'Delete' button. Choose 'Close' at any time to close out of the data form and return to your worksheet.

How to Find Duplicate Entries on an Excel Spreadsheet

1. Select the first cell in your column of data. This cell will serve as a template for the conditional statement that searches for duplicates in the file. After the cell is selected, click the 'Format' menu item and select 'Conditional Formatting.' This opens a new dialog window.
2. Select 'Formula Is' in the new dialog window. A text box displays, prompting you for an Excel formula. Enter the following code into the text box:=COUNTIF (A:A,A1) > 1The 'countif' function searches through the 'A' column and finds any duplicates (the formula assumes that A1 is your first cell. If your data begins at A2, replace 'A1' with 'A2' in the formula. Replace each 'A' with a 'B' to check column B, etc.).
3. Click the 'Format' button in the 'Conditional Formatting' dialog box. This opens a window prompting you for a color selection. Click a color you want to use as the highlight color for the duplicates. The 'countif' function finds the duplicates, and the format color is used to show you which cells are found. Click 'OK.'
4. With your cursor in the first data cell, click 'Copy' from the Edit menu. Press 'Ctrl spacebar' then click 'Paste Special' from the Edit menu. Select 'Formats' from the Paste Special dialog, then click 'OK' to copy the conditional formatting through the entire column.
5. Scroll down the column of data to find each duplicate. The duplicates are highlighted in the color you chose. You can either delete these duplicates, exclude them from future formulas or move them to another area of the spreadsheet.

How to Use Excel's DSUM Function

1. Learn the syntax of DSUM (database, field, criteria). Database is a cell range that specifies the database, field is a column in the database from which DSUM will add values and criteria is a cell range with the criteria to be used in selecting the values.
2. Define the database for DSUM. The first cell in the range will indicate the upper left corner of the database array and the second cell will indicate the lower right corner of the array. The first row of the array must contain the column names.
3. Specify the field for DSUM. It can include the column name enclosed in double quotation marks or a number representing the column's position in the database where 1 is the first column, 2 is the second column and so on.
4. Supply the criteria for DSUM. This cell range must contain at least one column name and one condition for that column. The condition will generally be of the form ='condition.' Thus, a value of ='=Apple' would select rows where the value in the specified column is equal to 'Apple.'
5. Enter the following in the first four columns of an Excel spreadsheet:Row 1: Tree, Height, Age and Yield;
Row 2: ='=Apple' and ='>10' (nothing in columns three and four);
Row 3: ='=Pear' (nothing in columns two, three and four);
Row 4: Tree, Height, Age and Yield;
Row 5: Apple, 18, 20 and 14;
Row 6: Pear, 12, 12 and 10;
Row 7: Cherry, 13, 14 and 9;
Row 8: Apple, 14, 15 and 10;
Row 9: Pear, 9, 8 and 8; and
Row 10: Apple, 8, 9 and 6.
6. Interpret the formula =DSUM(A4:D10,'Yield',A1:B2) as follows: The database is defined as the array A4:D10. Note the column names in the first row of this array. 'Yield' is the column name from which the values will be taken. The criteria are given by A1:B2 which indicates that the column named Tree must contain 'Apple' and the column named Height must contain a value greater than 10. DSUM(A4:D10,'Yield',A1:B2) will therefore be evaluated as SUM(14,10)=24.

Friday, November 22, 2013

How to Convert Columns of Text to Rows

1. Click the letter at the top of a column to select a column. Hold 'Ctrl' and click on more column headers to choose several columns.
2. Press 'Ctrl' and 'X' to cut the selected columns.
3. Right-click inside the first cell into which you want to paste the data as rows.
4. Select 'Paste Special' from the drop-down menu.
5. Select the 'Transpose' check box in the 'Paste Special' dialog box.
6. Click 'OK.' The columns will now appear as rows.

How to Share Password Protect Track Changes in Excel

Password Protection for Specific Cells
1. Determine the level of protection needed for your spreadsheet. If there are specific cells you do not want changed but others that can be, you can just lock cells. If the spreadsheet needs to be fully locked, or you want to prevent users from accessing the actual file, then file-level protection is appropriate.
2. Click 'Home' on the ribbon after opening a file in Excel.
3. Navigate to the Cells section, click 'format,' and then click 'Lock Cell.'
Password Protection for Workbooks
4. On the ribbon click 'Review.'
5. Navigate to the changes group and click 'Protect Workbook.' This will bring up a pop-up window.
6. Select 'Structure' or 'Windows' from the pop-up window. If you lock the structure, users will not be able to reorder worksheets. If you lock windows, users will not be able to change worksheet window sizes.
7. Choose and write down a password to lock the workbook. This is optional, but enhances the security of your worksheet.
Tracking Changes
8. On the ribbon click 'Review.'
9. Navigate to the changes group and click on 'Share Workbook.' This will bring up a pop-up window.
10. Select the check box next to 'allow changes by more than one user at the same time.' Then click on the 'Advanced' tab and type in the number of days you want Excel to keep the change history. Click 'OK.' Note that Excel defaults to 30 days of change history. This is sufficient for most users.
11. Navigate to the changes group and click on 'Track Changes.' Then select 'Highlight Changes.' This will highlight any changes made to a cell so that you will be able to review it later.

How to Do Stacked Charts in Excel

1. Enter the data for the stacked chart into an Excel spreadsheet. Clearly label the data in the first row so that it is easier to identify when you are making the chart.
2.
Highlighting the data tells Excel which data to include in the chart.
Highlight the data you want to include in the stacked chart. Include the data labels (typically placed in the first row). You can highlight by clicking your left mouse button, holding it down, and dragging to include all pieces of data. Alternatively, to include the entire spreadsheet in the chart, press 'Ctrl-A' to highlight everything.
3. Open the chart interface. In Excel 2007, click the 'Insert' tab, then click the down arrow under Column, and click 'All Chart Types.' In Excel 2010, click the 'Insert' tab and click 'Chart.'
4. Choose the chart type by clicking or scrolling to the corresponding section. The most common type of stacked chart is column, but you may also choose bar.
5. Choose stacked chart style by clicking the corresponding stacked style under the chart type. Verify that the style you have chosen is stacked by hovering over it and looking for 'stacked' in the tool tip description. Choose a 100 percent stacked style if you want all bars to be full and display the composition breakdown. Choose the style that most clearly displays your data and fits your taste.
6. Press 'OK' and verify the data.

Thursday, November 21, 2013

How to Audit Formulas in Excel 2007

1. Open an existing Microsoft Excel document to audit. Double click on the 'My computer' icon on the operating system desktop. Then locate the existing Excel document and double click on that file.
2. Select the 'Microsoft Office Main Menu Button' located in the top right hand corner of Microsoft Excel. From that menu click on the 'Excel Options' button located at the bottom of the menu dialogue box.
3. Click on the 'Advanced' options button on the Excel options dialogue box.
4. Check all the options under the 'Display Options for This Workbook' section. Also, make sure that the 'For Object: Show' option choose 'All.'
5. Show all the formulas in an Excel worksheet by selecting all the cells. Do this by clicking on the top left hand corner of the worksheet. Once they are all selected click on the 'Formulas' tab and choose 'Show All Formulas' from the formula auditing section of the formulas tab.
6. Audit the formulas by choosing the 'Error Checking' button in the formula auditing section of formula ribbon. A user can also trace dependents and precedents by choosing the 'Trace Dependents' and 'Trace Precedents' option from the very same section of the formula auditing section of the formulas ribbon.

How to Subtract Dates in Excel 2007

1. Open Excel 2007 and enter the current date in cell A1. Enter another date in cell B1.
2. Highlight cells A1 and B1. Click the 'Home' tab and locate the 'Numbers' group. Change the number format to 'Short Date.'
3. Click cell C1 and enter a subtraction formula. Enter 'A1-B1.' Press the 'Enter' key to see the results.

Wednesday, November 20, 2013

How to Create a Toggle Switch in Excel 2003

1. Open Microsoft Excel 2003. Click the 'Microsoft' button on the top left corner of the window. Click the 'Excel Options' button in the lower right corner of the dropdown menu.
2. Click the 'Popular' tab. On the box to the right, check the 'Show Developers tab in the Ribbon' box. Click the 'OK' button on the bottom right of the window.
3. Click the 'Insert' button in the top center of the window. Click on the 'Toggle' button on the bottom right corner of the dropdown menu.
4. Click the left mouse button anywhere on the screen. The toggle button appears. Click and hold the left mouse button on the 'Toggle' button to move it. Your toggle switch is now created.

How to Merge Two Macros in Excel

1. Create a master macro by clicking on the 'View' tab in the toolbar. Click on 'Macros' and select 'View Macros.' Type the name of the master macro, such as 'Master,' in the 'Macro name' box. Click on the 'Create' button to launch the Visual Basic editor.
2. Place the cursor in the second line under the subroutine. For example, if the name of the macro you created is 'Master,' the first line is 'Sub Master().' Under this line, type 'Call,' followed by the name of the first macro you would like to run. For example, if you created a macro called 'GetWebQueries' that loops through a list of URLs, opening each one and copying and pasting the data from a table into a new worksheet, you would type 'Call GetWebQueries().'
3. Press 'Enter' to go to the next line. Type 'Call,' followed by the name of the second macro, such as 'Call Format(),' which would run the Format macro that formats each worksheet in a specified manner. Save the macro and close the Visual Basic editor.
4. Run the master macro by clicking on the 'View' tab. Click on 'Macros' and select 'View Macros.' Select the master macro and click on the 'Run' button. The master macro will run 'GetWebQueries' and the 'Format' macros in the specified order.

How to Filter Excel for Duplicate Names

1. Open your Excel spreadsheet.
2. Select one or more columns in which you suspect there are duplicate values.
3. Click on the 'Data' tab at the top of the screen, then the 'Remove Duplicates' icon.
4. Check the boxes with suspected duplicates in the dialog box that opens.
5. Click 'OK.' Another box will appear, telling you how many duplicates have been removed and how many unique values remain.

Tuesday, November 19, 2013

How to Remove All the Blank Rows in Excel 2007

1. Open Excel 2007 and select a workbook. Select the 'Office' button and click 'Open.' Search your network for the workbook. Click the workbook and select 'Open.' The workbook opens.
2. Select the first column header in your workbook. Select the 'Home' tab and click 'Filter' from the 'Sort Filter' button in the 'Editing' group. Notice the dropdown icons that appear in your column headers.
3. Select a column header that may contain blank data. Click the dropdown icon. Scroll down in the list and locate 'Blanks.' Remove the check. The blank rows from this column are removed.
4. Repeat this step for each column that may contain blank data. Once you have done this for each affected column, all of the blank rows will be removed from your Excel 2007 workbook.

How Do I Count Distinct Records in an Excel Pivot Table?

1. Add a column to the database with which you are working; give it a text heading of your choice and add this field to your pivot table.
2. Enter the following formula in the first cell of the column you created:=IF($A$2:$A2=A2)>1,0,1)where A is the column which you are searching and 2 is the first row of data in your pivot table.
3. Copy the formula down the entire column you created and the cells in that column will display the number of distinct records in that row.

How to Combine Contents of Two Rows of Cells in Excel 2007

1. Click the 'Start' button from the desktop on your computer, and then click the 'All Programs' option. Select the 'Microsoft Excel 2007' application from the list of programs.
2. Click the 'Microsoft Office' button from the top right corner of the program followed by the 'Open' option. Select the Excel 2007 worksheet file you want to combine cells with, and then click the 'Open' button.
3. Select the two rows of cells that you want to combine with your mouse. The cells should be highlighted.
4. Select the 'Home' tab, and then click the 'Merge and Center' option from the 'Alignment' group. The cells will then be merged together.
5. Click the 'Merge and Center' button again at any time to split the merged cells if you no longer want the contents to be combined.

How to Hide/Unhide in Excel 2007

1. Log on to your computer and open Microsoft Excel 2007. Open the spreadsheet.
2. Select a cell within the row or column you wish to hide. Click on the 'Cells' group and choose 'Format'.
3. Go to the 'Visibility' section and choose 'Hide' or 'Unhide' from the menu.

Monday, November 18, 2013

How to Make a PERT Chart in Excel

1. Launch Microsoft Excel 2010.
2. Click 'Insert' at the top of the window.
3. Click the 'Shapes' drop-down menu in the Illustrations section of the ribbon. Select your desired shape from the Flowchart section.
4. Click your mouse at the location in the spreadsheet where you wish to insert the object, and then drag the mouse until the outline of the shape is the desired size.
5. Click the 'Text Box' option in the Insert Shapes section of the ribbon. Click inside the shape you just created and use your mouse to draw a text box inside the shape. Note that you can also click a color for the shape from the Shape Styles section of the ribbon.
6. Type the necessary text for the object.
7. Insert additional shapes from the Shapes drop-down menu on the Insert tab, and then add text boxes to each shape as needed.
8. Click your desired connector arrow from the Insert Shapes section of the ribbon.
9. Click the shape that will serve as the anchor for the arrow, and then drag the arrow to the target shape for the arrow.
10. Add additional arrows to your chart as needed.
11. Add outlying text to your chart by clicking the 'Insert' tab at the top of the window and then clicking the 'Text Box' button in the Text section of the ribbon.
12. Click in the desired location on your spreadsheet for the text box, and then drag the mouse until your desired text box shape is displayed.
13. Type your text into the text box.
14. Add additional text boxes to your chart until the chart is complete.

How to Create a Microsoft Calendar

1. Open Microsoft PowerPoint, Word, or Excel. In PowerPoint, Word or Excel 2010, select 'File' and 'New.' In PowerPoint, Word or Excel 2007, click the Microsoft Office button on the ribbon and select 'New.'
2. Select the 'Calendars' heading under Office Online templates to find a template type. A list of calendar templates will appear. The calendar templates in PowerPoint and Excel 2010 and 2007 include calendars from 2007 to 2010, academic calendars and even an Advent calendar. Word 2010's templates also include 2011 calendars.
3. Click on the calendar type you want and, within the calendar types, select a specific template design by double clicking it to begin the download. The download will automatically begin without leaving the application.
4. Customize your calendar. In PowerPoint you can change colors and font themes by going to the 'Design' tab and selecting a different design template. In Word and Excel, theme changes are made from the 'Page Layout' tab. The new design theme will apply to the entire calendar.
5. Add in text and graphics. Insert birthdays, social gatherings and appointments by positioning your cursor to the appropriate day and start typing. You can select the 'Insert' tab to place photos, pictures or clip art in your calendar. All graphics can be moved and resized as you desire. Customization can be made from month-to-month and printed as 'current page only' for month by month printing or in its entirety as a wall calendar.

How to Import Excel Into MS Word

1. Select the data in Excel you want to import into Word. Press 'Ctrl-C' to copy the data. You can also right-click on the selected data and click 'Copy' from the context menu.
2. Select the location in your Word document to enter the copied data. Click the 'Paste' drop-down menu and select 'Paste Special.'
3. Select the 'Paste link' radio dial, choose 'Microsoft Excel Worksheet Object' from the list and click 'OK.'
4. Adjust the formatting of the new table to meet your needs.

How to Link Bloomberg to Excel

1. Close Excel. Download the Bloomberg Excel add-in (www.bloomberg.com). Click the 'Download' link, which will open a separate window. Click the 'Run' button.
2. Install the Bloomberg Excel add-in. Click the 'Start' button and select the 'All Programs' option. Click 'Bloomberg' and select 'Install Excel Add-In,' which will open a separate window.
3. Click the 'Install' button and then close the window when the installation process has completed. Open Excel to view the 'Bloomberg' tab on the menu bar.

How to Use Data Analysis in MS Excel

Excel 2007
1. Open Excel and navigate to a blank or existing spreadsheet file. Locate the Data Analysis tool by clicking the 'Microsoft Office' button, located in the top left-hand corner of your open spreadsheet. This opens a menu with a series of commands. Click the “Excel Options” button at the bottom of this menu.
2. Choose the “Add-ins” option, located in the column on the left side of the Excel Options menu. This will display a set of available add-ins that can be installed in your Excel program. Click the “Analysis ToolPak” add in, which often appears first on the list of add-ins. After selecting this add-in, click “OK.” The Data Analysis tool should appear in your Excel 2007 tool bar, located at the top of an Excel workbook file.
3. Select the Data Analysis tool to analyze a set of data in Excel by clicking the tool in your toolbar. This will open a smaller window that displays a set of statistical procedures and analysis tools.
4. Select the analytical procedure you want by clicking on it, then choosing “OK.” This will take you to the window for that procedure. Here you must specify the range of data you want analyzed by clicking and dragging across the cells, rows or columns that contain the data.
5. Indicate whether you want your results displayed on the same worksheet, in a new worksheet or in a new workbook by choosing the appropriate option. Click “OK.” Excel will then run the analysis and display the results in the manner you selected.
Earlier Versions of Excel
6. Unlock and install the data analysis tool by clicking the “Tools” menu, found in the toolbar at the top of an open Excel workbook file. This will display a drop-down menu of available options.
7. Select “Add-ins” from the Tools menu. This opens a small menu that displays the add-ins that can be installed for use. Choose 'Analysis ToolPak' by clicking the check box next to it, then click “OK.” The tool “Data Analysis” should then appear in the Tools drop-down menu, ready for use.
8. Select the Data Analysis tool to analyze a set of data in Excel by clicking the tool in your toolbar. This will open a smaller window that displays a set of statistical procedures and analysis tools.
9. Select the analytical procedure you want by clicking on it, then choosing “OK.” This will take you to the window for that procedure. Here you must specify the range of data you want analyzed by clicking and dragging across the cells, rows or columns that contain the data.
10. Indicate whether you want your results displayed on the same worksheet, in a new worksheet or in a new workbook by choosing the appropriate option. Click “OK.” Excel will then run the analysis and display the results in the manner you selected.

Subscribe to: Posts (Atom)