Saturday, May 28, 2011

How to Add a Histogram in Microsoft Excel for Macs


1. Install the StatPlus-Mac LE (full link in Resources). This free add-on provides Mac users with statistical analysis tools. Excel 2011 for Mac does not include Microsoft's Analysis ToolPak.
2. Restart your Mac to complete the installation process and add StatPlus to your Excel installation.
3. Open Microsoft Excel and select 'File,' and then 'Open' to either open an existing workbook or start a new one.
4. Enter your bin values, which display as the horizontal axis values on your histogram, in a single row or column, one per cell. These are the labels that display at the bottom of your histogram to label each bar.
5. Enter your numeric data in a single column or row -- one per cell -- that corresponds. Histograms can only compare qualitative data, so do not enter non-numeric values in this column. For frequency histograms, these values are your frequency data.
6. Select 'Basic Statistics,' and then 'Descriptive Statistics' from the menu in Excel. The StatPlus installation automatically adds these menu options to your Excel installation.
7. Enter the values of the cells containing the numeric data values.
8. Enter the values of the cells containing the X-values, or bin numbers.
9. Select 'Advanced Options,' and then check both the 'Plot Histogram' and 'Overlay' boxes. Select 'OK' to let StatPlus create the histogram from your data.
10. Insert the graph into the current worksheet if you wish to include the histogram along with the table of data, or open a new worksheet on to put the graph into its own worksheet.
Read more ►

How to Work With Pictures in Excel 2007


Inserting the Picture File
1. Open the Excel worksheet.
2. Click the 'Insert' tab on the command ribbon.
3. Click the 'Picture' button on the 'Illustrations' group.
4. Click the saved image file from the 'Picture Library' or other location, such as the Desktop. The picture file appears over the worksheet. The 'Picture Tools' ribbon appears with tabs, such as 'Format.'
Editing the Picture
5. Click the 'Format' tab in the 'Picture Tools' ribbon. The ribbon displays four groups of commands: Adjust, Picture Styles, Arrange and Size.
6. Select a command and click the down-arrow, such as Brightness or Contrast from the 'Adjust' group. For example, to adjust the brightness, you can point over the list of increments ranging from ' 40%' for an extremely bright look to '-40%' for an extremely dark look.
7. Click the down-arrow on 'Picture Styles' to view the variety of frames and effects for your picture. Point over the command button to display a preview of the effect. For example, your picture can convert to a framed picture when you point over the 'Simple Frame, White' button.
8. Click a command button from the 'Size' group. For example, adjust the picture size by inserting values in the height or width text boxes. Click the 'Crop' command to display dark crop marks on the outer edge of the picture. Click and drag these crop marks to adjust the borders and delete unwanted items from the picture.
9. Click and drag the picture to move it anywhere on the worksheet.
10. Save this Excel file.
Read more ►

Friday, May 27, 2011

How to Create a Drop


1. Open an Excel spreadsheet. Click in an empty cell and type the first item for the drop-down list. Press 'Enter' and then type the second entry. Continue doing this for all entries in the list you need.
2. Click the cell where you want a drop-down list. Click and drag across multiple cells to highlight all of them if you want them all to have the drop-down list.
3. Click the 'Data' tab on the Ribbon and then click 'Data Validation' in the Data Tools group.
4. Click the drop-down menu under the Settings tab and select 'List.' Click the button beside the Source text box to open the reference box. Click and drag over the cells that contain the options for the menu to select all of them, and then click the button beside the selected area on the dialog box. Make sure both the 'Ignore blank' and 'In-cell drop-down' options are checked.
5. Click the 'Error Alert' tab. Type 'Invalid Entry' in the Title text box. Type 'You have made an invalid selection. Please try again' in the Error Message text box. If a user does not select an option from the drop-down list, this error message flashes on the screen.
6. Click the 'OK' button to close the Data Validation dialog box. The drop-down list appears in all of the highlighted cells. Save the spreadsheet.
Read more ►

How to Force Excel 2007 to Be Backwards Compatible With 2003


1. Open Excel 2007 and select the 'Office' button. Select 'Excel Options.' The Excel Options dialog box appears.
2. Select the 'Save' option on the left side of the Excel Options dialog box. Select 'Excel 97 -- 2003 Workbook' in the 'Save Files in this format. Click 'OK.'
3. Click the 'New' icon on the Quick Access Toolbar. A new Excel 2007 workbook opens displaying the Excel 2003 compatibility mode message.
Read more ►

How to Make Lower Case Data All Caps in Excel 2007


