Excel

Browse » Home » All posts

Tuesday, November 19, 2013

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.

How to Set Workbook Share Options in Microsoft Excel 2003

1. Open the workbook share menu. Scroll to “Tools” and then click on “Share Workbook.”
2. Set up the workbook to be shared. In the workbook share properties box that opens, check the box labeled as “Allow Changes to More Than One User at a Time.”
3. Set the advanced options. Click on the “Advanced” tab to access these options.
4. Set track changes options. Under the “Track Changes” field, you can set the number of days it tracks changes in the history by clicking on that radial button and using the up and down arrows to set the day limit. Or, you can set it to not track the history by clicking on the “Don’t Keep History Change” radial button.
5. Set update changes options. Under the “Update Changes” field you can have the workbook update changes every time the file is saved by another user. Utilize this feature by clicking on the “When File is Saved” radial button. You can set the workbook to automatically save at various time intervals by clicking on the “Automatically Every” radial button and using the up and down arrows to set the time preferences. This feature also allows users to save changes and see others’ changes.
6. Set conflicting changes between users’ options. Under the “Conflicting Changes Between Users” field you can set Excel to ask you which changes win, or set the options to changes being saved win by selecting the corresponding radial buttons.
7. Set include in personal view options. You can set the workbook to include print settings and filter settings by checking the corresponding boxes.
8. Save the changes. Click on the “OK” button to implement the workbook share options.

How to Use AutoFit Tool in Excel

Select the Data to AutoFit
1. Open the Excel workbook.
2. Select an entire worksheet by clicking the square in the top left corner.
3. Select a column by clicking the letter at the top of the column.
4. Select a row by clicking the number of the row on the left side of the spreadsheet.
5. Select an individual cell by clicking the cell.
6. Select multiple sequential columns, rows or cells by left-clicking on the first item, holding down the mouse button and dragging the mouse to include the items you want. Then release the mouse button.
7. Select multiple non-sequential columns, rows or cells by holding down the 'Ctrl' key while you make your selections using the instructions above.
Use AutoFit
8. Navigate to the Home tab on the top menu and look for the 'Cells' section.
9. Find the 'Format' button in the 'Cells' section and click it to reveal the drop-down menu.
10. Select 'AutoFit Column Width' to adjust the column width automatically. If you selected a worksheet, each column's width will adjust to fit the widest item in it. If you selected a column, the column width will adjust to fit the widest item in it. If you selected a row, each column's width will adjust to fit the widest selected cell in each column. If you selected one cell, the column width will adjust to fit the one cell.
11. Select 'AutoFit Row Height' to adjust the row height automatically. If you selected a worksheet, each row's height will adjust to fit the tallest item in it. If you selected a row, the row height will adjust to fit the tallest item in it. If you selected a column, each row's height will adjust to fit the tallest item in the selected cell in each row. If you selected one cell, the row height will adjust to fit the one cell.
12. Adjust the width of a column by selecting the column using the address at the top of it, and double-click on the line on the right side of the first column address selected.
13. Adjust the height of a rows by selecting the row using the address to the left of it, and double-click on the bottom line of the first row address selected.

Sunday, November 17, 2013

How to Convert Dates Times Into Decimals in Excel 2003

1. Click the Windows 'Start' button and type 'excel' in the search text box. Press 'Enter' to open the Excel 2003 software on your desktop.
2. Click the cell you want to format. Click the 'Format' menu item at the top of the window. Click 'Cells' from the list of options. This menu option opens a format window where you set up your cells' display options.
3. Click the 'Number' tab. Click the 'Number' option in the list. In the right panel with the list of number format options, click the decimal format you want to use.
4. Click 'OK' to save your settings. Type a date in the cell and watch the value change to a decimal number. Press 'Ctrl S' to save your changes.

How to Delete Blank Lines in Excel 2007 Lists

1. Open the Microsoft Excel 2007 application on your computer. Click the 'Office' button.
2. Click the 'Open' option and select the file that contains the information you want to edit. Click the 'Open' button.
3. Highlight all of the cells within the column that contains the blank rows that you want to delete. If you have multiple columns that need to remain in a specific order then select all of the cells in the columns.
4. Click the 'Data' tab. Click the 'Sort' button. Select the column heading, e.g., 'Column A,' that contains the blank rows in the 'Sort by' drop-down list. All of the blank rows will move to the bottom of the column.
5. Highlight all of the rows you want to delete. Click the 'Home' tab. Click the arrow under the 'Delete' option. Click the 'Delete Sheet Rows' option.

How to Protect a Workbook in Microsoft Excel 2003

1. Open the workbook that you wish to protect. You will need to first open a workbook before you can protect it. You can do this by scrolling to the “File” tab on the command bar and left-clicking on “Open.”
2. Open the workbook protect menu. To open the workbook protect menu, scroll to the “Tools” tab on the command bar and select “Protection.” Then scroll to “Protect Workbook” and left-click.
3. Set the workbook protect options. You can set two options by checking their boxes: structure (which will not allow the structure to be changed) and windows (which will not allow any modification windows to be opened). Check the boxes as desired.
4. Set the workbook protect password. Under the “Password” field you can enter the desired password, if you wish the workbook to be password protected.
5. Implement the changes. To implement the changes and protect your workbook, click on the “Okay” button.

Saturday, November 16, 2013

How to Copy Excel Macros From One PC to Another

1. Open the Visual Basic Editor (VBE) on the computer you want to transfer the file from by clicking on 'Alt' and 'F11' on your keyboard.
2. Right click on the module you want to export. The module list is on the left hand side of the VBE window.
3. Click on 'Export File.'
4. Select a save location on a thumb drive.
5. Move the thumb drive to the other PC.
6. Open the VBE on the PC you want to copy the file to.
7. Click on 'File' and then click on 'Import File.'
8. Click on the file location in the thumb drive, then click on 'Open.'

