May 2011 - Excel

Browse » Home » Archives for May 2011

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.

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.

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.

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.

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.

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.

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.

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.'

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.

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.

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.

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.

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.

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.'

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.'

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 example, if A1 contains the number 10.54, cell B1 will display the number 10.
4. Type another decimal number in cell A5.
5. Copy cell B1 and paste it into cell B5 to put a copy of the integer formula in B5. Click out of the cell to show the integer value of A5 in B5. As you copy and paste the integer formula, Excel automatically adjusts the parameters to find the integer value of A5 rather than A1.
6. Use the integer function in an actual spreadsheet to drop the decimal values of a number and leave only the whole number.

Tuesday, May 24, 2011

How to Make Bullets in Excel Entries

Insert a Bullet to a Cell
1. 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.” The cell will contain a line break. Repeat the “Alt” and “7” or “Alt” and “9” keyboard shortcut to insert the bullet symbol on the new line.
Insert a Bullet to a List
5. Open the Excel worksheet that contains the list.
6. Add a bullet to a list in one cell by double-clicking the cell.
7. Place the insertion point on the line where you wish to insert the bullet.
8. Click the “Insert” tab on the command ribbon.
9. Click the blue “Symbol” button in the “Symbols” group. A chart with symbols displays.
10. Click the “Font” text box arrow. Select your preferred font. For example, “Arial Narrow.”
11. Scroll to view the symbols. For example, certain fonts display squares, diamonds, flags and arrows. Click your preferred bullet symbol.
12. Click “Insert.” Click “Close.” The selected symbol appears on the selected line in the cell.

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 for the control.
3. Click in the 'Enabled' row of the 'Properties' window, then type 'False' to indicate that the option button is unable to process input. Note that the command button, which you created in Step 2, is enabled by default.
4. Click the 'Frame' control on the tool box, then drag on the userform. Click the button control you made in Step 1, then press 'Control' and 'X' simultaneously to cut this control to the clipboard.
5. Click the 'Frame' control, then press 'Control' and 'V' simultaneously to paste the button inside the frame. Repeat the cut and paste with the option button to paste it into the frame as well. By placing both controls into the frame, you've made one group that contains both controls. You'll now write code that checks a property of each control.
6. Double click the userform to display the programming window, then paste the following program into the window. This program iterates through each control in the frame, reading the 'Enabled' property you set in step 3, and printing the value of that property.Private Sub UserForm_Click()Dim ct As ControlDim s As StringFor Each ct In Frame1.ControlsIf ct.Enabled = True Thens = ct.Name ' is enabled.'Elses = ct.Name ' is disabled.'End IfMsgBox sNextEnd Sub
7. Click the 'Run' menu's 'Run' command to display your userform, then click anywhere on the form that doesn't have a control. Your program will run, displaying message about the 'Enabled' property described in Step 3.

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. Click the 'OK' button to save the changes to number-rounding.

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 series.

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 spreadsheet.

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 desired.

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 'Change.'
9. Follow the onscreen instructions to select the correct add-in and uninstall it from the list.

How to Create Multiple Formulas for the Same Space in Excel