1. Find the cell that contains the lower case information. Make note of the cell number. Cells in Excel are marked by a letter (column) and number (row). The first cell in a spreadsheet is A1.
2. Click in an empty adjacent cell where you want the upper case information to go. Formulas take existing data and change it based on the type of formula used. This information is then saved in its own separate cell where the formula was typed. Do not enter the formula in the cell that contains the lower case information. If you type the formula in the lower case cell, the words are deleted by the formula text.
3. Enter the formula '=UPPER(cell number)' into the cell. Use the cell number for the lowercase information. Do not enter the quotation marks.
4. Press the tab or enter button to exit the cell.
Read more ►

How to Recover Deleted Excel Files


Enable Automatic Recovery and Saving
1. Open Excel and select 'File,' 'Help,' and then 'Options.'
2. Select 'Save' and set the number of minutes between automatic saves in the provided check box.
3. Check the box to 'Keep the last autosaved version if I close without saving' if it is not already checked.
Recover Earlier Versions of an Excel Worksheet
4. Open Excel and select 'File.' If you are recovering an existing worksheet, select 'File,' and then 'Open' to select that worksheet.
5. Select 'Recent' to recover a brand-new file that you have not yet saved. Select 'Info' to recover an existing worksheet.
6. Select 'Recover Unsaved Workbooks' to recover the new worksheet. For existing worksheets, select 'Versions.'
7. For new worksheets, select the draft from the pop-up windows that appears. For existing worksheets, select the version that lists 'when I closed without saving,' and then click 'Restore' to make this version the one that is current. This overwrites any changes in the opened file to the last automatically saved content in the worksheet.
8. Select 'File,' and then 'Save' to avoid any further data loss.
Use Document Recovery
9. Recover your worksheet from the Document Recovery pane appears, which appears if you haven't enabled the auto-save feature in your Microsoft Office products.
10. Click the arrow next to the worksheet you need to recover. Select 'Open' to view the recovered worksheet; select 'Save As' to open the worksheet and create a fresh version and select 'Delete' if you don't need the recovered version.
11. Select 'File,' 'Save' to avoid any further data loss.
Read more ►

How to Use Autofilter in MS Excel


1.
Look at your data and decide what columns (or rows) you want to be able to sort with. Highlight those columns or rows and select 'Data' on the main menu. Then select 'Filter' and then click on 'Autofilter.'
2.
You now have drop down arrows in each title box. Each arrow can be clicked on to filter your data according to that column. Whatever you click on will become the only data visible.
3.
If you go to the 'DRAFT' title cell and click on the arrow, then click on '3/25/2008' in the autofilter column, you will be left only with data that matches.
4.
Notice that the arrow in 'DRAFT' is now blue. This tells you that the spreadsheet has been filtered by that column's data.
5.
You can apply additional filters. Click on the arrow in the 'WRITTEN' title cell, then click on '3/28/2008.' You now have only data that meets those two criteria, drafted on 3/25 and written on 3/28. You notice that both of those arrows are now blue.
6.
There are two ways to go back to your original data. You can remove all the autofilters. To remove all the autofilters at once, click on 'Data' then click on 'Filter' then click to remove the check mark on 'Autofilter.' This will restore all your data and remove the check boxes.
If you just want to restore your data one column at a time, click on the arrow, then click on 'all.' All the data in that column will be visible again.
The illustration shows both methods.
Read more ►

Thursday, May 26, 2011

How to Email One Sheet in an Excel 2007 Workbook


1. Open the Excel file that you want to send. With the tabs at the bottom, find and click on the worksheet in the file that you want to email.
2. Click on the 'File' menu, and select 'Send to.' Select 'Mail Recipient.'
3. Pick the selected worksheet in the notification dialog box that appears. Click on the 'OK' button.
4. Type the email address of the recipient in the 'To:' field. Write a message, and click on 'Send this Sheet.'
Read more ►

How to Make an Address Book in Excel


1. Click the 'Start' menu and go to 'All Programs.' Select 'Microsoft Office' followed by 'Microsoft Excel' to open Excel.
2. Type 'Name' in cell A1 of the spreadsheet, type 'Address' in cell B1 and type 'Phone Number' in cell C1.
3. Enter the data for your address book, starting in the second row. Enter the information based on the headers you create in Steps 2 through 4, so column A has the person's name, column B has the person's address and column C has the person's phone number. If you plan to alphabetize your address book, you may want to enter the names in a last-name-first format.
4. Click and hold the left mouse button on column A and move the cursor to column C to highlight columns A, B and C. Click 'Home' and 'Format.' Select 'AutoFit Column Width.' This makes all the columns fit to the longest text in a cell in the column.
5. Highlight A, B and C again. Click the 'Data' tab. Click the button with an A above a Z and the arrow pointing down. This sorts the data by the person's name in alphabetical order.
6. Click the 'Office' button and 'Save' to save your address book.
Read more ►