How to Round Up to the Nearest 10 in Excel

1. Open your spreadsheet and click on a cell next to the cell(s) you would like to round up to the nearest 10.
2. Input the formula '=MROUND(Cell Reference Number,10)'. The Cell Reference Number is the number to round to and 10 is the multiple it will round to.
3. Familiarize yourself with the concept of different formulas using MROUND to make your task easier. The following provide examples of the output for putting the following formulas in a cell.=MROUND(7, 10) Rounds 7 to a nearest multiple of 10 (10)
=MROUND(-9, -10) Rounds -9 to a nearest multiple of -10 (-10)
=MROUND(18.5, 10) Rounds 18.5 to a nearest multiple of 10 (20)
4. Download this function if it is not available on your toolbar. On the Tools menu, click 'Add-ins.' Select the 'Analysis TookPak' box and click 'OK.'

How to Add or Subtract Percentages

1. Convert the percentage you want to add or subtract to a decimal. For example, 60 percent in decimal form is equal to .60.
2. Multiply the amount to add or subtract to by the decimal percentage.
3. Add or subtract the result from the previous step from the original amount. If you want to find 60 percent off an item that is normally $10, multiply $10 by .60. The result is $6; subtract that from the original amount and you know the sale price is $4. Alternatively, if you wanted to add 60 percent, the new price would be $16.

How to Create a Bar Graph in Microsoft Works

1. Open Microsoft Works Spreadsheet by clicking 'Start,' 'All Programs,' 'Microsoft Works' and then 'Microsoft Works Spreadsheet.' If you wish to create a bar graph for previously entered data, click 'File' then 'Open.' Locate the file and click 'Open.'
2. Enter the data you wish displayed on the bar graph including row and column headers. When entering numerals in a spreadsheet, commas are generally not used. For example,A1 (blank)A2 JimA3 SueA4 Region TotalB1 Region 1B2 2589B3 4778B4 7367C1 Region 2C2 5456C3 5584C4 11040D1 Total SalesD2 8045D3 10362D4 18407Save your spreadsheet by clicking 'File' then 'Save.'
3. Select the data you want in the bar graph by clicking in the top-left cell (A1) and then dragging to the bottom right (D4) and releasing the mouse. The cells included in the bar graph will be those displayed in white text with a black background.
4. Click 'Create New Chart' under the 'Tools' menu. Select the first chart in the top row. Enter a name for your chart in 'Chart title.' You may optionally click 'Show border' or Show gridlines.' Click 'OK' to display your bar graph.

Friday, November 15, 2013

How to Track Changes to a Workbook by Others in Excel 2007

1. Open Microsoft Excel 2007. Click on the “Office” button.
2. Select 'Open' from the menu. Find and highlight the file where you want to track the changes. Click the 'Open' button.
3. Select “Share Workbook” from the “Changes” group of the “Review” menu. Check the box beside “Allow changes by more than one user at the same time” in the “Editing” menu.
4. Select the “Advanced” menu. Press “Keep change history for” under the “Track changes” group. Type the number of days to maintain a history of tracked changes in the “days” box.
5. Click “OK” to apply the changes. Click “OK” to save the workbook.
6. Select “Track Changes” from the “Changes” group on the “Review” menu. Click “Highlight Changes.”
7. Check the box beside “Track changes while editing. This also shares your workbook.”
8. Click in the check box next to “When” to select it. Select “All” from the “When” drop-down box.
9. Click in the check box beside “Who.” Select “Everyone” from the “Who” drop-down box.
10. Check the box next to “Highlight changes on the screen.” Click the “OK” button.
11. Click the “Save” button to save the file.

Thursday, November 14, 2013

How to Draw a Box Plot in Excel

1. Launch Excel. It should open up to a blank worksheet. Put your mouse in box A1 and type in the word 'Statistic.' Type 'Median' in cell A2, 'Q1' in cell A3, 'Min' in cell A4, 'Max in cell A5 and 'Q3' in cell A6. Next click on box B1. Type in 'a.' Continue entering data moving down the columns for cells B2 through B6, C1 through C6 and D1 through D6. Type the following numbers and letters, one in each cell, in this order: 40, 20, 10, 100, 70, b, 45, 22, 15, 110, 75, c, 50, 30, 18, 90 and 57.
2. Click on cell A1 and then drag your mouse to cell D6 with your finger still on the button. This will highlight all of the cells you just typed into. Click on the insert tab at the top left of your Excel window. Click on 'Other Charts' and then on 'Stock.' You will see icons for four types of stock charts. Roll over them with your mouse. Click on the icon for the one that says 'Volume-Open-High-Low-Close.' Then click 'Next.'
3. Click on the tab that says 'Data Range.' Look for a header that says 'Series In.' In the list below, select 'Rows.' Click 'Next.'
4. Click on the tab that says 'Axes.' Look for the words 'Secondary Axis.' Click on the box next to 'Value (Y) Axis.' Click 'Finish' at the bottom right.
5. Go to your chart, which should have popped up on the screen. You will see both white box columns and colored box columns. Click on a colored column to select it. Go back to the 'Chart' tab and select 'Line' under chart type to change how the graph is displayed. Click 'OK.' You now have your box plot.

How to Create a Grade Book Using Microsoft Excel

