October 2012 - Excel

Browse » Home » Archives for October 2012

Sunday, October 28, 2012

How to Insert a PDF File Into Excel 2003

1. Open the Excel file that you want to place the PDF into.
2. Click on the cell you want to insert the PDF on. The upper left corner of the PDF will align against the upper-left corner of the cell you have selected, although you can move it later if you want.
3. Click the 'Insert' menu from the top the Excel window.
4. Select 'Object.'
5. Click on the 'Adobe Acrobat Document' in the 'Object type' section and then click the 'OK' button.
6. Navigate to the PDF that you want to insert.
7. Click the PDF and then click the 'Open' button. Your computer will open the PDF in Adobe Reader and insert it into your spreadsheet.

How to Use the Range.sort Method in VBA

1. Open the Excel 2010 spreadsheet that you want to sort using VBA. Press 'Alt' and 'F11' simultaneously to bring up the VBA editor.
2. Double-click on the module, which is located on the left side of the editor, where you are writing your VBA code. Your code will appear on the right side of the screen. Place your cursor into an empty line of code where you want to use the Sort method.
3. Enter the following code into the VBA editor, without quotes: 'Range('A:B').Sort key1:=Range('A1'), Order1:=xlAscending'. Change 'A:B' to the Excel range that you want to sort and change 'A1' to the first cell in the column you want to use to sort by. Change 'xlAscending' to 'xlDescending' if you want to sort from high to low, instead of low to high.
4. Place a comma and a space at the end of your code if you want to add more parameters to the sort method. Enter the name of the parameter, followed by ':=' and then the value for the parameter. Place another comma and a space after each parameter, except for the last one.
5. Use the 'Key 2,' 'Order 2,' 'Key 3' and 'Order 3' parameters to set up additional sorting rules for when the first key find identical values. Use the 'Type' parameter to determine whether to sort the range by labels or values, and use 'xlSortLabels' and 'xlSortValues,' respectively. Use the 'Header' parameter, set as either 'xlYes,' 'xlNo' or 'xlGuess' to let the sort function know if your data has headers or not.Use the OrderCustom parameter, using an integer as the value, to use a preset custom sort order that you created. Use the 'MatchCase' parameter, setting it to either 'True' or 'False' to determine whether to make the sort case sensitive. Use the 'Orientation' parameter, set as either 'xlSortRows' or 'xlSortColumns' to determine how your data table is orientated. Use the 'SortMethod' parameter, set as 'xlStroke' or 'xlPinYin' if you are sorting Chinese characters. Finally, use the 'DataOption1,' 'DataOption2,' or 'DataOption3' parameters, set as 'xlSortTextAsNumbers' or 'xlSortNormal' to determine whether the respective key will have its text be treated as numbers for the sort.

How to Reverse the Order of a Chart in Excel 2003

1. Click the axis for the categories, series or values that you want to change the order of.
2. Click the 'Format' tab, click 'Selected Axis' and then click the 'Scale' tab.
3. Check 'Categories in Reverse Order,' 'Values in Reverse Order' or 'Series in Reverse Order.'

How to Convert Quattro Pro Files to Word Documents

Converting Quattro Pro with Microsoft Excel
1. Go to “Start->Settings->Control Panel” (or “Start->Control Panel” in Windows XP).
2. Choose “Add/Remove Programs,” highlight “Microsoft Office” or “Microsoft Excel,” and click “Add/Remove.”
3. Click on “Add/Remove Features,” locate and run “Quattro Pro Converter 5.0” to install the converter, and click “Update Now” to complete.
4. Open Microsoft Excel. Go to “File->Open” and select the Quattro Pro file you wish to convert.
5. Go to “File->Save As,” select “Microsoft Excel (*.xls)” under “Save as type” and click “Save.”
Converting Quattro Pro with Media Convert
6. Open the Media Convert website.
7. Browse for and select the Quattro Pro (WB2) file you wish to convert.
8. Select “Microsoft Excel (.xls)” under “Output Format” and click “OK” to convert the file.
Pasting Excel into Microsoft Word
9. Open a new Microsoft Word document. Copy and paste the contents of your converted Excel file.
10. Click the “Paste Options” icon next to the data. Choose “Match Destination Table Style” or “Keep Source Formatting” to keep the data as tables, “Keep Text Only” to reformat the data into paragraphs of text or “Paste as Picture” (Microsoft Word 2007 only).
11. Save the document in Microsoft Word (DOC) format.
Converting Excel to Word
12. Download and install Total Excel Converter. Run the program.
13. Locate the file you wish to convert and highlight it.
14. Click “DOC” under “Convert to” to save a copy of the file in DOC format.

Saturday, October 27, 2012

How to Put Small Caps in MS Excel Vista

1. Open Microsoft Excel. Choose 'View' from the menu bar and click 'Toolbars' from the drop-down menu. Select 'Formatting' to view the formatting toolbar. There should be a check mark on its left side to indicate that the toolbar is on, and it should appear below the standard toolbar, which you will recognize by 'Print' and 'Save' icons among many others.
2. Activate the cell or cells that you would like to apply the small caps effect. Write all of the text that you would like to have in small caps in uppercase, or capitalized, letters.
3. Highlight the letters that you would like to apply the small caps effect to (the letters following the first capitalized letter in each word or words). Decrease the font size for those letters by accessing the font formatting tools from the formatting toolbar. Repeat to apply this effect to all of the text that you would like to see appear as small caps.

How to Use Variables in Excel

1. Select the cell with the result that you want to declare as a variable.
2. Click on the Formulas menu item, and choose Names (Excel 2003 and earlier) or Name Manager (Excel 2007 and Excel 2010).
3. Enter the name of the variable in the dialog that comes up. From this point on, in this workbook, you'll be able to refer to the contents of this cell by entering its name.

How to Flow a List Across Multiple Columns

1. Choose the two columns you want to merge. You can also merge several columns on the spreadsheet. Once selected, click the first column and drag the mouse across the others. This will highlight multiple columns at once.
2. Click the 'Merge and Center' button in Microsoft Excel 2007. In Excel 2003, click the 'Format' menu item and select 'Merge and Center' from the list of options.
3. Select 'Merge Across' from the menu list in Excel 2007. In Excel 2003, select 'Merge.' You will notice that the cell lines across the columns disappear. This indicates that the columns have been merged.
4. Choose the alignment of the text in the columns. Click the 'Center' button to center the list within the selected merged columns. You can also left align or right align the text in the merge cells.
5. Click the 'Ctrl' and the 'S' button on the keyboard. This saves the Excel spreadsheet in both 2003 and 2007 versions.

How to Create Excel 2007 Custom Properties

1. Start Excel by clicking on the icon labeled Microsoft Office Excel 2007. It may be located on your start menu, quick launch bar or desktop.
2. Click on the menu item labeled 'File' with your left mouse button, then select the line labeled 'Properties' to bring up the file properties screen. Select the Custom tab to display the Custom panel. Enter a name for the custom property or select one from the drop-down box labeled 'Name.'
3. Look at the 2 buttons on the right. They are grayed out, which means that they are not functional at this point because you have not entered a value for the property. These buttons will be labeled 'Add' or 'Delete' if the selected property does not have a value. Otherwise, they will be labeled 'Modify' and 'Delete.'
4. Select a data type from the drop-down list labeled 'Type.' Your data may be text, a date, number or logical (yes or no). Enter a value in the box labeled 'Value.' Your data must match the data type you selected. For example, if you selected a Type of Number, then Value must be numeric.
5. Notice that the buttons labeled Add and Delete or Modify and Delete are now active. You may now Add, Modify or Delete the specified value. For a new value, you would click the 'Add' button.

Friday, October 26, 2012

How to Add Borders to Cells in Microsoft Excel 2003