Parenthetical Nesting
1. 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 sum by a constant, write the combined formula as '=(sum(a1:a15))*15' where the 'a1:a15' represents the cell range and '15' the constant multiplier.
4. Combine arithmetic formulas together into one formula using the standard order of mathematical operation. For example, if you need two separate formulas to calculate the products of two sets of numbers, and a third formula to find the difference in these two outcomes, combine them all in a simple formula as '=a1*3-a2*6'. This could be split into three formulas, but by using standard order of operation, the multiplication is handled first for each product before the subtraction is calculated.
'IF()' Function
5. Press an equal sign in the cell where you would like to create multiple formulas.
6. Type 'if(' to begin an 'IF()' function. These functions allows multiple separate formulas to be run depending on conditions. You can have one cell house at least two formulas that do not interact with each other or depend on each other's results.
7. Determine the condition for the 'IF()' statement. For example, perhaps you wish to calculate a number by another if that number is positive, but calculate it by a different number if the number is negative. Type the condition next, such that the formula currently reads '=if(a1>0', where 'a1' is the cell that determines which formula to use. Type a comma in the formula before proceeding.
8. Type the formula that will occur if the condition is true. The 'IF()' statement will thus currently read '=if(a1>0,a1*10' where 'a1*10' represents one of the two formulas you will include in this one single overall formula.
9. Type a comma followed by the second formula that is run if the condition is false. Close the formula with an ending parenthesis. The formula may read '=if(a1>0,a1*10,a1*12)'. The two formulas that are included in this statement are 'a1*10' and 'a1*12' based on the outcome of the test, 'a1>0'.

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 Sub
5. Click “Insert” in the main navigation menu. Select “Module” from the drop-down menu.
6. Paste the following code into the Module window:Option ExplicitPrivate Declare Function FindWindow Lib 'User32' _
Alias 'FindWindowA' ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As LongPrivate Declare Function GetWindowLong Lib 'User32' _
Alias 'GetWindowLongA' ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As LongPrivate Declare Function SetWindowLong Lib 'User32' _
Alias 'SetWindowLongA' (ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As LongPrivate Declare Function DrawMenuBar Lib 'User32' ( _
ByVal hwnd As Long) As LongSub RemoveCaption(objForm As Object)Dim lStyle As Long
Dim hMenu As Long
Dim mhWndForm As LongIf Val(Application.Version)
mhWndForm = FindWindow('ThunderXFrame', objForm.Caption) 'XL97
Else
mhWndForm = FindWindow('ThunderDFrame', objForm.Caption) 'XL2000
End If
lStyle = GetWindowLong(mhWndForm, -16)
lStyle = lStyle And Not HC00000
SetWindowLong mhWndForm, -16, lStyle
DrawMenuBar mhWndFormEnd SubSub ShowForm()UserForm1.Show FalseEnd Sub
7. Click the “x” button in the top right-hand corner of the Visual Basic Editor window to close the application and all windows. You don’t need to save the contents of the code boxes.
8. Click “Tools” in the main menu. Select “Macro” followed by “Macros.” Select “ShowForm.” Click the “Run” button. The Excel user form now displays without the title bar.

Sunday, May 22, 2011

How to Modify Microsoft Excel

Microsoft Excel 2007
1. 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 commands to meet individual needs. Only one row is possible. When finished, click \'OK.\'
3. Modify the Quick Access Gallery. From the Home Tab, the Quick Access Gallery shows a number of default styles of font weight, size, color and backgrounds. Customize styles by right-clicking on any style and selecting \'Modify.\'
4. Locate and modify Themes on the Page Layout tab. Customizing Themes adds significant flexibility for spreadsheet design in terms of colors, fonts and styles. Colors, Fonts and Effects can be individually modified, then three modified features are saved as a group in a Theme for easy future access.
5. Select the \'Fonts\' button. A window appears with font selection boxes for \'Heading\' and \'Body.\' Select the desired font pair, add a name for the pair and click \'Save.\' In the future, when the Font button is clicked, this pair will show in the \'Custom\' grouping. Creating a font pair automatically changes all of the default styles in the Quick Access Gallery to the theme's body and heading fonts.
6. Modify theme colors by pressing the \'Colors\' button and choosing \'Create new Theme colors\' from the very bottom of the drop-down menu. A window will open, showing the current theme colors for different aspects of the spreadsheet. Colors can be selected from drop-down menus. Two interactive graphics will show the results of the colors selected. The color grouping can be named and saved for future use.
7. Select an effect to be the default with the new colors and fonts. Then press the \'Theme\' button and select \'Save current Theme\' at the bottom of the drop-down window. Give it a name; it will appear in the Custom grouping in the future. Changing the spreadsheet Effects is not possible as part of the Themes. Graphic features can be modified individually when used in the spreadsheet.
Excel 2010
8. Right-click on the Ribbon and select \'Customize the Ribbon.\' Here you can select commands, place them in a new or existing Group, then add them to existing or new Tabs.
9. Use the same instructions from Section 1, Step 2 to modify the Quick Access toolbar. Select commands for the toolbar to speed program usage. When you are finished with the Ribbon and Quick Access toolbar, there will be a command to \'Import\' or \'Export\' both the Ribbon customization and the Quick Access toolbar customization. \'Export\' creates a back-up copy to be placed in a folder that is somewhere other than \'\\Programs\\Microsoft\\Office.\' This ensures that the back-up does not get erased if Office needs to be reinstalled.
10. Restore a lost Ribbon or Quick Access toolbars by importing them from the Customize screen. Both the Ribbon and toolbar modifications are saved in the same export file.

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 converted. Excel will automatically look up the correct cell, so the formula in B3 will be converting the value in A3, the formula in B4 will be converting the value in A4, and so on.

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 'Enter.'

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 as you drag the box to highlight new cells. When you let go of the mouse button, the formula will appear in all of the cells that you highlighted when you used the drag box.
4. Test the formula to assure that you implemented it properly. Make sure that you dragged the formula properly by double-checking the math to assure that the formula is functioning as desired.

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, you can name it which column you will be using the list in).
4. Go back to your list and click in the first cell you want the drop-down list to occur. Click on the 'Data' menu at the top of the screen and click on 'Validation.' Click on the 'Settings' tab when the 'Validation' window opens.
5. Click on 'List' in the 'Allow' box. Type '=' and your list name in the 'Source' box. Click on the box next to 'In-Cell drop down.' Click on the 'Ignore Blank' box if the cell can be left blank. Once it is complete, click 'OK.'
6. Right-click the cell you have just created the list and click on 'Copy.' Highlight the entire column, right-click, and click 'Paste' to make every cell in that column into that drop-down list.

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 the information appears since its last auto save (the computer automatically saves the document in progress, just in case you fail to before the program is closed or freezes).
3. Select 'File,' 'Open' if you never saved any changes to the document and want the original file open. A browse window appears on the computer screen. Select the file you wish to view, then choose 'Open' and the original document loads onto the computer screen, without all of the unsaved edits.

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 file.

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 installed.

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 Office.
6. Click 'OK' to close the window and then click 'OK' again.

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 box.

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 click and drag the cell downward to drag the formula down the column. This will copy the formula to the lower cells so you won't have to rewrite the formula for each cell.

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 = P
24 / 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 = P
1/5 x 100 = 20 or 20%
3. Convert a percentage to a fraction by dividing by 100 and then simplifying.20% = 20/100 = 1/5
4. Convert a percentage to a decimal by moving the decimal two places to the left.20% = 20.0 = .20
200% = 200.0 = 2.0
5. Convert to like measurements before calculating percent. When determining a percentage of differing measurements, you must first have all elements the same.For example, to determine the percent of 2 inches out of 12 feet, first convert the feet to inches.
12 inches = 1 foot
12 x 12 = 144 inches2 inches is what percent of 144?
2 = Percent x 144
2 ÷ 144 = .0138
Move the decimal point two places to the right of multiply by 100 for a percentage of 1.38%.

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 the data you want to include in the analysis.
6. Choose 'Chart Output.'. Your chart will appear within the same Excel worksheet as your data.
7. Use 'Chart Options' and 'Format Data Series' menu to adjust the final appearance of the chart. You can use these options to adjust, size, position and labeling.

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 cells.
3. Use preformatted formulas to calculate data in the spreadsheet. Select a blank cell and click the 'Insert Function' button to the left of the 'Formula Bar,' which opens a separate dialog window. Select a function, such as 'SUM,' from the Insert Function menu and click the 'OK' button. Select the cells that you want to use your formula, according to the required information to complete the selected formula. Click the 'OK' button.
4. Save your spreadsheet. Click the 'File' menu and select the 'Save As' option; select a folder in the resulting dialog window in which to save the XLS file using the 'Look in' menu. Type a name for the file in the 'File name' field and click the 'Save' button.

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 'Enter.'