1. Open a new spreadsheet in Excel. Save it using a name such as 'Gradebook template.xls.'
2. Type in a column name in the top left cell of your spreadsheet. This column will serve as the identification column for your students. For instance, the column name could be 'Student Name' or 'Student ID.'
3. Type in brief descriptions of all the assignments for the semester in the cells of the top row, starting with the second column. For example, the column headers could have titles like 'Exam 1,' 'Term Paper 1,' 'Group Project,' 'Exam 2' and so on.
4. Type 'Sum' into the top row of the column immediately following the assignment columns.
5. In the cell immediately below where you typed 'Sum,' type the formula '=sum('. In the second row of your spreadsheet, click the second cell and then drag the cursor all the way to the last assignment cell in the second row. This will select all of the class assignment cells on this row, and you will see the range of selected cells in your formula cell as well. For instance, if you selected cells B2 through G2 in the second row, the formula cell will now read '=sum(B2:G2'. Now type ')' in that cell to complete the formula, so that the formula cell reads '=sum(B2:G2)'. This cell will now contain the sum of all the grades for that row.
6. Save the grade book template spreadsheet, and create a copy for the current semester by clicking on 'File' on the upper left of your spreadsheet, then choosing 'Save As' and then giving it a new name such as 'History Grade Book Fall 2010.' Save the original template for future use.
7. Enter the names or other identification information of your students in the first column.
8. Click on the cell below 'Sum' in the last column, right-click on it, and choose 'Copy' from the next menu.
9. Highlight all the cells in the last column, starting with the third row and going all the way down to the last student row, by clicking and dragging your cursor over the relevant cells.
10. Place your mouse anywhere over the highlighted cells and right-click. Choose 'Paste' from this menu. Now all the cells in the last column will contain the total grade of all the assignments of each student. Every time you enter a new grade for a student, the total will be updated accordingly.
11. Manually assign letter grades for students at the end of the semester based on their total points, or use other formulas in Excel to do this task for you.

Tuesday, November 12, 2013

How to Group Images in Excel

1. Start Microsoft Excel 2007 and open an existing workbook from your files that contains at least two images that you would like to group together.
2. Hold down the 'Shift' key on your keyboard and click all the images you want included in the group. You will notice that when an image is selected, there's a border around it with white sizing handles.
3. Select the 'Format' tab at the top of the Excel 2007 screen to display the Format ribbon. Locate the 'Arrange' section which is the second to last section in the Format ribbon.
4. Click the 'Group' button from the Arrange section in the Format ribbon to display a small drop-down menu. Choose 'Group' from this menu. You will notice that a large border with sizing handles now surrounds all the images you have selected to be included in this group. You can now treat these images as if they are one image.

How to Copy Paste Hyperlinks to Another Worksheet in Excel

1. Launch Microsoft Excel.
2. Select the worksheet tab at the bottom of the screen which contains the hyperlink you wish to copy.
3. Right-click on the cell containing your desired hyperlink and choose 'Edit Hyperlink.'
4. Select all of the data in the text field next to 'Address,' then press 'Ctrl C' to copy it.
5. Click the worksheet tab at the bottom of the screen which contains the cell to which you want to add your copied hyperlink.
6. Right-click on the cell and choose 'Hyperlink.'
7. Click inside the field next to 'Address' and press 'Ctrl V' to paste the hyperlink. Click 'OK' to close the hyperlink window.

Monday, November 11, 2013

How to Set Print Area in Excel 2007

1. Open the Excel 2007 worksheet in which you want to define a print area.
2. Click a cell and drag the cursor over the rest of the cells you want to print to select them.
3. Go to the Page Setup group located on the Page Layout tab, and click 'Print Area.' Click 'Set Print Area' to define the selected cells as the area of the spreadsheet you want to print.

How to Unlock a Password for Excel Spreadsheets

1. Copy and paste the spreadsheet into a new workbook. Some password-protected spreadsheets can be copied completely, including all formulas. If this does not work, you will have to download and use an Excel password cracking program.
2. Download an Excel password cracker program or find a macro. There are many programs and macros that you can find with a quick Google, Yahoo! or Bing search with the search phrase 'Excel password cracker.' One example is Office Password Recovery Pro, which can be used to unlock the spreadsheet password.
3. After installing your Excel password cracking program, double-click the program's icon or go to the 'Start' menu and select 'Programs.' Click the newly installed program from the list.
4. Click 'Open' in your program and select the option '100% Instant Document Decryption' or similar option. Select the document for which you need the password. This will recover passwords for spreadsheets as well as the password to open the document. An option will appear to either create a new document without passwords or to display the passwords for the given document.
5. Choose where you would like to save the unprotected document if that is the option you chose.
6. Click 'Recover the Password to Open' if you want the passwords displayed. Select the document, if not already done, and click 'Next' twice and select 'Finish.' The program will run and then notify you when the results are ready, showing both document and spreadsheet passwords.

How to Troubleshoot Excel's Autofill

1. Check your formulas for formatting errors. Ensure that all parentheses in your formula are part of a matching pair and enclose all nonalphabetic characters within single quotation marks. Always enter unformatted numbers in your formulas; for example, '$1,000' should be entered as '1000.' The numbers in cells can be formatted at a later time using the 'Format' menu.
2. Avoid losing data in cells when dragging the fill handle by changing your preferences. Under the 'File' tab, select 'Excel' then click 'Options.' Click 'Advanced' then click the checkbox labeled 'Alert before overwriting cells.'
3. Set calculations to 'Automatic' or formulas will not recalculate as you fill cells. To do this, click the 'File' tab, then select 'Excel' and click 'Options.' Select the 'Formulas' category, then under 'Calculation Options' choose the setting labeled 'Workbook Calculation.' Click 'Automatic' to have the formulas automatically recalculate.
4. Disable autofill by selecting 'Excel' under the 'File' tab and clicking 'Options.' Choose 'Advanced', then uncheck the box labeled 'Enable AutoComplete for cell values.'