How to Alphabetize in Excel 2003


1. Open Excel 2003 and select a worksheet containing data. Click 'File' on the menu bar and select 'Open.' Search your files for the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Highlight the range of data that you want to sort. Include the column headers in your highlighted range. The column headers are important, for when you decide which columns you want to sort.
3. Select 'Data' --> 'Sort.' The Sort dialog box appears. In the 'Sort By' drop-down list, select a column header that you want to sort by. Select 'Ascending.' Click 'OK' to alphabetize the data.
Read more ►

How to Learn MS Excel Fast


1. Open Microsoft Excel or download a free trial program. Explore the in-program tutorial options by clicking the 'File' tab in the top-left corner of the screen and selecting 'Help.' Double-click the 'Getting Started' button to open the Microsoft Excel website or click the blue question mark 'Microsoft Office Help' button to open a searchable database of information. Type in a word or phrase such as 'cells,' 'sorting data' or 'columns.'
2. Visit the Microsoft Excel website for Excel tutorials, videos, plus setting-up and getting-started instructions. The Microsoft Excel support center (see Resources) offers information on formatting, filtering, making charts, using the worksheets (also known as spreadsheets), macros, importing data and lists the top issues Excel users report experiencing.
3. Read an Excel introductory book (be sure to purchase the correct version of Office/Excel, such as Excel 2010, the latest version as of November 2010). Options include 'Excel 2010 For Dummies' by Greg Harvey (who also wrote 'Excel 2010 All-in-One for Dummies'), 'Learn Excel 2007 Essential Skills with the Smart Method' by Mike Smart, 'Microsoft Excel for Beginners' by Web Wise Seminars or 'Excel for Beginners' by D. Larisch. Look for a book with plenty of screenshots and step-by-step tutorials.
4. Enroll in an Excel class, offered around the country at many community colleges and schools. Adult education or continuing education courses are short-term and usually require no prior Excel experience or education. Options include the Wake Technical Community College in Raleigh, North Carolina, offering a beginning Excel 1 class, plus options to further education with intermediate Excel training.
Read more ►

Wednesday, May 25, 2011

How to Make Trend Charts With Excel


1. Create a chart with names and grades. This can be mock information. In cell A1, type 'Names' and in cell B2 type 'Grade'. Enter the names and grades for 10 students.
2. Click the Insert tab. Click the down arrow under the Column option. Select the first option under the 2-D Column row. A chart will appear.
3. Right-click on one of the bars in the graph. Go to Add Trendline.
4. Select the Linear radio button, then click 'Close.' A trendline has been added to the chart for a trend chart.
Read more ►

How to Apply a Theme to a Worksheet


1. Open the Excel worksheet.
2. Click the 'Page Layout' tab on the command ribbon.
3. Click 'Themes' in the 'Themes' group. A gallery of 'Built In' theme samples appears.
4. Point over the theme samples to preview the effect. The column and row headers also change in format.
5. Click the preferred document theme. Examples include 'Concourse' with red hyperlinks and 'Newsprint' with 'Times New Roman' fonts.
6. Type the data in the worksheet. The data formats with the preferred theme.
Read more ►

How Can I Put Text a Formula Into the Same Cell in an Excel Spreadsheet?


Test the theory
1. Open a new workbook or spreadsheet and click in cell A1. Type the number 2.
2. Click in cell A2 and type the number 2 there.
3. Click in cell A3 and type:=SUM(A1,B1)
4. Press 'Enter' or click elsewhere on the spreadsheet and the cell will show the number 4 (as this is the sum of 2 2). Note that the function bar (the long white strip next to the 'fx' symbol) still shows the formula '=SUM(A1,B1).'
5. In the function bar, place your cursor after the equal symbol and type:'The sum of 2 2 is 'Include both quotation marks and the space after 'is' but no other spaces.The complete formula should read='The sum of 2 2 is 'SUM(A1,B1)
6. Click anywhere else on the table and cell A3 will now show: 'The sum of 2 2 is 4.'
Read more ►

How to Format Numbers in Excel 2003


1. Open your Excel worksheet and select a group of cells. Press the left mouse button and drag over the cells.
2. Click 'Format' on the top menu bar. A list of options for formatting your worksheet will appear.
3. Click on the 'Cells' option. This will open a window of tabbed options for formatting cells.
4. Click on the 'Numbers' tab (it's the default tab). You will see a list of categories of numbers on the left and options for formatting them on the right.
5. Click on the category you want to format (Number, Currency, Accounting, etc.) and choose how you want these numbers to appear using the options provided on the right.
6. When you are done formatting, click 'OK.'
Read more ►

Blogger news