1. Select the cell that you want to add borders to. You will first need to select and activate the cell that you wish to add a border to. Do this by left-clicking on the desired cell.
2. Select the border type. Adding a border is very easy to do. Once you have selected the cell that you wish to add a border to, right-click on it and select “Format Cell.”
3. Add the border. A cell formatting properties box will open. Left-click on the “Border” tab. There are several different types of borders that you can add, which are displayed as icons in the “Borders” tab. You can outline the cell, create side borders or create top and bottom borders. Select the desired border by left-clicking the corresponding border icon.
4. Save your spreadsheet file. Once you have made the desired changes, make sure to save your updated spreadsheet. You can do this by clicking on the “File” tab on the command bar and selecting “Save.”

Thursday, October 25, 2012

How to Remove a Password Protected Cell

1. Open Microsoft Excel. Open the Excel spreadsheet file from within Excel.
2. Find the security options in Excel. It will most likely be in the 'Tool' menu under 'Options.' Look under the 'General' tab and the 'Security' tab if there is one. Look for password settings.
3. In the password protect field select the asterisks and then the delete button. There may be a second password field for modifications, if so delete that password as well. You may be required to enter the password before you can delete the old password.
4. Exit out of the menu choices. Select the cell that was password protected and then delete the cell.

How to Create a Balance Sheet in Excel

Find Template Through Excel
1. Open Excel and click 'New' from the 'File' tab at the top of the screen, then scroll through the 'Templates' list at the left of the screen to view templates that came pre-loaded with your program and available on the Office website.
2. Click 'I Accept' on the right hand side of the page to accept the terms and conditions, if necessary. Browse under the 'Budget,' 'Expense Reports' and 'forms' categories to locate different kinds of balance sheet templates.
3. Click 'Download' to download and open a desired template in Excel.
4. Fill out your balance sheet.
Find Template Online
5. Visit the Microsoft Office Templates site, and enter 'balance sheet' in the text box.
6. Choose 'Excel' from the product drop-down and then click 'Search.'
7. Browse templates until you find one that fits your requirements for a balance sheet, and click 'Download.'
8. Validate your version of Excel, if prompted to do so, then indicate where to save the template (your desktop, hard drive or a memory device), then click 'Save.'
9. Open Excel, click 'Open' under the Office button, navigate to where you saved the template, and click 'Open.' Fill out your balance sheet.

How to Delete a Page in MS Excel 2007

1. Click the 'Worksheet' tab at the bottom of the Excel window that you want to delete. For example, click on 'Sheet 2.'
2. Click on the 'Home' tab in the 'Office' ribbon.
3. Click on the down arrow next to the 'Delete' icon in the 'Cells' group.
4. Scroll down in the drop-down menu and click on 'Delete Sheet.'

How to Create Graphs Embed in Spreadsheets

1. Open the saved Excel worksheet.
2. Click and drag to select the range of data for the chart. For example, select the categories and the values to appear on the chart.
3. Click the 'Insert' tab on the command ribbon.
4. Click the preferred 'Chart' button on the 'Charts' group. Click the window launcher arrow in the group's bottom right corner to open a more extensive chart list. A chart appears over the worksheet. The 'Chart Tools' ribbon appears with additional format options.
5. Click the preferred formats on the various ribbon tabs, such as 'Design,' 'Layout' or 'Format.' Create a custom look by changing the color and font for the plot area and the chart area, for example.
6. Save this Excel file.

Wednesday, October 24, 2012

How to Prevent Julian Dates in Excel 2003

1. Click on the cell with the Julian date.
2. Type '=RIGHT(YEAR(A1' into the 'Find what?' text box.
3. Search for the Excel formula that converts standard dates to Julian dates. The full formula is '=RIGHT(YEAR(A1),2)TEXT(A1-DATE(YEAR(A1),1,0),'000')' where 'A1' is the cell location of the Julian date.
4. Click on the cell with the formula, then press the 'Delete' key to remove the formula.

Tuesday, October 23, 2012

How to Change the Orientation of a Worksheet to Landscape in Microsoft Excel

1. Connect your computer to a printer if you have not set up a printer yet for your computer. You do not need an active printer connection to change the page orientation, but you do need a printer setting entered into the computer. Otherwise, Excel will gray out the page orientation option because it only applies to printing. Connecting a printer via USB will automatically launch the setup wizard. Follow the onscreen instructions to install the printer driver until the setup is complete.
2. Launch Excel and open your workbook to the worksheet you want in landscape mode.
3. Close out of any cells you are editing by pressing 'Enter' or clicking in another cell to save your changes, or press 'Esc' to exit the cell without change.
4. Click the 'Page layout' tab in the Office Ribbon, and then choose 'Orientation' from the 'Page setup' area.
5. Select 'Landscape' to change your page orientation. This will close the menu automatically and make the change.

How to Calculate Percentages in Excel 2007

1. Open Excel, and in cell A1 type the numerator. In cell B1 type the denominator. For example, for 75 percent, in A1 type '3' and in B1 type '4.'
2. Type '=a1/b1' in cell C1. In the example, C1 will result in 0.75.
3. Right-click cell C1, then click 'Format Cells.' Under the 'Number' tab, click on 'Percentage.' Choose how many decimal places you want to take the percentage to. Hit OK. The number is now formatted as a percentage.

Monday, October 22, 2012

How to Format the Toolbar Font Drop Down Menu in Excel 2007

1. Open Microsoft Excel 2007 on your computer screen.
2. Click on the circular button at the top of the screen that contains the 'Office' logo.
3. Click the 'Excel Options' button followed by the 'Customize' menu option.
4. Use the 'Customize' menu on-screen to make the necessary changes to both your main toolbar as well as to the font drop down-menu in Excel 2007. If you want to remove something from either menu, click on its name and click 'Remove.' To add a command to either menu, click on its name and click 'Add.'
5. Click 'OK' to save the changes you've just made to your Microsoft Excel 2007 toolbar and font drop-down menu.

How to Find Hidden Macros in an Excel Spreadsheet

Finding Macros with the Macro Tools in Excel
1. Open Excel normally.
2. Click on the 'Developer Tab.'
3. Click on the button labeled 'Macros.' A dialog box will pop up. The dialog box will have all the Macros available on all open workbooks. You can select 'Macros' there and delete them.
Finding Macros with the Visual Basic Editor
4. Open Excel normally.
5. Click on the 'Developer Tab.'
6. Click on the button labeled 'Visual Basic.' The Visual Basic editor will pop up. On the left hand pane, there will be a list of every single Macro that Excel has loaded; this will include Macros that are hidden from the Macros dialog box. You can select 'Macros' here and delete them individually, or look over their code.

How to Update Microsoft Excel

1. Use the update feature in Excel. Click the 'Office' logo in the top left corner. Click 'Options.' Click the 'Resources' tab. Click 'Check For Updates' under the 'Get Updates' title. The program will automatically search for updates and download them.
2. Use Microsoft Windows Updater. Click the 'Start Menu,' and type 'Check For Updates' in the 'Start Search' bar. Hit 'Enter' on the keyboard. Windows updater will open. Click 'Download Updated.' Windows Updater will download updates for Excel, Windows and all other Microsoft programs.
3. Upgrade Excel. Excel will only update the version of the software that is installed, not upgrade to a new version. Updating to a new version is considered an upgrade. For example, an update will not turn Excel 2007 into Excel 2010; this requires an upgrade.

How to Open a Protected Excel 2003 in Excel 2007

1. Click “Start,” select “All Programs,” open the “Microsoft Office” folder and then click “Microsoft Excel” to open Excel 2007.
2. Click “File” and then click “Open” to access the files on your computer.
3. Browse through the files on your computer for the password protected Excel 2003 worksheet you want to open in Excel 2007.
4. Select the file in question and click “Open” to open the worksheet.
5. Type in the password for the protected worksheet and then click “OK' to access the file. If you don’t know the password, you’ll have to ask the creator of the file for it or download a password remover from the Internet. Examples of password removing software include Excel Password, Excel Key and Excel Password Cracker (see Resources).
6. Click “File,” “Save As,” select the file type 'Excel 97-2003' and then click “Save.” Saving the file in this way will allow the file to be opened in older editions of Excel as well as the newer editions of Excel.