How to Set a Print Area in Microsoft Excel 2003

1. Highlight the desired print area. Do this by left-clicking with the mouse and holding the button as you drag the cursor over the cells that you wish to highlight. Release the mouse button when you are finished and the area that you selected will remain highlighted.
2. Access the print properties menu. Scroll to the “File” tab on the command bar and select “Print Area.”
3. Set the print area. A submenu will open. Scroll to “Set Print Area” and left-click to set the print area. The print area will be outlined with a dashed border inside of the spreadsheet.
4. Check the print area in the preview screen. Scroll to the “File” tab on the command bar and select “Print Preview” to see how the file will look when it is printed.
5. Adjust the print area. To make any changes, just hover the mouse over a corner of the dashed border surrounding the print area. When the cursor turns to a cross-sectioned arrow, left-click and hold. Drag the print area to the desired size.

How to Activate a Toggle Button in VBA

1. Click the 'Start' button in Windows and select the 'Microsoft Excel' from the 'All Programs' menu to open a new workbook.
2. Click the 'Tools,' 'Macro' and 'Visual Basic Editor'.
3. Click 'Insert,' then 'UserForm' to insert a user form in your workbook.
4. Add the 'ToggleButton' and 'Label' controls on the user form.
5. Double-click the 'ToggleButton' control to open the 'Code' window for the ToggleButton control.
6. Enter the following code for the 'ToggleButton1Click event:Private Sub ToggleButton1_Click()If ToggleButton1.Value = True Then' Set UserForm background to Red.Me.BackColor = RGB(255, 0, 0)Else' Set UserForm background to Blue.Me.BackColor = RGB(0, 0, 255)End IfEnd Sub
7. Click the 'Run Sub/UserForm' on the 'Run' menu to activate the toggle button in your VBA control.

Monday, October 28, 2013

How to Align Column Numbers on the Decimal in Excel 2003

1. Select a column of numbers that you want to align by clicking on the letter of the column. For example, column C.
2. Click the Format menu and select Cells.
3. Select the Number tab in the window.
4. Select Number from the Category list.
5. Select the number of decimal places you want by clicking the up or down arrow next to the box. If you select four decimal places, for example, Excel will round off any number that has more. For example, 5.89237 will become 5.8924. Any number with fewer decimal places will be padded with zeroes. For example, 4.8 will become 4.8000.
6. Click 'OK.' Excel will now align all the numbers on the decimal.

How to Convert Lotus123

The Steps
1. Open the Media-Convert page. Media-Convert is a free-to-use online converter that can read Lotus 1-2-3 file extensions and convert those files into several other spreadsheet file types.
2. Add the Lotus 1-2-3 file to Media-Convert. To add, click on the 'Browse' button beside 'File.' In some browsers, the button may say 'Open File' instead of 'Browse.' When the 'Open' window pops up, find the Lotus 1-2-3 file on the hard drive and click the 'Open' button to upload the file.
3. Double-check the input format. 'Lotus 1-2-3' is one of the available file types in the 'Input format' list. Media-Convert should select this format automatically, but if the detection fails, you can select the file type from the 'Input format' list.
4. Choose an output format of your choice from the 'Output format' list. Available options for Lotus 1-2-3 file conversions include OpenOffice spreadsheets, PDF and Microsoft Excel workbooks. The type of file that you choose depends on the program that you need the file to open in.
5. Convert the Lotus 1-2-3 file. To begin the conversion, click 'OK.' Media-Convert will begin the process and the status of the conversion will be displayed on the screen.
6. Download the converted file. When Media-Convert is done converting the Lotus 1-2-3 file to your selected file type, you will be transferred to a screen with a download link for the converted file. To download, click on the link provided and save the new file to your computer.

How to Create Microsoft Excel PivotTables

1. Open the Excel 2010 document that contains the information you want to use in a PivotTable.
2. Click the Insert tab on the Excel ribbon. Click the PivotTable button at the left end of the ribbon. This opens the Create PivotTable window.
3. Look for the dotted lines on your Excel worksheet. These lines determine what data the PivotTable uses. The PivotTable defaults to include all the data on the sheet, but if you need to choose a different area, select the top-left cell of the area you want to use. Then navigate to the bottom-right cell, hold shift, and select that cell.
4. Choose between placing the PivotTable on a new worksheet or using an existing worksheet. If you choose an existing worksheet, you need to choose the worksheet from a drop-down box. Click OK to create the PivotTable.
5. Click on the fields in the field list, located on the right side of the screen, to add them to the PivotTable. Each column in your original data area will become a field listed here. As you add fields to the PivotTable, Excel automatically places them into one of the four PivotTable areas, located below the field list.
6. Click a field in one of the four PivotTable areas, and then drag it to a different area to give the PivotTable your desired look. The four PivotTable areas correspond to different aspects of the table itself. Fields in the Report Filter area sit above the PivotTable and allow you to filter the entire table based on the values in that field. Fields in the Column Labels and Row Labels areas make up the columns and rows of the PivotTable. Fields in the Values area make up the bulk of the PivotTable.

Sunday, October 27, 2013

How to Make a Bubble Graph on Excel

1. Open a new Excel 2010 spreadsheet.
2. Click on cell 'A1' and type in the title for your first set of data. This data will be shown along the 'X' axis on your bubble graph. Once the title is in place, enter the data into the cell in column 'A.'
3. Select cell 'A2' and enter the title for the second set of data. This data will make up the 'Y' axis on your graph. Once the title is in place, enter your data into column 'B,' underneath the title.
4. Click on cell 'C1.' Enter the title for your third set of data. This set will be represented by the size of the bubbles on your graph. Once the title is in place, fill out the rest of column 'C' with your data.
5. Click on cell 'A1' and hold down the mouse button. Drag your mouse down to the last cell in column 'C' and release the button.
6. Click 'Insert' at the top of the screen. Locate the 'Charts' area of the ribbon and click the 'Other Charts' button found there. Select 'Bubble' or 'Bubble with 3D effect,' depending on how you want the chart to look. The bubble chart will appear on your spreadsheet.

