Excel

Browse » Home » All posts

Wednesday, March 23, 2011

How to Make Gridlines Print in Microsoft Excel 2007 Spreadsheets

1.
Select the 'Page Layout' tab. It is located on the 'Ribbon,' the group of icons above the work area in Excel.
2.
Put a check in the 'Print' box. Once on the Page Layout Tab, look toward the right side and you will see a group called 'Gridlines.' This will cause the gridlines to show when you print your spreadsheet.
3. If you change your mind and would like to have the gridlines hidden on print jobs again, simply remove the check from the 'Print' box.

How to Create a MS Excel Skin

1. In MS Excel, select 'Format' from the Menu Bar.
2. Select 'Sheet.'
3.
Select 'Background.'
4.
Click on the picture you would like to use as a skin.
5.
Click on the 'Insert' button.

How to Put a Decimal Place in Excel 2003

1. Open Excel 2003 and select the 'File' drop down menu. Click 'Open.' Browse the files and locate a workbook. Click the workbook and the 'Open' button. The workbook opens.
2. Review the spreadsheets in the workbook and locate a workbook that needs decimal places in the data. Highlight the range the contains data that needs a decimal place.
3. Select the 'Format' drop down menu and select 'Cells.' Click the 'Number' tab. Select the 'Number' option in the left 'Category' section. Change the decimal places to '1.' Click 'Ok.' The decimal places are added to the Excel 2003 spreadsheet.

How to Use Excel's FREQUENCY Function

1. Remember the formula breaks down to the following: FREQUENCY(data_array,bins_array). Data is the set of values for which you will compute the frequencies. If you don't enter any data in the cell, you'll receive zeros in return. Bin is how you want the data broken down, or the ranges into which you want the data to fall.
2. Note that the FREQUENCY function will always give you one more frequency than data entered. For example, if you're a teacher and you enter in the test scores of 20 students, then you'll receive 21 frequency values.
3. Set up your worksheet to contain the data and the ranges you desire. Using the test score example, column A should contain individual test scores (data) and column B should contain the ranges corresponding to specific letter grades (bin).
4. Select vertical cells in column C plus one cell more than the number of cells used in column B. Enter '=FREQUENCY(A1:A#,B1:B#)' in the formula bar, replacing '#' with the last row number for your column A and B cells.
5. Press the following after you enter the function in the formula bar: 'Control' 'Shift' 'Enter' if you're working in a Windows-based program or 'Command' 'Enter' if you're working on a Macintosh/Apple computer.
6. View your results and notice the one additional number at the end of column C.

How to Use Excel to Generate Random Samples

1. Determine a range of numerical data in an Excel worksheet from which you want to generate a random sample. For example, generate a random sample from the numbers 1 through 10 listed in the first column in cells A1 through A10.
2. Click the 'Data' tab at the top of Excel and click 'Data Analysis' in the 'Analysis' group.
3. Click 'Sampling' in the 'Analysis Tools' list, then click 'OK.' This brings up a small window called 'Sampling.'
4. Click the button with the red arrow next to box called 'Input Range' in the 'Input' section. This shrinks the sampling window to a single row in which you can enter an input range.
5. Click and hold the left mouse button in the top left cell of the range of data from which you want to generate a random sample. For example, click and hold the left mouse button in cell A1.
6. Drag the mouse to the bottom right cell in the range of data, then release the mouse button. This shows the range of cells that contain the population data in the sampling window. For example, drag the mouse to cell A10 and release. Excel shows '$A$1:$A$10' in the sampling window.
7. Click the button with the red arrow in the sampling window to expand the window to its original size and show the other options.
8. Click the 'Random' button in the 'Sampling Method' section of the sampling window and type the number of samples you want Excel to generate in the box titled 'Number of Samples.' For example, click the 'Random' button and type '10' in the box.
9. Click the 'New Worksheet Ply' button in the 'Output options' section to tell Excel to place your sample in a new worksheet.
10. Click 'OK.' Excel generates a list of random samples and lists them in a column in a new worksheet with the first sample in cell A1. In the example, Excel lists random numbers between 1 and 10 in cells A1 through A10 in a new worksheet.