How to Create a Legend for a Spreadsheet

Chart Legend
1. Create a chart from existing data within a Microsoft Excel spreadsheet. Note that legends aren’t recommended for use with pie charts. Labels should be used instead.
2. Go to “Insert” and select “Chart.”
3. Select your chart type. Press “Next.”
4. Select your data range. Either enter the range of cells manually or press the small button to the right of the range box. Highlight the cells you wish to include in your chart. Choose the “Series” tab to further customize your data range. Press “Next.”
5. Enter a title for your chart, X axis and Y axis.
6. Select the “Legend” tab. Check “Show Legend” and choose where to place the legend.
7. Press “Next” and choose where to place your chart. Press “Finish” to complete the process.
Spreadsheet Legend
8. Select a range of cells to place in your chart. The range should be near your data, such as the top, bottom or sides of the data.
9. Type the word “Legend” in the first cell of your range.
10. Type each item you wish to include in your legend. This should be a description of each item, not the symbol or color.
11. Highlight each item cell with the color used within your spreadsheet data if your legend is based upon colors. Select the cell and select the “Highlight” button on the “Formatting” toolbar. Use the drop-down arrow beside the “Highlight” button to choose different colors.
12. Enter the appropriate symbol beside each item cell in your legend if you used symbols instead of colors. Symbols often include arrows, dollar symbols, pound signs or asterisks.

Sunday, October 21, 2012

How to Change the Vertical Axis on an Excel Bar Chart

1. Click on the vertical axis of the bar graph.
2. Click on the 'Layout' tab under 'Chart Tools.'
3. Click on 'Format Selection.' The first tab in the pop-up window, 'Axis Options,' allows you to change general options like the interval between tick marks and placement of axis labels. For example, if you want the vertical axis labels to appear in front of the bars (as opposed to sitting in the middle), click on the 'Between tick marks' radio button under 'Position Axis.'
4. Select the placement for the vertical axis from the 'Axis Options' tab. For a vertical axis below the bar graph, choose 'low' from the drop-down menu next to 'Axis labels.' For an axis above the bars, choose 'High' from the drop-down menu.
5. Click on the 'Fill' tab from within the 'Format Selection' pop-up window to give your numbers a background or fill. Click on the 'Gradient Fill' radio button for a gradient fill, or click on 'Solid Fill' for a solid background to the numbers.
6. Change the alignment of the text from horizontal (reading across) to vertical (reading from top to bottom) by clicking on the 'alignment' tab and choosing 'Rotate all text 90 degrees' from the drop-down options box next to 'Text direction.' You can also choose custom angles and set margins from within this tab.

Saturday, October 20, 2012

How to Recover Unsaved Word Documents if a Computer Turns Off

Turn on the AutoSave Option
1. Open the Word file.
2. Click the 'File' tab on the command Ribbon. A list of basic commands opens.
3. Click the 'Options' button. A dialog window opens.
4. Click 'Save.' This link appears in the left pane. A dialog window opens.
5. Click to select the check box for 'Save AutoRecover information every 'X' minutes.' This option appears in the 'Save documents' section.
6. Type the number of minutes, such as 10, to specify the timed intervals.
7. Click to select the check box for 'Keep the last autosaved version if I close without saving.'
8. Click 'OK.'
Access the Document Recovery Pane
9. Open the Word program. The unsaved Word file may open with the Document Recovery task pane to the left. One to three versions of the file may display in the 'Available Files' text box.
10. Click a file version from the 'Available files' list. The drop-down displays three commands: open, save as and delete.
11. Click the preferred command for the version you chose. Click 'Open' to review and continue working on the file. Click 'Save As' to create a file name. Click 'Delete' to remove the file.
12. Save your changes.

How to Convert Rows Into Columns

1.
Open Excel and input the following data going across in cells 'A3 through L3' the months January through December. In cells 'A4 through L4' enter random figures as shown in the illustration. (NOTE: You can use an existing chart of your own, instead.)
2. Highlight the range starting from cells 'A3 through L4'. Do this by placing the mouse pointer in cell A3 and left-clicking the mouse once. While still holding down the mouse pointer, drag it to cover the last cell position in the range of data which is column L and row 4 (L4) and then let go of the mouse.
3.
Select 'Edit' and then choose 'Copy' from the toolbar menu.
4.
Place the mouse pointer in cell 'A6'. Right-click and select 'Paste Special.'
5.
Under the Paste section in the Paste Special dialogue box, place a check next to 'All', and under the Operation section, check 'None.' Finally, check 'Transpose' near the bottom of the Paste Special dialog box and then press 'OK.'
6.
Verify that the data has been transposed correctly. To clean up the spreadsheet proceed to Step 7.
7.
Highlight rows 3 and 4 by placing the mouse pointer at the beginning of row 3. Left-click and hold down the mouse to cover row 4 and then release the mouse. Right-click and press 'Delete.'
8.
Verify that the conversion was successful by viewing the data. Make further adjustments, deleting extra rows or adding titles as needed.

Friday, October 19, 2012

How to Make My Excel Spreadsheet Interactive

Use the 'Share Workbook' Command
1. Open the Excel file.
2. Click the 'Review' tab on the command Ribbon.
3. Click 'Share Workbook' in the 'Changes' group. A 'Share Workbook' dialog box appears.
4. Click the 'Editing' tab on the 'Share Workbook' dialog box.
5. Click to select the check box for 'Allow changes by more than one user at the same time. This also allows workbook merging.'
6. Click the 'Advanced' tab in the 'Share Workbook' dialog box.
7. Select the radio buttons for options, such as 'Track changes' and 'Conflicting changes between users.'
8. Click 'OK.'
9. Click 'Save' on the 'File' tab. If this workbook is a new file, the 'Save As' dialog window appears. Type the file name in the 'File name' text box. Click the location where the file should save, such as the Desktop or a named folder. Click 'Save.'
Use 'Windows Live SkyDrive'
10. Sign in to your 'Windows Live' account. A 'Hotmail' screen appears.
11. Click the 'SkyDrive' button at the top of the screen. A list of documents appears. If you do not see your Excel file, type the file name in the 'Search documents' text box.
12. Click the Excel file. The workbook appears on the 'Excel Web App' dialog window.
13. Click the preferred buttons, such as 'Edit in Browser' or 'Share.' The 'Edit in Browser' link brings up the command Ribbons with the 'Home', 'Insert' and 'View' tabs and commands.
14. Edit the worksheet. The workbook saves automatically.
15. Click the 'Share' button. A list of options appears.
16. Click 'Permissions.' The 'Edit Permissions' window appears.
17. Click the slider button on the 'Who can access this' slider. The choices range from 'Me' to 'Everyone (public).'
18. Select the 'Can edit' option in the text box.
19. Type a contact name or email address in the text box.
20. Click 'Save.' The Excel file is emailed to your recipient.
21. Close this application. The 'Documents' list appears. The Excel file includes an updated 'Date modified.' The 'Shared with' column also updates.

How to Delete All Unprotected Cells in Excel Spreadsheet

1. Open the spreadsheet containing the unprotected cells that you want to delete. Go to the 'Edit' menu in Excel 2003 and select 'Find.' Go to the 'Editing' group on the 'Home' tab in Excel 2007 or 2010 and click 'Find and Select.' Choose 'Find' from the drop-down menu. You can also use the keyboard shortcut 'Ctrl F' to open the 'Find' dialog box.
2. Click the 'Options' button to expand the 'Find' dialog box. Click the 'Format' button to open the 'Find Format' dialog box.
3. Go to the 'Protection' tab of the 'Find Format' dialog box.
4. Clear the 'Locked' check box by clicking inside it to remove the check mark. Click 'OK.'
5. Click the 'Find All' button. Excel will select all of the unprotected cells in the spreadsheet, which may take a few minutes.
6. Press 'Ctrl A' to select all of the results. Click 'Close' on the find dialog box. Press the 'Delete' key or right-click any selected cell and choose 'Clear Contents.'