How to Change an ActiveSheet Name

Excel
1. Click a sheet tab (near the bottom of your screen) to activate the sheet.
2. Right-click the sheet tab.
3. In Excel 2003 or Excel 2007, select 'Rename.' In Excel 2010, select 'Rename Sheet.'
4. Type the new name over the old name.
Visual Basic for Applications (VBA)
5. Click on a sheet tab to make it the active sheet.
6. Open the Visual Basic Editor. In Excel 2003, select Tools > Macro > Visual Basic Editor from the menu. In Excel 2007 and 2010, click 'Visual Basic' in the 'Code' area of the 'Developer' tab.
7. Insert a new module. Select Insert > Module from the menu.
8. Enter code. Type the following in the new module:Sub RenameSheet()ActiveSheet.Name = 'Graphs'End Sub
9. Execute your code. Select Run > Run Sub/User Form from the menu.
10. Return to your spreadsheet. Click the 'Excel' icon in the toolbar below the menu. On the active sheet's tab, you will see the name 'Graphs.'

How to Make Excel Not Simplify

1. Highlight the group of cells that you plan to enter fractions in. To highlight an entire row or column, click the letter or number on the top or left side of the spreadsheet. Do this only in a spreadsheet that you intend to enter fractions in; if you change the formatting of cells already containing fractions, Excel will change the fractions to decimals.
2. Right-click the highlighted cells, and select 'Format Cells' on the context menu. A new pop-up window titled 'Format Cells' appears.
3. Select the tab labeled 'Number' at the top of the Format Cells window.
4. Click to select 'Text' on the left side of the window.
5. Click 'OK' to close the window, and then enter the desired fraction in one of the cells that you changed. Excel leaves the fraction as you entered it.

How to Make an Excel 2007 Spreadsheet

1. Open Microsoft Office Excel 2007.
2. Create an Excel spreadsheet from scratch by selecting the Microsoft Office Button. Click on 'New' and press the 'Enter' key. A blank worksheet will be placed on your screen. The first cell is column A, row 1.
3. Add a heading to the first cell, such as 'Department' and press the 'Tab' key. You can use the 'Enter' key to go down one line or the 'Tab' key to move across your cells.
4. Add another heading to the right of 'Department' (in column B, row 1) called 'Sales' and press the 'Tab' key. Continue adding more information to your heading (in column C, row 1) as needed.
5. Place your cursor in the second row (column A, row 2) and type in a department name below the 'Department' heading, such as 'Marketing' and press the 'Enter' key. Repeat this step to add additional data to your rows (column A, row 3; column A, row 4) until you are finished.
6. Format the numbers to display in a specific format (such as $0.00) for the 'January Sales' column. Select the column heading (B), just above January Sales, to select the entire column.
7. Select the 'Home' tab, go to the 'Cells' group and choose 'Format.'
8. Make sure that the 'Number' tab is selected in the 'Format Cells' dialog box and choose 'Currency' under 'Category.'
9. Accept '2' for the number of decimal places. Then click on 'OK.' Once you have all the information you need, save your Excel 2007 spreadsheet by clicking on the 'Save' button in the 'Quick Access Toolbar' (shortcut: Ctrl S). Name your file and click on 'Save.'

How to Export Excel Graphs as Pictures in Words

Import the Excel File into a Word Document
1. Open the Word 2010 document.
2. Click the “Insert” tab on the command ribbon.
3. Click “Object” in the “Text” group. The “Object” dialog window opens.
4. Click the “Create from File” tab.
5. Type the Excel file name or click “Browse” to locate the Excel chart file.
6. Click “Insert.”
7. Click “OK.” The Excel file opens on the Word document.
8. Click in the chart area. If the chart appears huge, click and drag the sizing handles to re-size. You can also position the chart over the worksheet to minimize the worksheet from view.
9. Click outside the chart to set the Excel chart in the Word document.
Copy the Excel Chart into the Word Document
10. Open the Excel file that contains the chart.
11. Click in the chart area. Click “Copy.”
12. Open the Word document.
13. Click in the Word document where you wish to insert the Excel chart.
14. Press 'Ctrl' and 'V' to paste the Excel chart in the Word document.

How to Merge Data in Duplicate Rows in Excel

1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click the 'Microsoft Excel' shortcut to open the software.
2. Click the 'File' ribbon and then click 'Open.' Click the Excel file you want to edit and click 'Open.' Click the cell where you want to display the merged results.
3. Type the following function in the cell:=concatenate(a1, b1)
4. Press 'Enter' to save the changes. The cells 'a1' and 'b1' are merged and displayed in the selected cell.

How to Draw Data Trees in Excel

1. Launch Excel 2010 and begin working on a new spreadsheet. Click the cell where you want the top item of the data tree to appear. Begin around cell J1 for a vertical tree or cell A10 for a horizontal tree to allow sufficient room for branches.
2. Click the 'Home' tab at the top of the window. Click the arrow next to the Border button in the 'Font' section of the toolbar and select 'Thick Box Border' to draw a border around the tree item.
3. Click a cell a short diagonal distance from the first cell. For example, if you put the first tree item in cell J1, click cell 'H4.' Type the first item that you want to connect to the item you entered in the first cell and then place a border around it. Repeat this step using different cells to add additional branches below the first item on the tree.
4. Click the 'Insert' tab at the top of the window and then click the 'Shapes' button in the 'Illustrations' section of the toolbar. Select an appropriate shape to use for the branches of the data tree such as a straight line or arrow. Click and drag to draw a line from the first item on the tree to one of the branches and then repeat this process to connect the remaining items.
5. Repeat Steps 3 and 4 to add additional items and branches to the tree.

