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...
Browse » Home » Archives for May 2011
Saturday, May 28, 2011
How to Work With Pictures in Excel 2007
Inserting the Picture File1. 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 Picture5. 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...
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...
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 messa...
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...
How to Recover Deleted Excel Files
Enable Automatic Recovery and Saving1. 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 Worksheet4. 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...
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'...
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 Shee...
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....
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...
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...
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 cha...
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 the...
How Can I Put Text a Formula Into the Same Cell in an Excel Spreadsheet?
Test the theory1. 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...
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...
How to Drop Decimal Places Without Rounding in Microsoft Excel
1. Open a blank worksheet in Excel and type a decimal number in the A1 cell. Use this worksheet for practice before applying the procedure to an actual file.2. Type the 'integer function' into cell B1 as follows: f=INT(A1). The integer function lops off the decimal value of a number leaving only the whole number digits to the left of the decimal place. The formula in cell B1 instructs Excel to find the integer value of cell A1 and place that value in cell B1.3. Click any other cell in the spreadsheet to exit from cell B1. The cell will now display the integer value of the number in A1. For...
Tuesday, May 24, 2011
How to Make Bullets in Excel Entries
Insert a Bullet to a Cell1. Open the Excel worksheet.2. Double-click the cell where you wish to insert the bullet symbol.3. Press “Alt” and “7” using the numeric keypad on the keyboard. A black bullet appears in the cell. To create a bullet with a white interior and a black outline, press “Alt” and “9” on the numeric keypad. If you prefer a different bullet style, click the “Insert” tab on the command ribbon. Click “Symbol” in the “Symbols” group. Select a font. Select the bullet symbol. Click “OK” and “Close.”4. Add more bulleted lines in the same cell by pressing “Alt” and “Enter.”...
How to Check Each Control of a Group in Excel VBA
1. Click the 'Developer' tab's 'Visual Basic' button to enter the VBA programming environment, then click the 'Insert' menu's 'Userform' item to create a new form on which to place controls. If the 'Developer' tab does not appear among your menu choices in Excel, you may need to turn on its display in Excel's options.2. Click the 'Button' control on the tool box, then drag on the userform to create a button. Click the 'OptionButton' control from the tool box, then drag on the userform to create an option button. Right click this button and click 'Properties' to display a list of attributes...
How to Stop Rounding in Excel
1. Open the Excel workbook that currently uses rounded numbers. Click on the affected worksheet.2. Highlight the cell(s) that hold the numbers. Do so by clicking a single cell, dragging the cursor across several cells, clicking a row number or a column letter.3. Right-click on the highlighted cell(s). Select 'Format Cells' from the menu, which opens the Format Cells dialog box.4. Select the 'Number' tab. Click 'Number' in the Category pane. Press the up arrow next to the Decimal Places box to increase the number of digits after the decimal, such as '5' or '7' places after the point.5....
How to Plot a Titration Curve on Excel
1. Open a new worksheet in Excel, and create two column headings with the titles 'Titrant Amount' and 'pH Level.' Fill out the two columns with the data you have measured.2. Highlight your entire data set, including your column headings, by clicking and dragging your mouse across it.3. Click the 'Chart Wizard' button in the toolbar. Click the XY (Scatter) chart type to select it, then select a 'Scatter with data points connected by smoothed Lines' chart. Click 'OK'; Excel will generate the chart with the smoothed titration curve on top of the data seri...
Monday, May 23, 2011
How to Unlock Spreadsheets
1. Click on the 'Start' button, and then click on 'All Programs.'2. Click on 'Microsoft Excel.' The Microsoft Excel program will open.3. Click on 'Review,' and then click on 'Unprotected Sheet.' You may be prompted to input your password if you used one to lock your spreadshe...
How to Add a Footer in Excel 2003
1. Launch Excel 2003 and open the file to which you want to add a footer. Click once on the worksheet.2. Click the 'View' menu and select the 'Header Footer' option. This action will launch a separate dialogue window.3. Click the 'Header Footer' tab on the Page Setup window. Click the 'Custom Footer' button. This action will launch another dialogue window.4. Click once in one of the three available footer sections (left, center and right). Type the desired text in any of the fields or use the appropriate buttons to add the date, page number or other spreadsheet information, as desir...
How to Uninstall an Excel 2007 Add
1. Start Microsoft Excel 2007.2. Click the Microsoft Office Button located in the upper left corner.3. Click on 'Excel Options' and choose 'Add-Ins.'4. Choose 'Excel Add-Ins' in the 'Manage' box and click 'Go.'5. Clear the check box next to the add-in or add-ins you wish to uninstall in the 'Add-Ins available' box. Press 'OK' when finished.6. Close Excel and all other programs.7. Open the 'Control Panel' from the 'Start Menu' and double-click the 'Programs/Programs and Features' icon (Vista) or the 'Add/Remove Programs' icon (XP).8. Choose Microsoft Excel from the list and click...
How to Create Multiple Formulas for the Same Space in Excel
Parenthetical Nesting1. Locate the cell in which you would like to create multiple formulas.2. Press the equal button on the keyboard to start a formula in that cell.3. Combine multiple formulas into one nested formula using parenthesis organization. It is often necessary in Excel to arrive at a formula result after the processing of several intermediate formulas. While these could be split into individual cells, Excel allows many formulas to be combined into one. For example, if one formula requires that a cell range be added together, and another formula must subsequently multiply the...
How to Remove the Blue Titlebar From UserForm in Excel 2003
1. Launch Microsoft Excel 2003. Press the “Alt” and “F11” keys on your computer keyboard at the same time to launch the Microsoft Visual Basic Editor application inside Excel.2. Click “Insert” in the main navigation menu. Select “UserForm” from the drop-down menu.3. Double-click the “UserForm1” window to open the code window. Highlight and delete any code in the window.4. Paste the following code into the window:Option ExplicitPrivate Sub UserForm_Initialize()Call RemoveCaption(Me)End Sub5. Click “Insert” in the main navigation menu. Select “Module” from the drop-down menu.6. Paste...
Sunday, May 22, 2011
How to Modify Microsoft Excel
Microsoft Excel 20071. Modify Excel 2007 by changing the Quick Access toolbar, Themes and Styles to meet individual needs. Right-click on the three default buttons (\'Save,\' \'Undo\' and \'Redo\'), and select \'Customize.\' A two-pane window will open.2. Select commands for the Quick Access toolbar. The left pane accesses all available Excel commands; the right pane shows the existing three buttons. Highlight a command and press the \'Add\' button to move the command to the Quick Access pane. Controls are available to move commands up and down, add the separator bar creates a group and organizes...
How to Convert Inches to Millimeters in Excel 2003
1. Enter your measurement data (in inches) into Excel, and label the column so that you know what units the data are in (for example, 'Length (inches)'). Here you can assume that the first data point is in A2, and the rest follow in the same column (A3, A4, A5).2. Label the next column so that you know that the data is expressed in millimeters (for example 'Length (mm)').3. Enter the following formula in cell B2:=CONVERT(A2,'in','mm')This tells Excel to look up the value in cell A2, and convert it from inches to millimeters.4. Copy and paste this formula down so that all your data are...
How To: Custom Name for the X Y Axis in Excel 2007
1. Open your Excel document and click on a chart to edit it.2. Click the 'Layout' tab on the Microsoft Office Ribbon at the top of the Excel window.3. Click the 'Axis Titles' drop-down menu on the Layout tab, then select 'Primary Horizontal Axis Title' or 'Primary Vertical Axis Title' and click on a display option. For example, click 'Horizontal Title' to display the axis title horizontally.4. Type a name for the axis in the 'Axis Title' text box and press 'Ente...
Saturday, May 21, 2011
How to Drag Sum Formulas From Cell to Cell in Microsoft Excel 2003
1. Select the cell with the sum formula that you wish to drag. To select a cell that you wish to drag a sum formula from, you must first have a cell that contains a sum formula. You will need to left-click on the cell that you wish to drag the formula on, to highlight and activate that cell.2. Activate the formula drag corner. To drag a formula you can easily do so by using the cursor to highlight the lower right corner of the cell, where a drag box will appear.3. Drag the formula to the new cells. To drag the sum formula across the span of other cells, left-click on this drag box and hold...
How to Make Drop
1. Open Excel. Click on the Windows icon at the top of the the window and scroll down to 'Open.' Click on the command and browse through the folders on your computer to find your list. Click on it once to select it and click on 'Open.'2. Click on a blank sheet in the workbook (use the tabs on the bottom to select a blank sheet). Type the entries you would like to see in your drop-down list in a column with no spaces in between the entries.3. Highlight the list so it is all selected. Click in the name box (this is a text box next to the function bar) and give your list a name (for example,...
How to Recover a File After I Choose Not to Save the Changes
1. Avoid closing out the program after you have edited the document but want to return it back to normal. You may click 'Ctrl' and 'Z' or 'Apple' and 'Z' (when using a Mac computer) and the document moves back to a previous step. Continue pressing this until you remove all of the edits you want. You may also click 'Edit,' 'Undo' to perform the same actions.2. Launch the software back up if you have already closed the document but did not save the changes. Click the 'File' tab and you may find all of the 'Recent' documents displayed. Choose the document you recently have been working on and...
How to Find Data Source for Excel 2003
1. Open your file in Excel 2003. Click the 'Data' tab near the top of the Excel window.2. Click the 'Connections' icon right below the 'Data' tab. Click the 'Properties' button on the right side of the 'Workbook Connections' form.3. Click the 'Definition' tab on the 'Connection Properties' form. View the 'Command text' box at the bottom of the form to see the data source for the Excel fi...
Friday, May 20, 2011
How to Open Xlsx in Excel 2003
1. Access the Microsoft Downloads website. Type 'Compatibility Tool' in the search box. Download the Compatibility Tool. Once it has downloaded to your computer, double click the execution file and follow the installation instructions. Click 'OK' once the installation is complete.2. Open Excel 2003 and locate an Excel 2007 workbook. Click 'File' on the menu bar, then click 'Open.' Browse your files and locate the workbook. Click the workbook, then click 'Open.'3. View the xlsx workbook in Excel 2003. You can work with this new format since the compatibility tool is install...
How to Change the Language in Microsoft Excel
1. Click the Microsoft 'Office' button in Excel and click the 'Excel Options' button to open a dialog box.2. Click the 'Popular' tab on the left side of the screen if it isn't already selected.3. Click the 'Language Settings' button. A new window opens.4. Click any language on the left side of the screen that you want to be able to work with. Click the 'Add' button to move that language to the box on the right, enabling it for use in Excel and your other Office products.5. Click the down arrow in the Primary Editing Language section to change the default language for Excel and all of...
Thursday, May 19, 2011
How to Add the Sum Function to an Excel 2003 Toolbar
1. Open Excel 2003 and select 'Tools' on the menu bar. Select 'Customize.' The Customize dialog box appears. Click the 'Toolbars' tab. Select 'New.' The New Toolbar dialog box appears. Type a name for your new toolbar and click 'OK.'2. Review your new floating toolbar. Click the 'AutoSum' button on the standard toolbar to reveal a drop-down list of functions. Click 'SUM.'3. Drag 'SUM' from the standard toolbar to your new floating toolbar. Click 'Close' to close the Customize dialog b...
How to Get a Running Total by Subtraction in Excel 2007
1. Enter the numbers you want to subtract from beginning total in a column on the left. For example, if you want to subtract 10, 14, 19 and 16 from 1000, you would place 10, 14, 19 and 16 in cells A2, A3, A4 and A5. Place the beginning total in a second column. For example, 1000 would go into cell B1.2. Write the formula for the running subtraction, or balance. In cell B2, type '=SUM(B1-A2)'. This will subtract 10 from 1000 and put the result, 990, in cell B2.3. Copy the formula to the lower cells to create a running total. Click on cell B2. Move the cursor to the bottom right corner, then...
Wednesday, May 18, 2011
How to Do Percentages
1. Divide the number (N) by 100 to obtain a decimal, and then move the decimal point two places to the right to obtain your percentage (P).For example, if you have 100 items, what percent is N, where N=24?N / 100 = P24 / 100 = .24 or 24%2. Convert a fraction or decimal to a percent by multiplying by 100.For example, 1 girl (N) out of 5 (T) receives an equal share of the money from a bake sale. Divide the given amount (N) by the total amount (T) and multiply by 100 for the percent (P).N/T x 100 = P1/5 x 100 = 20 or 20%3. Convert a percentage to a fraction by dividing by 100 and then simplifying.20%...
Tuesday, May 17, 2011
How to Create a Histogram in Excel
1. Type or import data into an Excel spreadsheet. Organize data into columns representing the 'Input Range,' representing data range on the y-axis, and the 'Bin Range,' representing frequency values on the x-axis.2. Verify the 'Analysis ToolPak' option under Tools/Add-Ins has been checked or activated. If 'Data Analysis' option isn't available, please see the 'Tips' section for activating the Analysis ToolPak under the 'Add-Ins' menu.3. Click on the 'Data Analysis' option under the 'Tools' menu.4. Open the 'Histogram' option window.5. Select 'Input Range' by dragging your mouse over...
How to Make a Spreadsheet in Excel 2003
1. Launch Excel 2003 and open a blank document. Click the 'File' menu and select the 'New' option, which opens a task pane to the right of the screen. Click the 'Blank Workbook' link in the 'New' section.2. Enter your desired data into the individual spreadsheet cells. You may input text or numbers into cells. Format your data, as necessary, by highlighting the appropriate cells and clicking the 'Format' menu. Select the 'Cells' option to open a separate window. Click one of the options in the 'Category' section of the 'Number' tab -- for example, 'Number' to format the data in the selected...
How to Calculate Age From Date of Birth in Excel
1. Write the date of birth in cell A1. For example, write 2/9/1967.2. Type the TODAY function in cell B1. The TODAY function is:=TODAY()This will always return the current date.3. Type the following into cell C3:=(B1-A1)/365.25.The person's age in years will appear once you hit 'Ente...
Monday, May 16, 2011
How to Import XLR Files
Microsoft Excel1. Go to your 'Start' menu. Click 'All Programs,' 'Microsoft Officer Starter (English),' and then click 'Microsoft Excel 2010' to open Excel.2. Click the 'File' menu and then click 'Open' to browse your hard drive for the XLR.3. Browse your computer for the XLR you want to import and then click 'Open' to import the spreadsheet to Microsoft Excel.OpenOffice Calc4. Download and install the OpenOffice Suite from OpenOffice.org. Calc is the name of the spreadsheet software that is bundled with OpenOffice.5. Click 'Start,' 'All Programs,' 'OpenOffice' and then click 'OpenOffice...
How to Calculate the Time Difference in Excel
Calculate Differences Between Hours1. Open a new Microsoft Excel workbook.2. Select the top cell in column 'A' and type 'Start Time,' and press 'Enter.'3. Select the top cell in column 'B' and type 'End Time,' and press 'Enter.'4. Select the top cell in column 'C' and type 'Time Difference,' and press 'Enter.'5. Enter the start and end times in the appropriate columns by selecting the cell under the appropriate column and typing the time function, '=TIME(hour, minute, second)' and pressing 'Enter.' To enter times without using the TIME function, skip this step.6. Format the cells for...
How to Create a Bar Graph From an Excel Spreadsheet
Creating a Chart1. Start Microsoft Excel, and open the file you want to use to make a chart.2. Drag the cursor over the columns you want to show in your chart.3. Open the Insert menu, and select Chart. You can also select the chart icon on the toolbar.4. Select the type of chart you want to make, such as a line graph or pie chart, from the list on the left.5. Press Finish if you do not want to label or format your chart.6. Press Next to label and format your chart.Formatting Your Graph7. In the box that appears after you clicked Next, click the Series tab.8. Click on Series1, and...
How to Secure Excel Data
1. Turn on your computer, open Excel, and find the spreadsheet that you need to secure.2. Click on 'Tools' in the toolbar and then select 'Protection'.3. Now you have several options on how to protect the spreadsheet. If you have a single sheet, then choose 'Protect Sheet'. If you have several tabs on the spreadsheet then select 'Protect Workbook'. Finally, if you want the recipients to be able to alter data, choose 'Allow users to edit ranges'. In this example you should choose 'Protect Sheet'.4. When the Protect Sheet windows opened, type in a password in the password box, then check...
How to Use Excel's Covar Function
1. Learn the syntax for Covar. It is Covar (array_1,array_2) where array_1 is the first range of integers and array_2 is the second range of integers. The covariance of these two arrays will be returned.2. Study the restrictions on the arguments. They must be arrays, references, names or numbers. If the arguments contain empty cells, logical values or text, those values will be ignored. However, the value zero is included.3. Compare the number of values between array_1 and array_2. If they are not equal, Covar will return the #N/A error value. If either array is empty, Covar will return...
Sunday, May 15, 2011
How to Generate Random Numbers in Excel 2003
1. Open an Excel spreadsheet.2. Click on a cell where you want your range of random numbers to start.3. Enter the following into the formula box:=RAND()and hit 'Enter.' This is the default RAND function and will generate the first random number between 0 and 1 to 9 decimal places. You can expand the cell to see all 9 decimal places. To generate a whole random number between numbers other than 0 and 1, enter the following into the formula box: =INT(RAND()*(b-a)) awhere 'a' is the minimum random number you want and 'b' is the maximum random number you want. For example, to generate random...
How to Change the Default to Excel 2003 Instead of Excel 2007
1. Click on the Office button at the upper left-hand corner of the screen. A menu will come up.2. Click on 'Excel Options' at the bottom of the menu that comes up. A two-pane dialog window appears.3. Click 'Save' in the left-hand side of the pane. On the right-hand side of the pane, the panel will change to show save options.4. Select 'Excel 97-2003 Workbook (*.xls)' from the menu on the first option on the panel.5. Click 'OK' at the bottom of the pan...
Saturday, May 14, 2011
How to Specify That the Cell Address Will Not Change in Excel
1. Select the cell that contains the formula you want to change. Place the cursor in the formula bar, located directly above the spreadsheet, in order to alter the formula.2. Insert a dollar sign, '$,' in front of the reference's column or row in order to make it an absolute reference. For example, you can enter '$A1,' 'A$1' or '$A$1.' Only the part of the reference with the dollar sign in front of it becomes absolute. '$A1' will always reference column A, but the row will change if the formula moves.3. Press 'Enter' or select another cell on the worksheet to complete the changes to the...
How to: Watermarks in Excel 2007
1. Click the 'Page Layout' tab, then click the small down arrow at the bottom right of the 'Page setup' panel. Excel will display a dialog box with options for setting the header and footer of your workbook. Inserting a picture in a header creates a watermark for the workbook.2. Click the 'Header/Footer' tab, then click the 'Custom header' button. Excel will display a dialog box allowing you to specify different headers for the left, right and middle sections of your workbook.3. Click in the 'Center' text box, then click the icon above the text box that appears as a mountain with a rising...
How to Remove Duplicate Values From a List in Excel
Excel 2007 and 20101. Open your Excel document. Click on the column title above the column from which you want to remove duplicate values. If you do not have column titles, click on any of the data cells in the list. You can highlight multiple columns.2. Click on 'Data' in the menu bar, then click on the 'Remove Duplicates' button in the Data Tools section. This will open the 'Remove Duplicates' dialogue box. If you highlighted more than one column, you can use the check boxes to specify which columns or lists you want Excel to remove duplicate values from.3. Click 'OK' to remove all duplicate...
How to Remove a Formula or Program in an Excel Spreadsheet
Removing Custom Functions Macros1. In the Excel file containing the custom features you want to remove, click on the 'Developer' tab and select 'Visual Basic' in the ribbon. This opens a new window.2. Look at the window on the left titled 'Project - VBAProject.' The VBAProject may have a different name; however, the initial 'Project -' should still be part of the title. In this box you see 'Microsoft Excel Objects' and 'Modules.'3. Double-click the first listed item under 'Modules.' This brings up a new window containing VBA code. You can remove individual custom functions by deleting everything...
Friday, May 13, 2011
How to Remove Color Fills from Cells in Microsoft Excel 2003
1. Activate the cell with the color fill. To activate a cell that you wish to change the color fill in, simply left-click on that cell.2. Access the color fill palette. The color fill palette is located on the command bar and appears as an icon of a paint bucket spilling out paint. To access this palette, simply left-click on the icon and the palette will open.3. Remove the color fill. To remove the color fill, left-click on “No Fill” inside of the color fill palette.4. Make sure to save your changes. You can easily save your spreadsheet by pressing the hotkeys “CTRL-...
Thursday, May 12, 2011
How to Take Out Hyphens From a Cell in Excel 2007
1. Select a blank cell next to the desired cell containing text (source cell).2. Type '=substitute(' and then click the cell containing text (source cell).3. Type a comma followed by a space and a hyphen between quotation marks. Type a comma and then press the 'Space' key. For example, the formula in the cell will now read '=substitute(A3, '-', '4. Type the desired characters to replace the hyphen, enclosed in quotation marks, or simply type two quotation marks to remove the hyphens. Type ')' to complete the formula. For example, to remove hyphens and replace them with a period, the formula...
How to Use Excel's Indirect Function
1. Open your worksheet and find the cells that you want to add value to with Indirect functions.2. Access the 'function text box' for a specific cell. In some Excel programs, the text box is visible at the top of the page. If not, you'll have to go to the menu, select 'Insert' and then 'Function.'3. Utilize the function INDIRECT the same way as other functions for a direct cell reference. If you want the content of cell B6 to echo the content of cell C5, your command for B6 in the function text box will look like this: INDIRECT(C5)4. Use INDIRECT nested in brackets for in-depth cell referencing....
How to Create a Frequency Histogram in Excel
1. Type the title of your data in cell A1 and enter your data below it. For example, if you were creating a frequency histogram of grades on a test, you would type 'Grades' in cell A1 and then type the numerical grades in the cells below.2. Enter the bin values that you want to use in a separate column. The bins are the values that will be grouped together. For example, if your grading scale was: 90 and up is an A; 80 to 89 is a B; 70 to 79 is a C; 60 to 69 is a D, and 59 and below is an F, you would enter 59, 69, 79, and 89.3. Select the 'Data Analysis' option from the Data tab then select...
How to Eliminate Duplicate Records in Excel
1. Open the Excel spreadsheet.2. In the 'Data' tab, choose the 'Advanced' button in the group marked 'Sort Filter.' (In Excel 2003, click 'Data,' then 'Filter,' then 'Advanced Filter.')3. Confirm that your data range--all the rows you want to filter--is noted in the dialog box that appears. If it isn't, simply click in the first cell of the range (the dialog box will shrink to the cell selection field temporarily) and drag the mouse cursor down to select. When you release the button, the dialog box will restore.4. Eliminate duplicate entries by clicking on the 'Data' tab, then clicking...
Wednesday, May 11, 2011
How to Create an Auto Loan Calculator
How to Start the Auto Loan Calculator1. Click on cell A1 and type “Purchase Price.”2. Click on cell A2 and type “Down Payment.”3. Click on cell A3 and type “Amount of Loan.”4. Click on cell A4 and type “Interest Rate.”5. Click on cell A5 and type “Number of Monthly Payments.”6. Click on cell A6 and type “Monthly Payment.”7. Click on cell B3 (currency amount of loan) and, with the cell highlighted, left-click in the formula bar area and enter: =B1-B2.8. Click on cell B6 (monthly payments of loan) and, with the cell highlighted, left-click in the formula bar area and enter: =PMT(B4/12,B5,B3).9....
How to Restore a Deleted Worksheet in Excel
1. Click 'File > Save As...' to save your workbook with a different file name.2. Click 'File > Open' and select your original workbook, that still contains the deleted worksheet.3. Right-click the sheet you want to get back, and choose 'Move or Copy...'4. Select your newer workbook with the missing sheet from the drop-down list labeled 'To book.'5. Press 'OK.' Your newer workbook now has the recovered worksheet in ...
How to Convert Quattro Pro to Text Delimited
1. Click 'Start' on the desktop and click 'Settings,' 'Control Panel' and 'Add/Remove Programs.'2. Click 'Microsoft Excel 2003' and click 'Add/Remove.' Click 'Add or Remove Features' and click 'Quattro Pro 5.0 Converter.'3. Click 'Run From My Computer' and click 'Update Now.' Launch Microsoft Excel, and click the File menu at the top of the Excel window.4. Click 'Open' and locate the Quattro Pro file with the WB1 file extension. Double-click the file to open it in Excel.5. Click the File menu and click 'Save As.' Click the 'Save as Type' drop-down menu, and click '*.txt.'6. Click 'Save'...
How to Multiply Cells in Excel
Multiply with Cell References1. Click on cell A1. Type the number 12 into cell A1. Type the number 6 into cell B1.2. Click on cell C1. Type the equal sign (=) at the beginning of your equation.3. Click on cell A1. This action places A1 in cell C1 on the right side of the equal sign.4. Type an asterisk (*) sign into cell C1.5. Click on cell B1. This action places B1 into cell C1, after the asterisk.6. Press the 'Enter' button on the keyboard or click on the check mark on the tool bar to display a result. The number 72 should appear in cell C1.Multiply Numbers in a Cell7. Click on...
How to Protect a Worksheet in Excel
1. Open the Excel workbook in which you want to convert and replace the date data with day-of-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter some sample data.2. Select 'Tools' from the main menu.3. Select 'Protection' from the drop-down list.4. Select 'Protect Sheet' from the drop-down menu.5. Left-click with your mouse in the box labeled 'Protect worksheet and contents of locked cells.'6. Enter a password to unprotect the sheet in the 'Password to unprotect sheet' field. If you do not enter a password, any user will...
How to Do a Budget Spreadsheet With Excel 2003
1. Press the “Windows” and “R” keys on your keyboard simultaneously, type in “Microsoft Excel,” and then click “Run” to open Excel 2003.2. Click on the first square at the top left of the spreadsheet (A1) and then type in “Expense.”3. Press the “Tab” key on your keyboard, this will put you on the square directly to the right of A1 (B1) and then type in “Sub Total.”4. Press “Tab” to move to C1 and then type in “Actual Money Spent.”5. Click on A2 and then type in “Savings.”6. Press the “Enter” key on your computer keyboard, this will take you to the square directly below A2 (A3) and...
Subscribe to:
Posts (Atom)