How to Convert Wb3 Files to Xls

Quattro Pro Users
1. Launch Quattro Pro.
2. Go to 'File,' 'Open' and double-click the WB3 file you wish to convert.
3. Go to 'File,' 'Save As' and choose 'Microsoft Excel 97/2000/2002/2003' from the drop-down list, then click 'Save.'
Excel 97 or 2000 Users
4. Download and install the Quattro Pro converter from Microsoft for Excel 97 or Excel 2000.
5. Launch Excel.
6. Go to 'File' 'Open' and select the WB3 file you wish to change into an XLS file.
7. Go to 'File,' 'Save As' and choose 'Excel Workbook (*.xls)' from the drop-down menu, then click 'Save.'
Excel 2003 Users
8. Launch Excel.
9. Go to 'File,' 'Open' and choose 'Quattro Pro/DOS files' from the 'Files of Type' drop-down. Navigate to the folder where your WB3 file is located and double-click on it.
10. Go to 'File,' 'Save As' and choose 'Excel 97-2003 Workbook (*.xls)' from the 'Save as Type' list, then click 'Save.'

How to Compare Cells in Excel

1. Open a new Excel worksheet. Enter or import the first set of data into column A.
2. Enter or import the second column of data into Column C of the same worksheet. Leave Column B empty.
3. Type this formula in cell B1: '=IF(ISERROR(MATCH(A1,$C$1:$C$#,0)),'',A1)', replacing # with the row number of the last value in column C.
4. Select cells in column B, starting at B1 and dragging your mouse down to the cell that corresponds to the last row in columns A and/or C.
5. Go to the Home tab, click on 'Fill,' then select 'Down' from the drop-down menu to finish comparing the cells. Any duplicate numbers will appear in column B between the relevant cells in columns A and C.

Thursday, October 18, 2012

How to Learn Microsoft Office Excel

1. Open up Excel on your computer. You'll be greeted with a fresh spreadsheet. Play around with the software by typing data into blank cells. To get a quick understanding of Excel, some people dive right in to the process. Not every person learns by reading a book, so get an idea of what you can do before moving on to tutorials. Excel shouldn't be seen as on-the-job drudgery. You can balance checkbooks, use it for taxes and even create word puzzles. You can find lots of games to play on websites such as www.willard.k12.mo.us/co/tech/msexcel.htm that will teach you the basics while having fun.
2. Visit Microsoft Office's website for a list of training manuals and demos (see Resources). Manuals will give you step-by-step instructions on how to use Excel. Training sessions run between 30-50 minutes long, so you can choose how long to spend learning. Demos show users how to properly and time efficiently use the program.
3. Buy books that demonstrate the different functions of Excel. 'Microsoft Office Excel 2003 Step by Step' by Curtis Frye, 'Excel 2003 Bible' by John Walkenbach, and 'Excel 2003 for Dummies' by Greg Harvey are wonderful training books. You can find them at Amazon.com or www.powells.com. Check you local library's collection for these books and others written about Excel.
4. Take a class at your local library or community college. There are bountiful classes that will cover the basics and advanced skills of Excel. Prices for a class differ for those who have in-state-residency compared to those who are out-of-state. By taking a class, you are able to have one-on-one time with the teacher who can troubleshoot all of your questions. For people who are new to computers or learn better by instruction, this is the most recommended suggestion.
5. Practice on a free Excel tutorial online. You can go to www.baycongroup.com/el0.htm to use a free trail of Excel and learn how to improve your skills.
6. Meet other people learning Excel on an online message boards at www.ozgrid.com/forum/ or www.mrexcel.com/forum/index.php. You can complain, praise, or ask questions about Excel to people with a wide range of Excel skills. Oftentimes just by having an virtual support group can get you through the trails of Excel.

How to Remove Everything After a Character in Excel 2007

1. Start Excel 2007. Open the worksheet you want to use by clicking 'Open' in the menu linked to the round 'Office' button.
2. Identify the cell you want to trim. The cell's name is a combination of the letters printed across the top row of the worksheet and the numbers printed at the leftmost column. By default, the first cell in your spreadsheet will be 'A1.' The currently selected cell is always listed just above the first row containing the cell labels.
3. Choose a blank cell close to the cell you want to remove characters from and click on it to make it the currently active cell.
4. Type '=LEFT(' followed by the name of the cell you want to trim, a comma, and 'SEARCH(.' For example, if you want to trim the contents of cell 'A1,' you would write '=LEFT(A1,SEARCH(.' The Excel 2007 formula bar, which is located immediately to the right of the cell label, shows you the formula you're currently writing.
5. Write the final character you want preserved, making sure to put it between quotes, and then type a comma, the name of the cell again and two closing parentheses. For example, if cell 'A1' contains the phrase 'abcefg' and you want to remove everything after the letter 'c,' the formula bar should read '=LEFT(A1,SEARCH('c',A1)).'
6. Hit the 'Enter' key.

How to Change the Decimal Place in Cells in Microsoft Excel 2003

1. Highlight the cell in which you wish to change the decimal place. Simply left-click on that cell with your mouse.
2. Access the cell-formatting menu. Scroll to the “Format” tab on the command bar and select “Cells.” A cell-formatting-properties box will open.
3. Access the number submenu. Scroll to the “Number” section in the drop-down menu of the cell-formatting-properties box and left-click.
4. Change the cell decimal place. A “Decimal Places” subsection will appear to the right. Click on the up and down arrows to modify the decimal places. Then click on 'OK' to implement these changes.

How to Fill an Excel Cell With Two Colors

1. Right-click the Excel cell you wish to format and select 'Format Cells.'
2. Click the 'Fill' tab.
3. Click 'Fill Effects' under the color picker section.
4. Click 'Two colors' at the top-left, and choose the two colors from the right-hand drop-down menus.
5. Select the 'Shading styles,' and view the preview to get the effect you want.
6. Click 'OK' twice to exit both windows.

How to Calculate CPK With Excel

1. Launch Microsoft Excel and type “Data” in A1, “Upper Limit” in B1, “Average” in C1, “StDev” in D1, and “Cpk” in E1.
2. Type “1” in A2, “2” in A3, “3” in A4, “4” in A5, “5” in A6, “6” in A7, “7” in A8, “8” in A9, “9” in A10, and “10” in A11. Type “15” in B2.
3. Add the following formula in C2 to calculate the average for the data:=AVERAGE(A2:A11)
4. Add the following formula in D2 to calculate the standard deviation for the data:=STDEV(A2:A11)
5. Calculate CPK using the upper limit, average, and standard deviation values by adding the following formula in E2:=((B2-C2)/(3*D2))

How to Do Name Badges With Microsoft Excel

1. Launch Excel. A blank spreadsheet will appear.
2. Populate a column with names. If you want to include titles on the badge, populate a second column with this information. Click 'File' and select 'Save.' Give your list a name, and click 'OK.'
3. Launch Microsoft Word.
4. Click 'Tools.' Point to 'Letters and Mailings,' and click on 'Mail Merge Wizard.'
5. Click 'Labels.' Select 'Label options.' Select your name badge size.
6. Click 'Use an existing list' under 'Select recipients.' Click 'Browse.' Select the Excel file with your name badge information. Click 'Open.'
7. Click your Excel list in the 'Select Table' dialog box. Click 'OK.' Click 'File' and select 'Print.'
8. Peel off the printed labels from the label paper and stick them onto the name tag holders.

How to Use VBA Userforms