Tuesday, March 22, 2011

How to Add a Drop Down Calendar in Excel 2007

1. Make sure the Developer tab is showing in the ribbon (the toolbar at the top of the page). If it isn't, then click the 'Office' button at the top left, then choose 'Excel Options'. This will bring up a pop up window. Put a check mark in the 'Show Developer Tab in the Ribbon' option, then click 'OK'.
2. Click on the 'Developer' tab. Click on the small arrow below the 'Insert' tab and choose 'ActiveX Controls'. Click the last icon in the list ('More controls').
3. Choose 'Calendar Control' and press 'OK'.
4. Click on a cell in your worksheet where you would like to place the calendar. Click the 'Design Mode' button to turn off design mode and return to normal operation.

How to Create a Template for Excel 2003

1. Open Excel 2003 and locate a workbook that you want to base you template on. Click 'File' and 'Open' on the menu bar. Search your computer for the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Review the workbook and decide what information will remain in the workbook after it becomes a template. Information that would remain would be date formulas, contact information, company logo and other information relevant to your project. Remove any information that refers to a specific customer or vendor if this workbook will be used for all of customers and vendors.
3. Save the workbook by clicking 'File' on the menu bar. Click 'Save As.' Change your save as type to 'Template.' Type a template name in the 'File Name' field. Click 'Save.' Your template is now saved and is available for use in your future projects.

How to Use the Option Buttons in Excel

1. Open Excel. Click 'File' and 'New' in the menu bar. Select 'Blank Workbook' from the pane on the right side of the Excel window.
2. Turn on the Forms toolbar. Right-click in the gray area of any toolbar. Select 'Forms' from the displayed list.
3. Add a group box. Select 'Group Box' from the 'Forms' toolbar. Click and drag in the spreadsheet with the mouse to create a group box of the desired size.
4. Rename Group Box 1 by highlighting the text and typing an appropriate name.
5. Create the option button. Click on 'Option Button' in the 'Forms' toolbar. Click in the desired area of the group box to paste the option button.
6. Customize text for the option button. Right-click the option button. Select 'Edit Text' from the displayed menu. Type the new name.
7. Change the appearance of the option button, if desired. Right-click the option button. Select 'Format Control'. Add color and select line style and weight.
8. Create more option buttons by repeating Steps 5, 6 and 7. Use the arrow keys on the keyboard to align or reposition the option buttons.
9. Save the file. Click 'File' and 'Save' in the menu bar. Name the file. Click the 'Save' button. Click on 'File' and 'Close' in the menu bar to close the file.

How to Export OpenOffice Calc to XML

1. Open your Calc spreadsheet in OpenOffice.org's Calc program.
2. Click 'File' in the top menu and select 'Save As'. Alternatively, hold the 'Ctrl' and 'Shift' keys down and press 'S'.
3. Click the drop-down menu next to 'Save as type' and select 'Microsoft Excel 2003 XML (xml)'.
4. Enter a new name for your spreadsheet if one is not automatically chosen. If you previously saved this document the original file name is filled in. You can use this file name without overwriting the original file, since the extension will be different.
5. Click 'Save' to export your Calc spreadsheet in XML format.

Monday, March 21, 2011

How to Make a Thermometer Chart in Microsoft Excel