Monday, May 16, 2011

How to Import XLR Files

Microsoft Excel
1. 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 Calc
4. 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 Calc' to run the spreadsheet program.
6. Click the 'File' menu and then click 'Open' to find the XLR you want to import into OpenOffice Calc.
7. Click 'Open' again to import the XLR file.

How to Calculate the Time Difference in Excel

Calculate Differences Between Hours
1. 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 time by selecting the 'Home' tab on the top menu and clicking the arrow in the 'Font' section to open the 'Format Cells' window.
7. Click the 'Number' tab and select the 'Time' category. Select your preferred format in the 'Type:' box and click 'OK.' Enter times using proper syntax. Examples are '5 p' for 5:00 p.m., '5:42 p' for 5:42 p.m., or if you want to include seconds, '5:42:15 p' for 5:42:15 p.m. To display seconds, be sure to select a format that shows seconds.
8. Calculate the time difference by using the formula end time minus start time, using the 'TEXT' function for proper formatting (h=hour, m=minute and s=second). Select the cell under the appropriate column and type the combined text function and time formula, '=TEXT(B2-A2, 'h:mm:ss')' and press 'Enter.'
9. Select the cell containing the formula and hold down 'Ctrl' and 'C' to copy it. Paste the formula in column C next to all rows that contain start and end times to be calculated.
Calculate Differences Between Dates
10. Open a new Microsoft Excel workbook.
11. Select the top cell in column 'A' and type 'Start Date,' and press 'Enter.'
12. Select the top cell in column 'B' and type 'End Date,' and press 'Enter.'
13. Select the top cell in column 'C' and type 'Date Difference,' and press 'Enter.'
14. Enter the start and end dates in the appropriate columns by selecting the cell under the appropriate column and typing the date function, '=DATE(year, month, day)' and pressing 'Enter.' To enter times without using the DATE function, skip this step.
15. Format the cells for dates by selecting the 'Home' tab on the top menu and clicking the arrow in the 'Font' section to open the 'Format Cells' window.
16. Click the 'Number' tab and select the 'Date' category. Select your preferred format in the 'Type:' box and click 'OK.'
17. Enter dates using proper syntax. Click the cell and type '5-8' and press 'Enter' for dates in the current year. In the year 2010, Excel would read this date as May 8, 2010.
18. Include the year for dates not in the current year. Click the cell and type '5-8-9' or '5-8-09' and press 'Enter.' Excel would interpret this date as May 8, 2009.
19. Enter the full year for dates in other centuries. Type '5-8-1942' and press 'Enter.' Excel would interpret this date as May 8, 1942.
20. Calculate the date difference by using the formula end date minus start date. Type '=B2-A2' and press 'Enter.'
21. Calculate the date difference using workdays only by using the NETWORKDAYS function (=NETWORKDAYS(Start Date, End Date)). Type '=NETWORKDAYS(a2, b2)' and press 'Enter.'
22. Select the cell containing the formula and hold down 'Ctrl' and 'C' to copy it. Paste the formula in column C next to all rows that contain start and end dates to be calculated.