Saturday, October 26, 2013

How to Copy Paste Conditional Formatting

1. Open the worksheet that contains the conditional formatting you want to copy and paste.
2. Select the range you want to copy. Make sure the cell that contains the conditional formatting is included in the range.
3. Go to the Format menu and select Conditional Format. The conditional formatting from the highlighted range will be already defined in the dialog box.
4. Click 'OK.' This will copy the formatting to the rest of the cells in the highlighted range.

How to Disable the Office 2007 Clipboard

1. Start Microsoft Word 2007 from the 'Start' menu or shortcut. If you do not have this application installed, you can load Excel 2007, Access 2007, Outlook 2007 or PowerPoint 2007 and follow the same steps.
2. Click on the 'Home' tab from the top toolbar so you can see the large 'Paste' icon. Below the 'Paste' icon is a caption labeled 'Clipboard.' Click on the small icon at the right of the 'Clipboard' text to bring up the clipboard task pane.
3. Click the 'Options' button on the bottom left and click on any items that are checked. Doing this will uncheck the item and disable the corresponding clipboard feature. Click on the 'X' icon on the upper right of the clipboard task pane to close it.

How to Calculate Ranges in Excel 2007

1. Open Excel. Click the 'Office' button. Select 'Open' from the menu.
2. Highlight the file you want to open. Click the 'Open' button.
3. Highlight the cells that you want to put in the range. Click in the 'Name Box' to the left of the 'Formula Bar.' Type a name for the range. Repeat this step until you have created all the needed ranges.
4. Click in the cell where you want to perform the calculations. Hold the 'Shift' key and tap the 'F3' key on the keyboard. Select the type of calculation from the 'Select a function' pane in the 'Insert Function' window.
5. Type the name of the range in the 'Number1' field of the 'Function Arguments' window. Type the name of the next range for this calculation in the 'Number2' field (This is optional if the 'Number1' field range contains more than one cell). Click the 'OK' button.

Thursday, October 24, 2013

How to Create User Forms With Excel 2003

1. In the 'Tools' menu, point to 'Macro' and then click 'Visual Basic Editor' or press 'ALT' 'F11' to open the Visual Basic Editor. Open the 'Main' menu, select 'Insert' and click 'User Form' to create a new UserForm object.
2. Place a TextBox control and a Label control for each column in your worksheet onto the form by double-clicking the control or by dragging them from the Toolbox onto the form. Replace any TextBox and Label combination with a ComboBox control if you wish to present the user with a list of options to select from for the field rather than a text box.
3. Assign the correct field name properties to the text and combo boxes by typing the corresponding database field names into the Name Property line in its corresponding property window. Change Caption Properties to a user-friendly display name at the same time.
4. Double-click the 'CommandButton' control tool four times to add the First, Previous, Next and Last buttons to your form. Type their names into the Caption Property line in their corresponding property windows.
5. Drag a TextBox control onto the form in between the Previous and Next controls. Type 'RowNumber' in its Name Property line. Type '2' on the Text Property line.
6. Click or drag three additional CommandButton controls onto the form. Type 'Save,' 'Cancel' and 'Add' onto their corresponding Name Property lines. Set the Enabled Property to False on the Save and Cancel command buttons when you type the Name Properties.
7. Arrange your controls and labels, then adjust the size of the user form with the click and drag method until you are sure that the form is easy to use and pleasing to a user.
8. Add any additional text boxes and set their properties as needed for additional functionality and appeal. A form title box is recommended.
9. Type this GetData routine into the VBA code window replacing your column names (field name properties) and data types with the sample CustomerId, CustomerName, City, State, Zip and DateAdded column names:'Private Sub GetData()Dim r As LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)ElseClearDataMsgBox 'Illegal row number'Exit SubEnd IfIf r > 1 And r
10. Type this ClearData routine into the VBA code window. Replace your column names (field name properties) with the sample CustomerId, CustomerName, City, State, Zip and DateAdded column names:'Private Sub ClearData()CustomerId.Text = ''CustomerName.Text = ''City.Text = ''State.Text = 'AK'Zip.Text = ''DateAdded.Text = ''End Sub'
11. Type this constant LastRow command into the VBA code window:'Const LastRow = 20'
12. Type this DisableSave routine into the VBA code window:'Private Sub DisableSave()CommandButton5.Enabled = FalseCommandButton6.Enabled = FalseEnd Sub'
13. Type this routine named RowNumber_Change into the VBA code window:'Private Sub RowNumber_Change()GetDataEnd Sub'
14. Set the appropriate event command by typing this into the VBA code window:'RowNumber.Text = '2''
15. Set the Previous and Next buttons codes by typing this into the VBA code window:'Private Sub CommandButton2_Click()Dim r As LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)r = r ? 1If r > 1 And r
16. Type this LastRow constant variable code into the VBA code window:'Private Sub UserForm_Initialize()GetDataEnd Sub'
17. Type this FindLastRow() routine into the VBA code window:'Private Function FindLastRow()Dim r As Longr = 2Do While r
0r = r 1LoopFindLastRow = rEnd Function'
18. Type these UserForm_Initialize events into the VBA code window:'LastRow = FindLastRowPrivate Sub CommandButton4_Click()LastRow = FindLastRow - 1RowNumber.Text = FormatNumber(LastRow, 0)End Sub'
19. Type the PutData routine into the code window changing the sample column names and locations to match your worksheet:'Private Sub PutData()Dim r As LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)ElseMsgBox 'Illegal row number'Exit SubEnd IfIf r > 1 And r
20. Type this Adding data routine into the code window:'Private Sub CommandButton7_Click()RowNumber.Text = FormatNumber(LastRow, 0)End Sub'
21. Type this Validating Data routine into the code window:'Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If KeyAscii
Asc('9') ThenKeyAscii = 0End IfEnd Sub'
22. Type this Exit event code into the VBA code window:'Private Sub DateAdded_Exit(ByVal Cancel As MSForms.ReturnBoolean)If Not IsDate(DateAdded.Text) ThenDateAdded.BackColor = HFFMsgBox 'Illegal date value'Cancel = TrueElseDateAdded.BackColor = H80000005End IfEnd Sub'
23. Type these combo box list details into the code window:'Private Sub AddStates()State.AddItem 'AK'State.AddItem 'AL'State.AddItem 'AR'State.AddItem 'AZ'End Sub'
24. Type these Displaying the User Form commands into the VBA code window:'Public Sub ShowForm()UserForm1.Show vbModalEnd Sub'
25. Review and text your form instructions and coding by selecting 'Run' from the 'Main Visual Basic for Applications Menu.'