1. Open a blank worksheet in Excel. Enter headings for two columns of data, such as 'Month' and 'Total Clients' in cells A1 and B1. Enter the appropriate data into the column cells. The data should be building toward a goal.
2. Enter the goal number at the bottom of the column and enter the actual number reached in the cell beneath the goal amount, which will be the number in the last cell.
3. Skip a cell below the totals, and enter a formula to determine the percentage reached in the next cell. In the Formula Bar, use the formula total amount/goal amount. For example, the formula could be '=B17/B16.' Right-click the cell, select 'Format Cells,' select the 'Number' tab and choose 'Percentage.'
4. Select the cell with the percentage amount. Click the 'Chart Wizard' button in Excel 2003 or earlier and select a clustered column chart. In later versions of Excel, select the 'Insert' menu and choose a clustered column. In step 3 of the chart setup, go to the 'Axes' tab and clear 'Category (x).' On the 'Legend' tab, clear 'Show Legend,' and on the 'Data Labels' tab select 'Value.' Click 'Finish.'
5. Double-click the column of the chart. The Format Data Series dialog will open. Click the 'Options' tab and change the 'Gap Width' to '0.' Go to the 'Patterns' tab and click on 'Fill Effects.' On the 'Gradient' tab, select two colors, such as black and red, and select 'Horizontal' in the shading styles. Click 'OK' twice to apply changes and close the dialog.
6. Drag the left side of the chart toward the right, making the graph thinner. Right-click the chart and select 'Format Chart Area.' On the 'Patterns' tab, select 'None' for both Border and Area. Click 'OK' to apply the changes.
7. Add a circle auto shape at the bottom of the chart by selecting the autoshape from the drawing toolbar in Excel 2000 to 2003, or from the 'Insert' tab in later versions of Excel. Right-click the circle and select 'Format Shape.' Fill the circle with the same gradient colors used in the chart.

How to Break Hours Minutes Down into Increments for Excel

1. Open a new Microsoft Excel 2010 spreadsheet. Click on cell 'A1' and type in the time that you want to break down. Enter the time as 'hh:mm.'
2. Click on cell 'B1' and enter the following formula into the cell:=ROUND(A1*(1440/x),0)/(1440/x)Change each 'x' to be a number equal to the number of minutes in your desired time increments. If your increments are in seconds, change both instances of '1440' to '86400' and enter the number of seconds in each increment for 'x.' Press 'Enter' to complete the formula and a small number will appear in the cell.
3. Right-click cell 'B1 and choose 'Format Cells' from the drop-down menu. Click on 'Time' on the left side of the window that appears. Then choose the '37:30:55' option on the right side of the window. This format will allow you to view amounts over 24 hours correctly. Click 'OK' and the small number in cell 'B1' will be changed into your rounded time.
4. Click on cell 'C1' and enter the following formula:=B1/(0.00069444*x)Change 'x' to be the number of minutes in your increments. If your increments are in seconds, change the formula to read:=B1/(0.000011574*x)Change 'x' to be the number of seconds in your increments. Press 'Enter' to complete the formula and cell 'C1' will break your hours and minutes in cell 'A1' down into a number of increments.

How to Restore One Deleted Excel Worksheet

1. Click the 'Office Button' and select 'Save As' from the drop-down menu. Select Excel Workbook from the 'Save As' menu and type a new name for your workbook in the 'File Name' field. Press 'Save.' You will now have two workbooks---your original workbook before you made any changes with the 'deleted' worksheet intact, and the newly saved workbook with all of your changes. Only your newly created workbook will remain open.
2. Open your original workbook. You should now have both workbooks open.
3. Right click on the name tab of the 'deleted' worksheet in your original workbook and click 'Move or Copy...' when the menu appears. Under the 'To book' option, select your new workbook and choose where you would like the worksheet to appear in the 'Before sheet' option. Click 'OK.' Note that Excel takes you to your new workbook and the 'deleted' worksheet is displayed.
4. Check to make sure that links between worksheets, formulas and macros are functioning as expected. The next step will eliminate your original workbook, so be absolutely certain that your new workbook is correct before moving forward. If everything is in order, go back to your original workbook and close it.
5. Save your new workbook, renaming it with the original workbook name. The workbook with the 'deleted' worksheet will be overwritten with your now correct workbook.

How to Learn Excel 2003