How to Create a Bar Graph From an Excel Spreadsheet

Creating a Chart
1. 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 Graph
7. In the box that appears after you clicked Next, click the Series tab.
8. Click on Series1, and enter a label for your data, such as 'Number of Widgets per Month,' in the Name field.
9. Click on any additional series, and name those as well.
10. Click Next.
11. Select the Titles tab to label your chart and both its x-axis and y-axis.
12. Select the Axes, Gridlines, or Legend tabs to format the look of your chart.
13. Select the Data Label tab to label specific points on your chart.
14. Select the Data Table tab to add a table of chart data under your chart.
15. Click Next.
16. Decide whether you want your chart on a new worksheet or on the same worksheet with your current Excel entries.
17. Click Finish.

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 the appropriate boxes for the options that you need for the spreadsheet. Normally if you are protecting a sheet from being altered you should only check 'Select locked cells' and 'Select unlocked cells'. Click 'OK' to continue.
5. Save the spreadsheet, and if you want extra protection simply choose 'Save as', then click 'Tools' and then 'General Options'. This will open a Save Options window where you can add an additional password and make the document 'Read-Only' as well.
6. Once you have entered the password and selected the 'Read-Only' box, click 'OK' and then 'Save'.

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 the #DIV/0! error value.
4. Calculate the covariance. Covar(X,Y) = The sum of (x - Average (array_1))(y - Average (array_2))/n where x and y are individual values in array_1 and array_2 respectively and n is the sample size.
5. Look at the following example: Data_1 = (3, 2, 4, 5, 6) and Data_2 = (9, 7, 12, 15, 17). Average (array_1) = 4 and Average(array_2) = 10. The sum of (x-4)(y-10) for all values of data_1 and data_2 is (3-4)(9-10) (2-4)(7-10) (4-4)(1-10) (5-4)(15-10) (6-4)(17-10) = 1 6 0 5 14 = 26. 26/5 = 5.2 so Covar (Data_1,Data_2) = 5.2.

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 numbers between 1 and 100, you would enter: =INT(RAND()*(100-1)) 1.
4. Click on that cell, then move your mouse to the bottom right corner until a solid, black cross appears.
5. Drag this black cross down or across the number of cells that you want. For example, if you want to generate 20 random numbers, drag the cursor down 19 additional cells. All the cells, including the first one should now have a new random number.

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 panel.

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 formula.

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 sun over it. This button is the 'Insert picture' tool.
4. Click the button just mentioned, then use the controls in the 'Insert' dialog box that appears to move to a folder on your hard drive containing a picture file you would like to use as a watermark.
5. Click the picture file to select it, then click the dialog box's 'Insert' button to load the picture into the header for your workbook. Excel will display the text '(Picture)' in the 'Center' text box, which indicates it successfully loaded the picture.
6. Click 'OK' to exit the dialog boxes and complete the insertion of the picture into your workbook. Excel will not display the picture while in draft view, which Excel documentation refers to as 'Normal' view.
7. Click the 'View' tab, then click the 'Page layout' button of the 'Workbook views' panel. Excel will display the picture you inserted in the header's center section, but only on the first page of the preview.
8. Click a cell on any of the pages where Excel displays the text 'Click to add data.' Excel will insert your watermark graphic onto the page you clicked.