1. Display VBA by choosing the 'Tools' menu, then 'Macro' and finally, 'Visual Basic Editor.' The VBA window appears.
2. Choose the 'Insert' menu and 'Userform.' A blank userform with handles appears on the right and a 'Toolbox' window appears below that. A userform icon appears in the 'Project' window in the upper left. The 'Properties' window in the lower, left changes to display the userform properties. If these windows are missing, choose them from the 'View' menu.
3. Resize the userform by clicking on a handle, holding down the left mouse button and dragging the handle.
4. Rename the userform to something more relevant by clicking the name in the 'Properties' windows. Type in a new using the form 'frm
,' which follows recommended programming style. Example names include 'frmStartup' or 'frmMain.'
5. Change the caption by clicking on the 'Caption' property and entering a new name. Because this name appear at the top of the userform, enter one that is meaningful to the user such as 'Sign-in' or 'Main.'
6. Adjust additional properties as needed by clicking on their values. You can then set new values by typing them in or selecting them from a dropdown. For example, to change the background color, click the 'BackColor' property, choose the 'Palette' tab and choose a new color.
7.
Add controls from the Toolbox. If this window is not visible, choose the 'View' menu and select 'Toolbox.' Click on the Toolbox control you want to add and drop it into the userform. Note that the information in the 'Properties' window changes to relate to the current control. To define the properties of other controls or the userform, choose a new control in the dropdown at the top of the window.
8. Change a control property by clicking on the property and typing a new value or choosing one from the dropdown. Resize controls by their handles, or click on their centers to move them around the form.
9.
Enter code for the userform itself by clicking the first 'Select Objects' button in the Toolbox and then by double-clicking on any blank part of the form. The code window appears on the right with a blank 'Click' subroutine. Enter code as needed or enter a new subroutine by choosing a name from the 'Subroutine' dropdown in the upper right. Note that you can enter code for different controls by clicking the 'Control' dropdown in the upper left.
10. Return to the userform as needed by double-clicking its icon in the 'Project' window. Don't forget to save frequently as you build the form.
11. Test your creation by clicking the 'Play' button of the 'Standard' toolbar in the top toolbar. If this bar is missing, choose the 'View' menu, then 'Toolbars' and finally, 'Standard.'
12. Close the window and return to the Word document by choosing the 'File' menu and then the 'Close' option.

Wednesday, October 17, 2012

How to Create a Cumulative Frequency Distribution Chart in Excel 2003

1. Click cell A1 and type 'Class Limits'.
2. Click cell B1 and type 'Frequency'.
3. Click cell C1 and type 'cumulative frequency'.
4. Type the class limits (categories) for your data in column A, starting in cell A2 and continuing down the column. Enter one set of class limits per cell. For example, if your study is about IQ scores you might have class limits of 0-50, 51-100, and 101-150. Type '0-50' in cell A2, '51-100' in cell A3 and '101-150' in cell A4.
5. Type the frequencies in column B, starting in cell B2 and working down the column. A frequency is how often a particular item was found. For example, if you are studying the IQ scores for a group of 20 children, you might have frequencies of 5 children with IQ score between 0 and 50, 7 with IQ scores of 51 to 100 and 8 with IQ scores between 101 to 150. Type '5' in cell B2, '7' in cell B3 and '8' in cell B4.
6. Click cell C2, type an equal (=) sign and type the cell of the first frequency listed in column B. In this example, the first frequency is listed in cell B2, so type '=B2'.
7. Click cell C3, type an equal (=) sign followed by 'C2', a plus ( ) sign and the location of the second frequency in column B. In this example, you would type '=C2 B3'.
8. Click the fill handle at the bottom-right of cell C3. The fill handle looks like a little black square. Drag the fill handle down the column until it is equal with the last filled-in cell row in column A.

How to Use Cells in Countif on Excel 2003

1. Run the Microsoft Excel application and open the worksheet that you wish to edit.
2. Determine the cell addresses of the range that you want to count. For example, to count all instances of an object in the first 50 rows of column A you refer to cells A1:A50.
3. Click a cell that you want to use to perform the COUNTIF calculation and display the observed value.
4. Type the following in the cell:=COUNTIF(A1:A50, 7)Substitute the cell addresses of your range in place of 'A1:A50', and substitute the value that you wish to count in place of '7'. You can search for either a number value or a character string. In order to search for a character string you must surround the value with quotation marks. For example, this formula will count all cells in the range that contain the word 'Trouble':=COUNTIF (A1:A50, 'Trouble')Alternately, you can use a cell reference as your criterion. For example, the following search counts all items that match the value in cell B3:=COUNTIF (A1:A50, B3)
5. Press Enter. Excel will calculate the frequency of your search criterion in the data range and will display the observed value in the formula cell.

How to Balance Your Checkbook Using Microsoft Excel 2010

Create a Register Template
1. Open a new spreadsheet in Excel 2010. Leave cell A1 empty, and label the various columns you will need beginning in cell B1. Common examples of column headers to add to your spreadsheet include date, check number and transaction information. The last three column headers should be debit, credit and balance.
2. Apply any desired formatting to your register. This includes changing font size and adding a colored background to your column headers so they stand out more. You should also adjust the size of the columns to compensate for the information that you will enter into them. Those that will be used for transaction or memo information should be considerably wider to allow you to enter more detailed notes.
3. Enter data into your spreadsheet to test its functionality. Do not use real data, but instead create generic information so you can make sure that the columns you created in Step 2 are adequate for the information that you need to enter.
4. Create a formula in your spreadsheet so Excel can keep track of your account balance automatically. Make note of the first balance cell on your spreadsheet. If Column G is marked as your balance, then your first balance cell would be G2. This cell will remain untouched, as it will be where you enter your beginning balance. To create the formula with a balance column at G, credit column at F and a debit column at E, enter the formula =SUM(G3-E3 F3) into cell G2. This instructs Excel to add any credit and subtract any debits on that line from your total balance.
5. Move your cursor to cell G3 and enter the following formula: =IF(AND((ISBLANK(E3)),(ISBLANK(F3))),'',SUM(G3-E3 F7)). Ensure that cell G3 is still active and press the auto fill button with your mouse. Drag your curser down the balance columns in your spreadsheet until you reach the 100th line. You can now enter debit and credit information and Excel will add or subtract it form your balance automatically on all lines between 2 and 100.
Enter Account Information
6. Reconcile your checkbook by subtracting any transactions that you have completed since your last statement date. If you have access to online banking, you can also use that to help you determine your current balance by comparing those transactions that have cleared to those that are still pending.
7. Enter your beginning balance on the first cell of your spreadsheet. In the above example you would enter your beginning balance into cell G2.
8. Enter all credit and debit transactions that you complete into the appropriate column so your balance is accurate at all times.

How to Define Cell Ranges in Excel 2003

1. Open Excel from the Start menu.
2. Choose your cell range. For example, highlight 'F5' through 'F28.' (Letters represent columns while numbers represent rows.) To highlight cells, just click and drag. To select a group of cells that are not touching, click each cell separately and hold down the 'Ctrl' key.
3. Click on the name box, which is directly above the 'A1' cell.
4. Type the new cell range name. For example, on a schedule, you might name all the time that you are commuting to work 'Commuting.' Hit the 'Enter' key.
5. Use the cell range names in your next formula. These names can make your formulas much easier to use.
6. Visit the Help section of the Microsoft Web site for more information on how to use cell range names in formulas.

Tuesday, October 16, 2012

How to Use the Freeze Pane Command in Excel to Create Static Headings

1. Open a spreadsheet file.
2. To freeze the top horizontal pane, select the row below where you want the split to appear.
3. To freeze the left vertical pane, select the column to the right of where you want the split to appear.
4. To freeze both the upper and left panes, click the cell below and to the right of where you want the split to appear.
5. Open the Window menu and select Freeze Panes. When you scroll through the worksheet, the area you selected remains on the screen.

How to Format the Values of Vertical Axis Currency in Excel

1. Click and drag to select the cells for the vertical axis. To select the entire row or column, click the cell header.
2. Click the 'Home' tab on the command ribbon.
3. Click the down-arrow for the 'Accounting Number Format' button in the 'Number' group. This button displays a currency symbol. A list of currencies and symbols appears.
4. Click the currency, such as U.S. dollar or the Euro.
5. Click the arrow in the bottom-right corner of the 'Number' group. A window entitled 'Format Cells' opens.
6. Click the 'Number' tab.
7. Click the 'Currency' option. The selected currency appears in the 'Symbol' and 'Negative Numbers' text box.
8. Select the number of decimal places in the 'Decimal places' text box. For example, '2' for U.S. dollars.
9. Click an option in the 'Negative numbers' text box. For example, a negative number can appear in red text or inside parenthesis.
10. Click 'OK.'
11. Type the values for the vertical axis. The values automatically format to the selected currency.