1. Learn Excel directly from the maker using the resources available at Microsoft Office Online. This website offers courses ranging from 30 to 50 minutes that walk you through the basics of how to use Excel, so you can be on your way in just a couple of hours.
2. Check the website of your local community or technical college for introductory classes on Excel. These classes may be free or discounted compared to private university tuition. If you are a senior citizen you may be eligible for a discount on tuition. Most classes will focus on newer versions of Excel, but you can ask the instructor which functions are compatible with the 2003 version, and the basic operation of the software remains the same.
3. Teach yourself using the Help files that are included with Microsoft Excel. Simply open up Excel and press 'F1' on your keyboard to launch the Help panel. Browse to 'Training' and click on 'Get to know Excel 2003.' This will walk you through the basics of Excel.

How to Use Microsoft Excel 2003 as a Normal User

1. Open Microsoft Excel 2003. A blank spreadsheet appears. At the top, the toolbar lists the functions available to you. Use the arrow keys on your keyboard to move one cell at a time throughout your spreadsheet. You can also click into any cell.
2. Enter information by typing it and pressing the 'Enter' key. For example, to keep track of grades for three students, enter the first name in cell A2. Enter the second name in cell A3. Enter the third name in cell A3. Enter 'Test 1' in cell B1. Enter 'Test 2' in cell C1. Then, enter the grades for the students.
3. Format cells to make your spreadsheet easier to read. Click your mouse in cell B1 to add some formatting. Press the 'CTRL/B' keys to add bolding to the cell. Click your mouse in cell C1 to do the same thing. Press the 'CTRL/B' keys to add bolding to the cell. Notice that the numbers automatically align to the right of the cell. Text aligns to the left.
4. Add formula to perform calculations. For example, add a formula to cell D2 that calculates the average grade for the student in that row. Enter the formula below that adds the two numbers in cells B2 and C2 (the grades for the first student) and then divides them by the number of grades (in this case, 2).=(B2 C2)/2
5. Use Microsoft Excel 2003 functions to make your work easier. For example, replicate the formula you just entered by selecting cell D2. Press the 'CTRL/C' key to copy it. Click into cell E2 and press the 'CTRL/V' keys to paste the formula into the cell. To replicate a formula across a range of cells, select the cell that contains the formula and click the bottom right corner. Drag it down several rows and the formula copies into each cell, changing to reflect the current row.
6. Insert a new row by placing your cursor directly below where you want to put the new information. From the 'Insert' menu, select the 'Rows' option. To insert another column, select the 'Columns' option from the 'Insert' menu.
7. Sort information to order and organize it. Excel retains the relationships within the information for you. For example, to arrange student grades in descending order, click the 'Sort' option from the 'Data' menu. Set the sorting to 'Descending' to organize the information in this manner.

How to Have Multiple Users Use One Sheet in Excel

Sharing in Excel 97/2000/XP/2003
1. Create or open the workbook you would like to share.
2. Under the Tools menu, select 'Share Workbook.'
3. The 'Share Workbook' window will appear. The 'Editing' tab shows all users who currently have the workbook open (you should see your name in the list). Click the box to allow other users to access the workbook simultaneously.
4. The 'Advanced' tab allows you to adjust the options for sharing, such as how long changes are tracked, how often they are updated and what happens when information conflicts. Select the options you wish to implement and click 'OK.'
5. A message box will appear confirming you would like to save the workbook. Click 'OK.' The word 'Shared' should now follow the file name at the top of the window.
Sharing in Excel Office 2007
6. Create or open the workbook you would like to share.
7. Under the 'Review' tab at the top of the page, click on the 'Share Workbook' icon in the 'Changes' group.
8. As with previous Excel versions, the 'Share Workbook' window will appear. The Editing tab shows all users who currently have the workbook open (you should see your name in the list). Click the box to allow other users to access the workbook simultaneously.
9. Click on the 'Advanced' tab to adjust the options. Select the options you would like to implement and click 'OK.'
10. If it is a new workbook, the program will prompt you to save it. Enter the file name you wish to use and click 'Save.' If the workbook has already been saved, a message box will appear confirming you would like to save the workbook. Click 'OK.' The word 'Shared' should now follow the file name at the top of the window.

Subscribe to: Posts (Atom)