How to Remove Duplicate Values From a List in Excel

Excel 2007 and 2010
1. 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 values from your list. If you have only selected one data cell, Excel will remove duplicate values from the entire list. If you selected more than one column, Excel will only remove values from the columns you specified.
4. Click 'OK' to close the message box that Excel created. If the list had any duplicate values, this message box tells you how many duplicate values it removed and how many unique values remain.
Excel 2003 and Earlier
5. Open your Excel document. Click on the column title above the column where you want to remove duplicate values. Select multiple column titles to remove duplicate values from more than one list.
6. Click on 'Data' in the menu bar, click on 'Filter,' then click on 'Advanced Filter.' This opens the Advanced Filter dialogue box.
7. Click on 'Filter the list, in place' and select the 'Unique records only' check box. Click 'OK.' This will hide the rows containing any duplicate values from previous entries.
8. Highlight the list without any duplicates showing. Click on 'Edit' and choose 'Copy,' or press 'CTRL C,' to copy the list.
9. Click on 'Data' in the menu bar, click on 'Filter' and then click on 'Show All.' This will display the entire list, including the duplicate values. Press the 'Delete' key to delete the list.
10. Click on the first blank cell underneath the column title. Click on 'Edit' and click 'Paste,' or press 'CTRL V,' to paste the list back in without any hidden duplicate values.

How to Remove a Formula or Program in an Excel Spreadsheet

Removing Custom Functions Macros
1. 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 from the 'Function' before the function's name to the next 'End Function,' inclusively. To remove a macro, select everything from 'Sub' to 'End Sub.'
4. Right-click on the module name, and select 'Remove (the module's name)' to remove all the functions or macros within the module.
5. Click on the items under 'Microsoft Excel Objects.' This is where code specific to sheets or workbooks is placed. You can remove individual functions and macros in the same manner as those in the 'Modules.' You cannot, however, remove all of them by removing the module. To remove all of the functions or macros, simply select all of the code and press 'Delete.'
6. Select 'File' then 'Save and Return to Microsoft Excel' to save the changes.
Removing Formulas
7. Find the cell containing the formula you wish to remove.
8. Left-click on the cell.
9. Press 'Delete' to remove the contents of the cell, formula included. To remove just part of a formula, select the portion in the 'formula bar' and press 'Delete.'

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-S.”

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 will read '=substitute(A3, '-', '.')' or to remove hyphens the formula will read '=substitute(A3, '-', '')' in the cell.
5. Press the 'Enter' key to complete the SUBSTITUTE 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. For example, to use INDIRECT to make versatile values within a SUM function, you would use SUM on the 'outside' and INDIRECT nested in parentheses, for a command like this: SUM (INDIRECT (A1, A2)).
5. Pass parameters to an INDIRECT statement for 'on the fly' cell changes. This step gets a little complicated. The thing to remember is that when you are passing values into an INDIRECT statement, you use ampersands to turn strings into references. Here's how it works: if you want B1 and B2 to contain the cell values for one of the A row cells, you put those into the B1 and B2 cells. Then, you create a statement like this: INDIRECT ('A'B1':A'B2). If this seems totally unclear, look at it carefully. In this case, INDIRECT is working like a computer programming function. The Excel function will 'see' the combination of text in quotations and cell references and interpret it to pass the cell you're building the values of the A row cells you selected and put into B1 and B2.

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 'Histogram.' Enter the cells that you typed your data in, for example A2:A19, into the 'Input Range' and then enter the cells that you typed your bins into the 'Bin Range.'
4. Chose whether to have the histogram appear as an item in the existing spreadsheet or on its own in a new sheet then click 'OK.' The histogram will show up where you have specified.

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 the 'Advanced' button in the 'Filter' button set. Excel should automatically select your entire data set, but if it doesn't you can select it yourself by clicking and selecting the range.
5. Leave the radio button next to 'Filter the list, in place' filled, and place a check mark next to 'Unique records only.' Click 'OK.' Excel will delete any duplicate entries seamlessly, without leaving blank rows.

Wednesday, May 11, 2011

How to Create an Auto Loan Calculator