How to Change the Orientation of Text in Excel Cells

1. Start Microsoft Excel and open the file you want to change.
2. Select the cells in which you want to format the text.
3. Open the Format menu and select Cells.
4. In the Format Cells dialog box, select the Alignment tab.
5. In the Orientation box, increase or decrease the number of degrees, or drag the indicator to the angle you want.
6. Select OK to accept the changes.

How to Create a Drop Down Menu in Excel 2007

1. Switch to a different sheet in the workbook that is being used by clicking a tab on the bottom of the window. This helps prevent the list from being confused with a data-entry area.
2. Type a list of items on the sheet using cells that are on top of one another (e.g., A1, A2, A3 and A4). Leave no spaces between the items and type them in the order that they should appear on the drop-down menu.
3. Use the mouse to draw a box around the cells you used for the list.
4. Click inside the text-entry box at the top of the screen, to the left of the formula area. To confirm that you have the correct box, hover the mouse cursor over it. A message saying 'Name Box' will appear. In the box, type a name for the list. Remember the name.
5. Return to the original sheet and click the cell where the drop-down menu should be created.
6. Click the 'Data' tab at the top of the window. In the 'Data Tools' portion of the tab, click 'Data Validation.'
7. Click the drop-down menu next to 'Allow' and select 'List.'
8. Click inside the 'Source' box. Type the equal sign followed by the name of the list. For example, if you named the list 'MyList,' type '=MyList' in the box. Click 'OK.' The drop-down menu will appear in the cell.

Monday, October 15, 2012

How to Use the Excel Chart Wizard

1. Enter your data into an Excel spreadsheet.
2. Start the chart wizard. Click on the 'Insert' button on the toolbar and then select 'Chart.'
3. Select the type of chart you want from the 'Chart type' list in the chart wizard dialog box. Different versions of the chart you have selected will appear in the 'Chart sub-type' window on the right. Select one and click the 'Next' button.
4. Examine the preview of your chart. The 'Data range' tab shows you the range of data from your spreadsheet that is being displayed in your chart. If you want to change the range of data or the labels, click in the 'Data range' box to make the changes without closing the wizard.
5. Plot your chart. Click on the 'Rows' or 'Columns' radio buttons to plot your chart by rows or by columns.
6. Add or remove data from the selected range. Click on the 'Series' tab to make adjustments if there is information you would like to add or remove. Use the 'Series,' 'Name,' 'Values' and 'Category (X) axis labels' to make the changes.
7. Enter chart options. Use this step of the wizard to enter labels for your chart, select the type of gridlines to show and choose whether to hide or display a legend and the data table. Click 'Next.'
8. Choose the location of your chart. Select the 'As new sheet' radio button if you want to place your chart on its own sheet. The 'As object in' radio button will embed it in an existing worksheet which you can select from the drop down menu.
9. Click 'Finish.' If you selected a new sheet for your chart, it will be displayed in a separate window. Otherwise, your chart will be displayed on the worksheet you selected with handles you can use to move it where you want it.

How to Calculate Time Cards in Excel

1.
Go to 'Start,' 'Programs,' 'Microsoft Office' and 'Microsoft Excel.' If you have used Excel recently, go to 'Start' and 'Microsoft Excel.' Click 'New' to open a new document.
2.
Go to 'Timesheets,' click on 'Weekly timesheets with breaks,' then click on 'Download' at the lower right-hand corner of the screen. The time card will have a place for the Company name, time in and time out.
3. Enter '9:00 AM' in the 'Time In' cell (E11) for Sunday. Enter '12:00 PM' in the 'Time Out' cell (E12) for Sunday. The total hours calculated before the break is 3.00.
4.
Enter '1:00 PM' in the second 'Time In' cell (E14) for Sunday. Enter '6:00 PM' in the second 'Time Out' cell (E15) for Sunday. The total hours calculated after the break is 5.00.
5.
Enter '9:00 PM,' '2:00 AM,' '2:30 AM' and '5:00 AM' in the respective 'Time In' and 'Time Out' cells for Tuesday. The time before and after the 'Meal Break' is 5.00 and 2.50 hours, respectively.
6. Change the formula for the day's total hours. Enter '=J12 J15' to add the total hours for the day. Press 'Enter.'
7. Copy and paste the formula in each cell, except the 'Total Hours Scheduled' cell, to remove the error message icon.

How to Make a Fitness Chart

Excel 2010
1. Click the 'File' tab and select 'New.' Type 'Fitness chart' in the search box. View the available fitness charts. Download the chart by clicking the chart image and the 'Download' icon.
2. Replace the default measurements in the spreadsheet with your measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Chart' tab. Depending on the template, the tab may be called 'Measurements' or something similar.
3. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
Google Documents
4. Access the Google Documents website. Type 'Fitness Chart' in the search box. View the available fitness charts. Download the chart by clicking the 'Use This Template' icon.
5. Replace the default measurements in the spreadsheet with your actual measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Graphs' tab. Depending on the template, this tab will be called 'Measurements' or something similar.
6. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
OpenOffice
7. Access the OpenOffice website. Type 'Fitness' in the search box. View the available fitness spreadsheets. Download the spreadsheet by clicking the 'Use This Template' icon.
8. Replace the default measurements in the spreadsheet with your own measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Graphs' tab. Depending on the template, it may be called 'Measurements' or something similar.
9. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.

How to Disconnect a User From a Shared Workbook

1. Click on the 'Review' tab in Microsoft Excel. Select 'Share Workbook' in the 'Changes' section.
2. Click the 'Editing' tab. Review the listing of user names under 'Who Has This Workbook Open Now.'
3. Click to highlight a user's name. Click on the 'Remove User' button.
4. Click the 'View' tab. Select 'Custom Views' in the 'Workbook Views' group.
5. Click to highlight a user's name in the 'View' list. Click the 'Delete' button.

How to Create a Pie Chart in Excel

1. Launch Excel and open the file which has the data that you want to base your pie chart on. The data can be created by simply entering the numbers, formulas, or by calling it in from other programs.
2. Highlight the data that you wish to base your chart on. The last cell that you wish to include will not be highlighted but it will have a bold outline around it.
3. Click 'Insert', 'Chart' and a Chart Wizard window will appear. Scroll down the right side column of Chart Type and click on 'Pie.' This will bring up several sub-types of pie charts on the left hand side of the window. Pick the one you think will best describe your data. Click 'Next.'
4. Review the data that you are seeing on this new window. If you do not see a pie chart with your data or the numbers look incorrect, click 'Cancel' and highlight your data and try the process again. If all is correct, select 'Next.'
5. Enter labels and a title for your pie chart on the next window. The next tab, Legend, allows the placement of the titles. The Data Labels tab allows manipulation and changes of the labeling of the pie chart. Click 'Finish' when the pie chart appears the way you want it to.
6. Manipulate the size of the chart on the spreadsheet by clicking the box it is in and dragging the mouse. Right clicking the chart and box that the chart is in brings up many options to edit the chart also.

How to Use the Chart Wizard in Access to Create a Chart