How to Open XML in Excel 2007

1. Enable the 'Developer' tab in MS Excel 2007. Click on the Windows icon on the top left hand side of the screen and click on the 'Excel Options' button. Click on the 'Popular' category to bring up popular Excel 2007 choices. Then click on the box next to 'Show Developer Tab in Ribbon.' Click on 'OK' once this box is checked.
2. Click on the 'Developer' tab on the top of the screen to show the Developer commands. Click on the 'Import' button in the 'XML' box.
3. Browse through your files until you find the file you want. Click on the file to select it and then click 'Open.'
4. Select the import option you want in the dialog box that opens. 'XML Table in Existing Worksheet' will open the table beginning in the cell location you are currently in. 'XML Table in New Worksheet' will open up a new Excel file and import the file starting in field A1. Click 'Import' once you have selected the correct option to begin the import process.

How to Add Labels in Excel 2007

1. Open Microsoft Excel 2007 and the worksheet that contains the data you want to chart.
2. Select the data that will be charted and create a chart for it by clicking on the 'Insert' tab. Choose the type of chart you want to create from the 'Charts' group---column, line, pie, bar, area, scatter or other. Your chart will be placed onto the worksheet.
3. Make sure graph is selected, then click on the 'Layout' tab in the Ribbon, so you can add your data labels. Go to the 'Labels' group and click on the arrow by 'Data Labels.' Choose 'More Data Label Options' from the list of choices.
4. Select 'Label Options' in the 'Format Data Labels' dialog box.
5. Place a check mark by the options you want the data label to contain---'Series name,' 'Category Name' and/or 'Value' and remove any check marks you don't want.
6. Pick where you'd like the labels to appear---'Center,' 'Inside End' or 'Inside Base,' then click on 'Close.' The labels will be placed onto your chart.
7. Drag the labels manually on your chart if you'd prefer to place them in a different location.

How to Create a Formula in Excel 2003

1. Open Excel by double-clicking the Excel icon on your desktop. If you already have a spreadsheet made, you can create a formula using your existing data. If you do not have any data, just plug some numbers into the cells so you can try out these formulas.
2. Decide what your formula needs to calculate. Do you need to add up a column or row of figures? Do you need to divide one cell's value by another cell's value?
3. Start with an '=' (equal sign). Every formula will start with an equal sign. If you don't begin with this symbol, your formula will not work.
4. Put the first cell's name next. Each cell has a name assigned to it. 'A1' is the first cell in the worksheet. Find the cell name by looking at the row and column that it is in.
5. Use the correct function key: '*' for multiplication, '/' for division, ' ' is for addition and '-' for subtraction.
6. Insert the last cell name. This is just like a math problem. Use the same rules as a simple math problem and your formula will work for you.
7. Hit 'Enter.' Does your formula work? Double-check to make sure the components of the formula are correct.

Wednesday, October 23, 2013

How to Use Excel's VLOOKUP Function

1. Learn the syntax for VLOOKUP. It is VLOOKUP(search_value,table_array,column_index_number,range_lookup).
2. Specify search_value as the value to search for in table_array. Search_value may be a reference or a value. VLOOKUP will return the #N/A error value if search_value is smaller than any value in the first column of table_array.
3. Use table_array to provide the data to search. It must represent at least two columns and a reference to a range or range name may be used. The values in the first column may be logical values, numbers or text. The search is not case sensitive.
4. Provide the column_index_number. This is the column number of the value to be returned. For example, a 2 in this field will return the value in the second column. If column_index_number is less than one, VLOOKUP will return #VALUE!. If it is greater than the number of columns in the table being searched, VLOOKUP will return #REF!.
5. Enter a value for range_lookup if needed. This is a logical value that instructs VLOOKUP to perform an approximate or exact search. If range_lookup is TRUE or omitted, an exact match or the largest value less than search_value will be found. If range_lookup if FALSE, only an exact match will be found.

How to Select Visible Cells Only in Excel 2007