How to Start the Auto Loan Calculator
1. 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. Left-click on the A column to highlight it, then right-click and choose “column width,” enter the number “25” and click return. This will increase the width of the column so all the characters can be seen.
10. Left-click on the B column to highlight it, then right-click and choose “format cells,” click on the “Number” tab, locate the category “Currency” and click on it. Verify that the decimal places are set to “2” and the symbol is set to “$.” Click “OK” to return to the worksheet.
11. Left-click on the B column to highlight it, then right-click and choose “format cells,” click on the “Number” tab, locate the category “Currency” and click on it. Verify that the decimal places are set to “2” and the symbol is set to “$.” Click “OK” to return to the worksheet.
12. Left-click on the B5 cell to highlight it, then right-click and choose “format cells,” click on the “Number” tab, locate the category “Number” and click on it. Verify that the decimal places are set to “0.” Click “OK” to return to the worksheet.
13. Left-click and drag on cells A1 through A6 to select those cells, then right-click and choose “Format Cells,” click on the “Patterns” tab, locate the color box for yellow and click on it. Click “OK” to return to the worksheet.
14. Left-click and drag on cells B1 through B6 to select those cells, then right-click and choose “Format Cells,” click on the “Patterns” tab, locate the color box for green and click on it. Click “OK” to return to the worksheet.
15. Left-click and drag on cells A1 through B6 to select those cells, then right-click and choose “Format Cells,” click on the “Border” tab, locate the preset with the “Inside” and click on it. Locate the preset with the “Outline” and click on it. Click “OK” to return to the worksheet.
16. Save the worksheet for later use.
How to Use the Auto Loan Calculator
17. Open the worksheet.
18. Click on cell B1 and enter the amount of the purchase price of the car and click return.
19. Click on cell B2 and enter the down payment amount of the car and click return.
20. Click on cell B4 and enter the yearly interest rate of the car loan and click return.
21. Click on cell B5 and enter the number of monthly payments that will be made on the car and click return. The monthly payment will be calculated in cell B6.

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 it.

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' to convert the WB1 file to the TXT format.

How to Multiply Cells in Excel

Multiply with Cell References
1. 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 Cell
7. Click on a blank cell. Type in the first number you want to multiply, for instance, 6.
8. Type in an asterisk (*).
9. Type the second number, for instance, 3.
10. Press the 'Enter' key or click on the check mark button on the toolbar. The result should appear in the cell.
Multiply a Group of Numbers by One Number
11. Enter the numbers you would like to multiply in a group of cells. For instance, enter the numbers 5, 4 and 8 into cells B1, B2 and B3.
12. Type the number 7 into cell A1.
13. Select cell A1. Open the 'Edit' menu and click on 'Copy.'
14. Select cells B1, B2 and B3.
15. Open the 'Edit' menu and select 'Paste Special.' In the 'Operations' section of the 'Paste Special' box, click on 'Multiply.'
16. Click 'OK' to perform the calculation. The numbers in cells B1, B2 and B3 should now be 35, 28 and 56, respectively.
17. Delete the number in A1.
Multiply Numbers in Different Cells with the PRODUCT Function
18. Enter the numbers you want to multiply. Type 4, 8 and 10 into cells A1, A2 and A3 respectively. Type the number 2 into cell B1.
19. Click on cell A4. Type =PRODUCT(A1:A3).
20. Press the 'Enter' key to perform the calculation. The PRODUCT function will multiply all the numbers in the range. Cell A4 should display 320.
21. Use the PRODUCT function to multiply a group of numbers in a range by a number. To multiply the numbers in cells A1, A2 and A3 by 2, click on cell A4. Type =PRODUCT(A1:A3,2).
22. Press the 'Enter' key to display the result. Cell A4 should show 640.

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 be able to unprotect the sheet and make changes to the data or formulas.

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 then type in “Savings Account.” Repeat this step for every type of savings or savings account you have including IRA, CD and Money Market accounts.
7. Type in every type of expenditure you have in all consequent squares within the A column until you have no more expenditures. Examples include groceries, gasoline, clothing, medical insurance, taxes, car repairs, credit card bills and utilities.
8. On the squares next to the expenditures and below “Sub Totals,” all the squares in the B column, type in the amount of money you estimate you will spend on those items for the month. For example, if you think you’ll spend $100 a week on groceries, type in “400” next to groceries.
9. Type in the actual amount of money that you spent on a certain item at the end of the month within the squares under the C column. For example, you actually only spent $370 on groceries so you would type “370” next to the 400 you estimated for groceries.

Subscribe to: Posts (Atom)