1. Open the Access database. Right-click the report or form to which you want to add a chart, and select 'Design View' to open it in Design View, which is a window showing the underlying design of your form or report that displays the details and properties of the boxes, buttons and other items in your object. Using Design View makes it easier for you to make changes to the way an Access object looks and acts.
2. Go to the 'Insert' menu and click 'Chart' in Access 2003. Click the 'Chart' button in the 'Controls' group of the 'Design' tab in Access 2007 or 2010. Click on the form or report where you want to place the chart. The Chart Wizard will open. The Chart Wizard is a small window that walks you through the steps to create precisely the chart that you require.
3. Select the table or query from which you want to obtain data to create a chart. To choose a table, click the 'Tables' button and click on the table you want to use from the list presented. To choose a query, click the 'Query' button and click on the name of the query you want to use from the list presented. Click the 'Next' button at the bottom of the window.
4. Choose the fields containing data that you want included in the chart. To select a field, click on the field name in the 'Available Fields' list. Click the single right-arrow button in the middle of the window to move that field to the 'Fields for Chart' list. Repeat, adding up to six fields to the 'Fields for Chart' list. Click the 'Next' button at the bottom of the window.
5. Select the type of chart you want to create by clicking on the chart type under 'What Type of Chart Would You Like.' To find out more about a specific chart type, click on the type to view a description in the right side of the Chart Wizard window. Click the 'Next' button at the bottom of the window after selecting a chart. Select layout options. Drag and drop field buttons from the right side of the Chart Wizard onto the sample chart in the main Chart Wizard window. Click the 'Next' button at the bottom of the window.
6. Type in a title for the chart in the 'What Title Would You Like for Your Chart' Box. Select 'Yes' or 'No' under 'Do You Want the Chart to Display a Legend?' A legend is a box next to or beneath the chart that provides further detail by identifying the colors or patterns used in the chart. Click the 'Finish' button at the bottom of the Chart Wizard window.
7. Click the 'Save' button on the 'Standard Toolbar' in Access 2003 or the 'Quick Access Toolbar' in Access 2007 or 2010 to save the chart added to your Access report or form.

How Do You Limit the Number of Rows in an Excel Spreadsheet?

1. Open Microsoft Excel from the 'Start' menu.
2. Press 'Alt' and 'F11' at the same time to open the visual basic editor.
3. Click 'View' on the top toolbar then select 'Project Explorer' from the drop down menu.
4. Click 'View' again then select 'Properties Window' from the drop down menu.
5. Select the name of the spreadsheet you are working with from the list in the explorer window on the left side of the screen.
6. Change the value in the 'ScrollArea' category to limit the number of rows allowed on the spreadsheet. For example, in order to limit the spreadsheet to 365 rows, you would enter the text '$1:$365' (without the quotation marks) into the ScrollArea box. To limit the spreadsheet to another number of rows just put that number in the place of 365 when entering '$1:$365' in the ScrollArea box.
7. Close the visual basic editor when you are done and the number of rows in the spreadsheet will be limited based on your specifications.

Sunday, October 14, 2012

How to Train in Microsoft Excel 2007

1. Talk to your employer. If you need to use Microsoft Excel 2007 for work, your employer may have a training program in place or may be willing to start one to help train employees.
2. Work with your instructor. If you need to use Microsoft Excel 2007 for a class, your instructor may be able to help you understand what you need to know to complete assignments. Tutoring services might also be available through your school.
3. Go through the Microsoft Excel 2007 intermediate level tutorial. This is a free service offered by Microsoft that takes you through all the basic functions of Excel and using a hands-on approach to help you retain what you learn. The tutorial takes 30 to 40 minutes.
4. Use the Excel 2007 'Support/Training' page when you have general questions about Excel or want to find out how to access or accomplish a specific task. From this page, you can access multiple informative articles.
5. Utilize other Microsoft Excel training courses. Microsoft offers separate programs for different uses of Excel, such as creating workbooks, entering formulas and adding charts. These courses give you more in-depth information about each major use.
6. Practice what you learn. Each time you learn something new in your Excel training, practice it. For example, if you learn how to use a formula, create a practice spreadsheet with data to test your knowledge. This helps you retain what you learn.

How to Use VBA to Save in Excel As a PDF

1. Double-click the Excel file name from My Documents or Windows Explorer, or launch Excel and use the 'Open' command from the start button. Browse to your file and click 'Open.'
2. Press the 'Alt' and 'F11' keys simultaneously to open the Visual Basic editor. Use the Project Explorer to browse to the module where you need to automate saving the spreadsheet as a PDF file. Place your cursor in your Visual Basic code where you want the save to occur.
3. Use the 'ExportAsFixedFormat' function to perform a 'Save to PDF.' This function can be invoked from any worksheet (e.g., ActiveSheet.ExportAsFixedFormat) or for the workbook as a whole (e.g., ActiveWorkbook.ExportAsFixedFormat).You can set a number of options with this command; the option values shown below are Excel's default values.[Mandatory] Type:= xlTypePDF[Mandatory] Filename:=
[Optional] Quality:= xlQualityStandard (xlQualityStandard or xlQualityMinimum)[Optional] IncludeDocProperties:= True ('True' means that the document properties, such as author name and document title, will be included in the PDF file; 'False' means that these properties will not be set in the PDF file.)[Optional] IgnorePrintAreas:= False ('True' means that all content in the spreadsheet should be included; 'False' indicates that only the information within your set printing areas will be included.)[Optional] From:= 1 (This is the page number where Excel should start the save. If this is omitted, then Excel begins at the first page.)[Optional] To:= 5 (This is the page number where Excel should finish the save. If this is omitted, then Excel ends on the last page.)[Optional] OpenAfterPublish:= False ('True' indicates that Excel should launch your default reader for PDF files with the PDF file open; 'False' indicates that the new PDF file should not be automatically opened.)
4. Execute the code function to ensure that the 'Save to PDF' works properly. Save the spreadsheet file.

How to Put a Check Box in Excel 2003

1. Open the specific Excel 2003 worksheet that needs to have a check box added. Locate the 'Forms' toolbar at the top of the screen, which shows a series of boxes corresponding to different forms available to be created.
2. Click the 'Check Box' icon in the 'Forms' toolbar, which shows a square blue box with a black check mark. Click the starting point anywhere inside the spreadsheet where you want the check box to appear. Drag your pointer down and toward the right to set the borders of the check box. Release your mouse button to create the check box.
3. Click the 'Check Box 1' text appearing next to the box and type whatever label you'd like to use for the box. Click the 'Control Properties' icon located in the 'Forms' toolbar to open a new window containing the check box's properties.
4. Navigate to the 'Control' tab. Type the name of the cell that should correspond to the check box in the 'Cell Link' box. For instance, if you want the box to be checked in correlation to data stored in cell 'A4,' type 'A4' in the box. Click the 'Checked' or 'Unchecked' radio button to change whether the box should check or uncheck when data is entered into the corresponding cell.
5. Click 'OK' to save your changes. If you need to make changes to the check box in the future, click the box and then click the 'Control Properties' button.

A Line Won't Delete in Microsoft Excel

1. Check to see if you are highlighting the correct line to delete in your document. Point your cursor on the line number on the right side and left-click to highlight. Once highlighted, you should be able to delete the line.
2. Remove any document protections. If the Excel document is protected, this may prevent editing including removal of lines. On the 'Review' tab, click to see 'Changes' group to see if there are protections on the worksheet or workbook. These may have to be removed or a password inserted to continue.
3. Investigate the explanation of any error messages you receive when attempting to delete a line in Excel. The error message may provide insight into why the command cannot be executed. If you do not understand the message, visit Microsoft Online to search for Excel help.
4. Highlight all rows and press F5 and 'Go To' and click 'Special.' Select 'Visible Cells' and click 'OK.' This may now allow you to delete lines as necessary, especially if there are multiple or hidden lines.
5. Close the Excel document and exit the application. Delete any temporary or extra files off your computer as you may have a memory or resource issue. Shut down your machine and reboot. Open your Excel document and retry to delete your line.

How to Make a Graph on Excel 2003