Using the 'Select Visible Cells Only' Button
1. Launch Excel and open the file that you'd like to work on.
2. Right-click on the menu bar and then click on 'Customize Quick Access Toolbar.'
3. Click the drop-down menu under 'Choose commands from' and then choose 'Commands Not in the Ribbon.'
4. Choose 'Select Visible Cells' from the list and then click the 'Add' button. Click 'OK.' The 'Select Visible Cells Only' button should now be visible on your Quick Access Toolbar.
5. Select a range of cells that contains hidden rows or columns. Click the 'Select Visible Cells Only' button. This removes all hidden cells from your selection.
Using the 'Go to Special' Window
6. Launch Excel and open the file that you'd like to work on.
7. Select a range of cells that contains hidden rows or columns.
8. Press 'F5.' Click on the 'Special' button. You may also do this by holding down the 'Alt' key and then pressing 'S.' This opens the 'Go to Special' window.
9. Press 'Y' to select 'Visible cells only.' Click 'OK,' or press 'Enter' to close the window. This removes all hidden cells from your selection.

Tuesday, October 22, 2013

How to Use Random Function in Microsoft Excel

1. Open Microsoft Excel.
2. Create two values between which you want a random number generated. For this example, enter '1' in cell A1 and '10' in cell A2, omitting the quotation marks in both cases.
3. Type the implementation of the random number function show below into cell A3:=RAND()*(A2-A1) A1The random function is designed to return a random value greater than or equal to 0 and less than 1. By taking the difference between the upper limit of the range (in cell A2) and the lower limit of the range (in cell A1) and then adding the lower range value, you generate a random number between those values.
4. Press the 'Enter' key to confirm the function entered in the previous step and return a random number within the specified range. To return only whole numbers, right-click the cell containing the function and select the 'Format Cells' option. Select the first tab on the resulting screen, choose 'Number' from the category list, specify a decimal place value of 0 and then click 'OK.'

How to Convert Word Files to Excel Files

Copy and Paste
1. Open the Word file in Microsoft Word.
2. Click and drag to highlight the information you wish to bring into Excel, press 'Ctrl-C' to copy.
3. Open Microsoft Excel and click into a blank cell.
4. Press 'Ctrl-V' to paste' the Word content into Excel. Table data from Word transfers the best. Paragraph text is pasted into the first cell on each row, with one paragraph per row.
Save the Word file as HTML First
5. Open the Word file in Microsoft Word.
6. Select Click the 'File' menu and select 'Save As.' Choose 'HTML' or 'Web page' in the Save As Type drop-down menu.
7. Click 'Save' and you will have a file with the .htm extension.
8. Open Excel to a blank spreadsheet, select the 'File' menu and choose 'Open.'
9. Select the HTML file you just saved from Word and click 'Open.' Excel will read this file, placing any table data in separate cells and individual paragraphs on new rows, but the text is formatted a little better than with the copy-and-paste method.
10. Click in cells containing numerical table data and you'll see that they are completely editable in Excel. Excel maintains the format from the Word HTML document very well.
Insert Word File as an Object
11. Open Excel to a blank spreadsheet.
12. Select the 'Insert' menu, choose 'Object...' and choose Microsoft Word document as the type.
13. Click the 'From File' button, navigate to the Word file you want to bring into Excel and click 'OK.'
14. Notice that this object is not editable in Excel. It appears as an image on the spreadsheet. If you double click the object, however, Microsoft Word will open and allow you to edit the file. Any changes made to the file in Word are automatically transferred to the object in Excel when you save and exit Word.

Monday, October 21, 2013

How to Recover a Lost Document in Microsoft Excel

Closed Without Saving
1. Click 'File' and then click 'Recent.'
2. Click 'Recover Unsaved Workbooks.' A folder of drafts will open in another window.
3. Double-click the file to open it.
4. Click 'File' and 'Save As' to save the file so you don't lose it again.
Previously Saved
5. Open the file on which you were working before you lost your changes.
6. Click 'File' and 'Info.'
7. Click the version that says '(when I closed without saving)' under Versions.
8. Click 'Restore' at the top of the window to save the version you lost.

How to Create Microsoft Excel Forms

Add Data Forms Command to Quick Access Toolbar
1. Open Excel to a default, blank worksheet. Click the 'Microsoft Office' button at the top left of the screen to open the 'File' menu and then select the “Excel Options” to open a new “Options” window.
2. Select “Customization” from the list on the left side of the window to view commands you can add to the Quick Access toolbar. To make locating the 'Data' Forms command easier, click the drop-down list next to “Choose Commands From” and select “Commands Not on the Ribbon.”
3. Select the “Forms” command from the drop-down list and press the “Add” button to add the command. Click “OK” to commit and return to the blank worksheet. The “Forms” command icon will be on the far right of the Quick Access toolbar.
Prepare the Worksheet
4. Enter column titles. Type “January” in cell A1, and “February” in cell B1, and use AutoFill to fill in the remaining months by selecting cells A1 and B1, then placing the mouse cursor in the lower-right corner of cell B1 until it changes to a “cross” shape. Hold the mouse button and drag across the spreadsheet to cell L1.
5. Set column width by clicking on cell A1 and dragging across to cell L1 to select and highlight cells. Locate the “Cells” section on the “Home” tab of the main menu and click the “Format” drop-down box. Select “Column Width” and set the width to “20” (or whatever width you want the columns to be) and then click “OK” to return to the spreadsheet.
6. Type the number “12” in cell A2 and “4” in cell B2. This step is necessary so Excel can distinguish column headings from the data.
Create the Data Entry Form
7. Place your cursor in the “A” column heading, click to select and drag across the spreadsheet to select all columns.
8. Click the “Form” icon you added to the Quick Access toolbar to create a data entry form that displays as a new window. The data entry form displays the months you entered as column titles.
9. Enter information as necessary to complete the worksheet. To start a new row, click the “New” button and continue entering information. Click “Close” when data entry is complete to return to the completed worksheet.

Subscribe to: Posts (Atom)