1. Open the spreadsheet you want to graph in Excel 2003. Select 'Insert' and 'Chart' from the toolbar. The chart wizard appears.
2. Select the type of chart you want to create in the 'Standard Types' tab. To see a sample of how the chart will look, click the 'Press and Hold to View Sample' button and click 'Next.'
3. Click the button on the right of the 'Data range' text box to select the area of your spreadsheet you want to graph. The 'Source Data - Data range' toolbar appears.
4. Drag your mouse over the area of your spreadsheet that will be graphed.
5. Click the button on the right of the 'Source Data - Data range' toolbar to return to the chart wizard. A sample of your chart appears in the dialog box.
6. Select 'Rows' or 'Columns' under 'Series in' and click 'Next.'
7. Select the tab containing the options you want to customize: 'Titles,' 'Axes,' 'Gridlines,' 'Legend,' 'Data Labels' or 'Data Table.' Click 'Next.'
8. Select 'As new sheet' to place the graph in a separate worksheet or 'As object in' to place it into the current worksheet. Click 'Finish.'
9. Select the graph on your worksheet if you chose 'As object' in the previous step and drag it where you want it to appear.

Saturday, October 13, 2012

How to Edit a Calculated Field PivotTable

1. Open Excel 2010 and open a workbook containing a PivotTable.
2. Click the worksheet containing the PivotTable. Click the PivotTable. The PivotTable Tools Tab appears. Click the 'Options' tab and 'Fields, Items, and Sets.' Select 'Calculated Field.' Your calculated fields are displayed.
3. Select the calculated field formula that you want to edit. Click the 'Formula' box and select 'Modify.' Edit the formula and click 'OK.' Your calculated field is edited in your PivotTable.

How to Use Excel's ZTEST Function

1. Learn the syntax for the ZTEST function. It is: ZTEST(array,u0,sigma) where array of data to test the given value against, u0, is the given value to test and sigma is the known standard deviation. ZTEST returns the #N/A error value if array is empty.
2. Calculate ZEST when sigma is supplied as follows: ZTEST(array,u0) = 1 - NORMSDIST(x - u0)/(sigma/square root of n)).
3. Substitute the sample standard deviation if sigma is omitted as follows:
ZTEST(array,u0) = 1 - NORMSDIST((x - u0)/(s/square root of n)).
4. Note that x is AVERAGE(array)), s is the known standard deviation and n is the number of observation in COUNT(array). Furthermore, if AVERAGE(array)
5. Determine the two-tailed probability. This is the probability that the sample mean is further from the given population mean than AVERAGE(array) and is expressed as follows:2 * MIN(ZTEST(array,u0,sigma),1 - ZTEST(array,uo,sigma)) where u0 is the given population mean.
6. Use Excel 2003 and later for improved accuracy of NORMDIST. Previous versions of Excel used a single computation for all values of z, providing accuracy to about seven decimal places.

Friday, October 12, 2012

How to Use Excel to Make a Queue

1. Open a new Excel 2010 spreadsheet. Click the 'File' tab at the top of the screen and choose 'Options' on the left side of the screen. Select 'Customize Ribbon' from the left side of the new window. Find the 'Developer' listing on the list on the far right side of the window. Click the check box next to 'Developer' to add a check and click 'OK.'
2. Select cell 'A1' and type in 'Name.' Select cell 'B1' and type in 'Time.' Click the 'B' at the top of the second column. Right-click anywhere in the column and choose 'Format Cells.' Select 'Time' from the list on the left side of the window. Select the option that says '*1:30:55 PM' and click 'OK.'
3. Click the 'Developer' tab at the top of the screen. Click the 'Insert' button in the 'Controls' area of the ribbon. Click the 'Button' icon in the top-left corner of the small field of icons that opens up. Move your mouse over the spreadsheet. Click and hold the mouse button and drag it down and to the right. Release the mouse button to create a button on your spreadsheet. Type 'Add' into the 'Macro Name' field and click the 'New' button. The VBA console will open up automatically. Click the 'Minimize' button to get this window out of the way for now.
4. Click the 'Insert' button again and repeat the process. This time, name the macro 'Remove.' The VBA console will come up automatically again.
5. Click on the line between 'Sub Add()' and 'End Sub' in the VBA console. Enter the following text into the console:Rows('2:2').Insert shift:=xlDownRange('C1').CopyRange('A2').PasteSpecialActiveCell.Offset(0, 1) = NowRange('A:B').Sort Key1:=Range('B1'), order1:=xlAscending, Header:=xlYesThis code will take a name from cell 'C1' and add it to the queue.
6. Click on the line between 'Sub Remove()' and 'End Sub.' Enter the following text into the console:ActiveCell.EntireRow.DeleteThis code will delete the selected row from the queue. Click the 'X' in the top-right corner of the console to close it and go back to your Excel spreadsheet.
7. Click the 'Design Mode' button on the toolbar. Right-click the first button you created and click 'Edit Text.' Delete the text in the box and type in 'Add.' Do the same thing for the second box and type in 'Remove.'
8. Type in the name of the first person or thing you want to enter into the queue into cell 'C1.' Click the 'Add' button. The name will appear in column 'A' and the current time will appear in column 'B.' When you want to add another person to the queue, change the name in cell 'C1' and click 'Add.' When you want to remove an entry from the queue, just click on the name in column 'A' and click the 'Remove' button.

Thursday, October 11, 2012

How to Password

Microsoft Excel 2010
1. Open Microsoft Excel 2010.
2. Click 'File' on the top menu bar. Choose 'Info.'
3. Select the option to 'Protect Document.' Choose 'Encrypt with Password' from the list.
4. Enter a password. Click 'OK.' Retype the password. Click 'OK.'
Microsoft Excel 2007
5. Open Microsoft Excel 2007.
6. Click the 'Microsoft Office' button. Select 'Prepare' from the list. Choose 'Encrypt' from the menu.
7. Enter a password. Click 'OK.' Retype the password. Click 'OK.'
Microsoft Excel 2003
8. Open Microsoft Excel 2003.
9. Click 'Tools' on the top menu bar. Choose 'Options' and then 'Security.'
10. Enter a password in the box labeled 'Password to Open.' Click 'OK.' Retype the password. Click 'OK.'

How to Create a Check Box in Excel 2007

1. Open Microsoft Excel 2007.
2. Click the 'Office' button located in the left corner of the window represented by the Microsoft Office Logo.
3. Click 'Excel Options' located along the bottom of the menu on the right.
4. Click the tab titled 'Popular' and select the check box next to 'Show Developer tab in the Ribbon.' Click 'OK' when you have finished to exit.
5. Click the 'Developer' tab along the Office menu. Click 'Insert' and select the check box from the set of options.
6. Click-and-drag your mouse to draw the check box to your desired size.

How to Alphabetize a Listing in Excel 2007

1. Click on the Start button on the lower left-hand side of your screen. Point to All Programs at the bottom, to Microsoft Office, and then click on 'Microsoft Office Excel 2007.'
2. Click on the top-left circular Microsoft button and click on the second option, Open. Search for the excel spreadsheet you want to sort alphabetically on the left hand panel and double-click the folder it is located in with the main, right-hand panel. Double-click on the file name and click on Open.
3. Determine if the column of text you want to sort alphabetically corresponds to the any or all of the columns of text within that one spreadsheet. For example, a column of student names would correspond to student exam grades, essay grades, and overall grade point average (GPA).
4. Highlight all of the columns that correspond to each other. Do not highlight the column titles, such as 'Name' or 'Averages.'
5. Click on the Data button, the fifth option on the ribbon.
6. Click on the Sort option near the middle of the ribbon.
7. Click on the first arrow on the left-hand side next to 'Sort by' and choose the column you want to be sorted alphabetically. Click on the last arrow on the right-hand side and choose A-Z sorting or Z-A sorting.
8. Click on 'Add level' if you want to sort by other data after that one alphabetical column from the last step. You will want to include all of the columns associated to the first column you sorted by. For example, if you sort student names alphabetically you will want to sort all of the grades with the names. Click on the drop-down arrow right next to 'Then By' and choose the next column. Click on the last arrow on the right-most side and choose how you want the data sorted; if it's numeric data, it will say 'Smallest to Largest' or 'Largest to Smallest.'
9. Repeat step 8 to sort however many cells you wish.

Subscribe to: Posts (Atom)