How to Make a Chart in the Same Sheet in VBA

1. Open Excel 2010 and show the Developer tab if it isn't already showing. Click on 'File' and then 'Options.' Click on 'Customize Ribbon' on the Categories pane. Select 'Developer' from the list of main tabs and then click on 'OK.'
2. Click on the 'Developer' tab that now should be showing in the Ribbon. Click on 'Visual Basic' to open the Visual Basic editor.
3. Double-click on the worksheet in which you want to embed the chart from the Project pain. All of the worksheets that are currently in your project is listed in the pane. The code for the worksheet will open in the Code window. If you haven't already added code to the worksheet the Code window will be blank.
4. Click on 'Insert' and then 'Procedure.' Type in a name for the procedure in the name window, leave all the other options the same and then click on 'OK.' For the example in this article, name the procedure 'embedChart.'
5. Add the code that will embed a chart into the worksheet. Click between the 'Public Sub...' and 'End Sub' declarations that are now in the Code window. You will add the code for the procedure in between the two declarations. Microsoft has developed the code that will create a chart and embed it in the current spreadsheet. Copy this code into your procedure or write your own.'Sub embedChart()Dim chtNew As ChartSet chtNew = Charts.AddSet chtNew= chtNew.Location(Where:=xlLocationAsObject, Name:='Sheet1')With chtNew.ChartType = xl3DPie'Set the data range source for the chart..SetSourceData Source:=Sheets('Sheet1').Range('A1:H2'), PlotBy:= _xlRows.HasTitle = True.ChartTitle.Text = 'My Pie Chart'End WithEnd Sub'
6. Click 'Save' on the Visual Basic editor's toolbar and close. Go to the worksheet that you embedded the chart in and check to see that it is showing.

Read more ►

How to Capitalize Everything in a Row in Microsoft Excel 2003

Capitalizing a Row of Cells
1. Start Microsoft Excel 2003, and open your spreadsheet.
2. Click the row number, not the cell, of the row immediately beneath the one you want to capitalize.
3. Right-click and select 'Insert' to insert an empty new row.
4. Click the cell in the new row that is directly beneath the left-most cell of the row you want to capitalize.
5. Type '=UPPER(name of cell immediately above the one you are typing in)'. Press 'Enter.'
6. Click the cell you just typed in to select it, and then hold the cursor in the lower-right corner of the cell until a black plus sign appears.
7. Hold the left mouse button down, and drag the cursor to the right, highlighting the whole row you want to capitalize.

Read more ►

How to Add Buttons to an Excel Spreadsheet

1. Access the Button tool. In Excel 2003 and earlier versions, do this by clicking 'View,' 'Toolbars,' 'Forms' and then clicking the Button tool on the Forms toolbar. In Excel 2007 and later, select the 'Developer' tab, then click 'Insert' and select the Button tool.
2. Click where you want the top-left corner of your button to be, and drag to create a rectangular outline. Let go and your button will appear.
3. Right-click the button and click 'Assign Macro.' This will bring up a dialog box listing all of Excel's saved macros. (The box may appear as soon as you've created the button, without the need for the 'Assign Macro' menu selection.) Click on the macro you want and click 'OK.'
4. Right-click the button again. You can now click within the text on the button to change it as you would with any MS Office text. Give the button whatever name you want. You can also right-click on the button's outline and click 'Format Control' to change the font of the button's name.
5. Click the button to run the assigned macro.

Read more ►

How to Use Data Analysis for Random Number Generation in Excel

Activate the Data Analysis Tool Pack
1. Launch Excel from the 'Start' menu or shortcut icon.
2. Click on the Microsoft Office orb to open the menu.
3. Click the 'Excel Options' option at the bottom.
4. Click 'Add-Ins' in the left pane.
5. Verify that the 'Manage' field at the bottom is set to 'Excel Add-Ins' and click the 'Go' button.
6. Click the check box beside 'Analysis ToolPak' and click 'OK' to install the add-in.
Random Number Generation
7. Click the 'Data' tab and select the 'Data Analysis Tools' icon.
8. Select 'Random Number Generation' from the list and click 'OK.'
9. Enter the number of variables you wish to use for your random number output. This determines the number of columns used to display your output.
10. Enter the number of random numbers you wish to generate. This determines the number of rows used to display the output.
11. Select the distribution type to use in the random number generation. The choices are Uniform, Normal, Bernouli, Binomial, Poisson, Patterned and Discrete. Each one is used under a different circumstance depending on the type of data you wish to analyze or produce. For example, Uniform generates a set of random numbers within the range you specify while Normal is used to generate random numbers with a certain mean and standard deviation.
12. Enter the parameters for your chosen distribution type. For example, a Uniform distribution requires you to enter the upper an lower limits of a range of numbers.
13. Enter the cell you want to use as the upper-left point in your output table in the Output Range field.
14. Click 'OK' to generate a random number table based on your selections.

Read more ►

How to Convert Office 2007 to Excel 2003

1. Open your Web browser, then go to the Microsoft Office Compatibility Pack download page.
2. Click the blue 'Download' button at the top of the page.
3. Click 'Save' in the download confirmation pop-up box. This downloads the installation file to your computer. Note the save location to help you find the file when you come to install it. The default save location is usually the 'Downloads' library folder.
4. Wait for the file to download. Windows displays a progress bar during the download process.
5. Find the downloaded installation file -- named 'FileFormatConverters' -- on your computer.
6. Double-click the file to install the program. Click 'Yes' if the installation wizard prompts you to restart the computer.
7. Launch Microsoft Excel 2003. Click 'File,' then click 'Open' to open the Excel 2007 file that you want to view or edit. Excel will now convert the new spreadsheet or workbook into a format compatible with the older software version.

Read more ►

How to Scroll on Microsoft Excel

Turning on Scroll Bars in Excel 2010
1. Open Microsoft Excel 2010.
2. Select 'Open' from the 'File' menu.
3. Navigate to and double-click the spreadsheet in which you want to scroll.
4. Click on 'File' again, then select 'Options.' Click 'Advanced.'
5. Locate 'Display options for this workbook' in the right column. Check the boxes next to 'Show horizontal scroll bar' and 'Show vertical scroll bar.' Click 'OK.'
Turning on Scroll Bars in Excel 2007
6. Open Microsoft Excel 2007.
7. Click on the Microsoft Office button in the upper-left corner of Excel. Click 'Open.'
8. Navigate to and double-click the file in which you want to scroll.
9. Click the Microsoft Office button again, then select 'Excel Options.' Click 'Advanced' in the left column.
10. Locate 'Display options for this workbook' in the right column. Check the boxes next to 'Show horizontal scroll bar' and 'Show vertical scroll bar.' Click 'OK.'
Scrolling through Excel
11. Click on the horizontal scroll bar to scroll to the right and left. Click on the vertical scroll bar to scroll up and down. Hold down the mouse button at the screen edge for at least 10 seconds to increase the scroll speed. Hold down the 'Shift' button while scrolling to scroll long distances.
12. Press the 'Scroll Lock' button on the keyboard to scroll through columns and rows as opposed to moving to an adjacent cell. Press the up and down arrows on the keyboard to scroll vertically or the left and right buttons to scroll horizontally. Press 'Ctrl' while pressing an arrow button to scroll through large distances in the worksheet. Press 'Page Up' or 'Page Down' to scroll down a window.
13. Rotate your mouse wheel up or down to scroll. Click the wheel and move the mouse in the direction in which you wish to scroll to accelerate the scroll speed. Reclick the mouse to stop scrolling.

Read more ►

How to Convert Corel Quattro Pro to Microsoft Excel

1. Click 'File,' then 'Open' in Microsoft Excel 2003 or earlier. In Microsoft Excel 2007, click the 'Office' button, then 'Open.' The 'Open' dialog box appears.
2. Locate the Corel Quattro Pro file. Click it once and click 'Open' or double-click the file to open it.
3. Save the file as Microsoft Excel spreadsheet. Click on 'File,' then 'Save as' in Excel 2003 or earlier version. In Excel 2007, click the 'Office' button, then 'Save as.' Choose a file location. Leave the name the same or rename the file by typing in the new name in the 'Filename' box. Click 'Save.'

Read more ►

How to Merge Center in Excel

1. Open Outlook by double-clicking on an Excel file. Alternatively, click 'Start,' then 'All Programs,' then 'Microsoft Office' then select 'Microsoft Excel' from the list.
2. Click on the 'Home' tab on the menu screen.
3. Select the cells you want to merge and center by left clicking on a cell and dragging the mouse until all the desired cells are highlighted. You can tell they are selected by the black line surrounding the cells.
4. Click on 'Merge Center' located in the middle of the 'Home' tab menu (it will be under the 'Wrap Text' option).

Read more ►

How to Make Negative Numbers Red in Excel

1. Highlight the cells containing your data. Note that Excel allows you to apply special formatting for different types of numerical data, such as percentages and currency values. If your data contains numbers formatted as different types, for example, both percentages and currencies, make sure the cells you highlight contain numbers formatted as the same type. If all your numbers are formatted the same way, you can highlight all the cells on a worksheet with one command: press and hold the 'Ctrl' key on Windows (or the 'Command' key on a Mac), and then press the 'A' key.
2. Right-click a highlighted cell. In the menu that pops-up, click 'Format Cells' to open a formatting window.
3. Select the 'Number' tab in the 'Format Cells' window, then select 'Number' in the category box. If you have applied a specific format to your cells, such as Currency, Accounting, Percentage, Fraction, Scientific or Special, select that category instead of 'Number.'
4. Click on the red numbers in the 'Negative Numbers' box, then click 'OK.' All negative numbers in your selected cells will now display in red.

Read more ►

How to Create a Fillable Order Form

Microsoft Excel 2010
1. Open Excel 2010 and select the 'File' tab. Click 'New' and type 'order form' in the search box. Hit the 'Enter' key on your keyboard. Excel displays the available templates.
2. Preview a template by clicking on one. The preview is displayed in the right task pane. Download the template by clicking it and selecting the 'Download' button. The template downloads to your computer.
3. Add your products, sales staff and logo to the form template. Add your name, address and date. Save your template by clicking the 'Save' icon on the Quick Access Toolbar.
Google Documents
4. Access the Google Documents website. Type 'Order Form' and press 'Enter.' Review the available templates that appear.
5. Preview the templates by clicking the 'Preview' button. Download the template by clicking the 'Use This Template' button.
6. Edit the newly downloaded template by adding your customized information. This includes your name, address, products, sales staff, logo and date.
OpenOffice Calc
7. Access the OpenOffice website. Type 'Order Form' and press 'Enter.' Review the available templates that appear.
8. Download an order template by clicking the 'Use This' button. The template downloads to your computer.
9. Double-click the template file to open it in OpenOffice Calc. Edit the newly downloaded template by adding your customized information. This includes your name, address, products, sales staff, logo, and date.

Read more ►

How to Take the Label 'Sum of' Out of a Pivot Table

1. Click on a cell in the PivotTable in the column from which you want to remove 'Sum of.'
2. Click the 'Options' tab on the toolbar, then click the 'Field Settings' button under the 'Active Field' area of the toolbar. A small window will pop up.
3. Place your cursor in the 'Custom Name' field and erase the 'Sum of' from the name.
4. Move your cursor to the end of what is left and add a space to the name. This is because once you remove the 'Sum of,' the remaining name is that same as a recognized field in the field list, and if you try to make a second field, Excel will give you an error. The extra space lets Excel differentiate between the two names, but they will look the same to anyone who views your PivotTable.

Read more ►

How to Use Excel's VARP Function

1. Learn the syntax of VARP. It is VARP(number1,number2,...) where number1, number2,... are up to 30 arguments which evaluate to numbers and comprise a complete population. VARP requires at least one argument.
2. Use arrays, names or references that contain numbers as arguments for VARP, in addition to pure numbers. Only numbers in an array or reference will be counted. Empty cells, error values, logical values or text in an array or reference will be ignored.
3. Enter logical values and text that represents numbers directly into the argument list. Error values or text that does not evaluate to a number will cause an error.
4. Ensure VARP is the correct function to use. VAR should be used instead of VARP if the data represents only a population sample. Use the VARPA function instead if you wish to include a reference that contains logical values or numbers represented by text in the calculation.
5. Calculate the VARP as the sum of (number - AVERAGE(number1,number2,...))^2/n where number is each value in the population and n is the number of values in the population. For A2 = 3.5, A3 = 5, A4 = 7.23 and A5 = 2.99, VARP(A2,A3,A4,A5) would return 2.71335.

Read more ►

How to Extract Tables From Multiple Word Documents Import Them to Excel

1. Use Windows Explorer to copy a Word file containing at least one table to the folder 'C:\.' Rename the file as 'table.docx.' You'll write an introductory program that will read a table cell from this file.
2. Click the 'Developer' tab, then click the 'Visual Basic' button to enter the Visual Basic programming environment. Paste the following program into VB code window. This program creates a link to Word, which allows excel to use Word's virtual objects just as though the program were running inside a Word document. This process is called 'Automation.'Public Sub LoadWordTablebak()Dim pgmWord As Word.ApplicationSet pgmWord = CreateObject('Word.Application')pgmWord.Documents.Open ('c:\table.docx')MsgBox pgmWord.ActiveDocument.Tables(1).Cell(1, 1)pgmWord.ActiveDocument.ClosepgmWord.QuitEnd Sub
3. Click the 'Tools' menu, then click the 'References' command. Scroll through the window that appears to locate the 'Microsoft Word' item, then click the checkbox for that item. This action lets Excel access the visual objects in a Word document.
4. Click any statement in the program, then click the 'Run' menu's 'Run' command. The program will display a message box showing the contents of a table cell in a Word document. You'll now expand the program to load in a complete table from any Word document.
5. Paste the following revised program below the 'End Sub' statement of the original program. This program prompts the user for the filename of a Word document. The user must also type in the number of a table within the document. The program uses the automation method of the first program to load copy the specified table cell by cell into an Excel spreadsheet.Public Sub LoadWordTable2()Dim docname As StringDim TableId As IntegerDim c, r, startRow As IntegerDim curCellDim pgmWord As Word.ApplicationSet curCell = ActiveCellSet pgmWord = CreateObject('Word.Application')docname = InputBox('Enter Word document name')docname = InputBox('Enter Word document name')While (docname
'')TableId = InputBox('Enter table number')pgmWord.Documents.Open ('c:\table.docx')With pgmWord.ActiveDocument.Tables(TableId)startRow = ActiveCell.RowFor c = 1 To .Columns.CountFor r = 1 To .Rows.CountcurCell.Value = .Cell(r, c)'Move to next rowSet curCell = curCell.Offset(1, 0)Next r'Move to next columnSet curCell = Cells(startRow, curCell.Column 1)Next cEnd WithpgmWord.ActiveDocument.Closedocname = InputBox('Enter Word document name')WendpgmWord.QuitEnd Sub
6. Click the 'Excel' application icon in the Windows taskbar to return to Excel, then click the 'Macros' button of the 'Developer' tab. Double-click the 'LoadWordTable' macro to run that macro.
7. Type the name of a Word document containing a table when the prompt to do so appears. Type the number of the table you want when the prompt for that table appears. For example, if you want the second table in the document, type '2.' Excel will load the table into the current spreadsheet.
8. Continue loading Word tables with the program as needed. Press 'Enter' at the 'document name' prompt to terminate the program.

Read more ►

How to Create a Form in Microsoft Excel 2007

1. Launch Excel from the start menu or from the desktop. Click the small drop-down arrow at the very top of the screen, to the right of the Save, Undo, and Re-do buttons. In the menu that appears, choose 'More commands.' The Excel Options screen should appear.
2. In Excel Options, click the drop-down at the top of the left-hand list of commands, labeled 'Choose commands from.' In the list that drops down, choose 'All commands,' which is the third item down from the top. In the list box below the drop-down, a long list of commands will appear, sorted alphabetically. Locate the command named 'Forms,' click on it, and click the 'Add' button in the center of the screen. Click 'OK' to close the Excel Options screen, and the Forms button will appear next to the Re-do button at the top of the screen.
3. Open the spreadsheet with the rows that are to be viewed in forms. Click on a cell that is in the list of data the form is to be for. It can be a heading cell or any cell within the data. Click on the Forms button that was added in step 1. Excel will automatically create the form.

Read more ►

How to Paste Special HTML in Microsoft Excel 2003

1. Create the desired HTML that you wish to paste special into Excel with.
2. Copy the HTML code. You can easily copy the HTML by highlighting it all and pressing the hotkeys “CTRL-A” and then by pressing the hotkeys “CTRL-C.”
3. Access the 'Paste special' menu. To access this menu, you will need to scroll to the “Edit” tab and select “Paste Special.”
4. Set the paste special options. Under the paste special properties menu, in the “As” box, you should left-click on “HTML” to select the format.
5. Paste special the HTML into Excel. Now click on the “OK” button to paste special HTML into Excel.

Read more ►

How to Speed Up an ODC Connection in Excel 2007

1. Open the Microsoft Excel 2007 program on your computer and then click the 'Microsoft Office' button.
2. Click the 'Open' option and then select the Excel 2007 worksheet you want to speed up an ODC connection for. Click the 'Open' button.
3. Click the 'Data' tab from the top of the application and then click the 'Connections' option from the 'Connections' group.
4. Select the 'Usage' tab in the Workbook Connections dialog box. Click on each box in the 'Refresh control' section so that they're deselected.
5. Enter a lower number of records to retrieve in the 'Maximum number of records to retrieve' field.
6. Click the 'Definition' tab and then click the 'Parameter' button. You can then edit the parameters for your ODC query. For example, you can make your query as short as possible to speed up the connectivity.
7. Click the 'Apply' button to save all of your changes and then click the 'OK' button to close out of the dialog box.

Read more ►

How to Insert a Picture or Logo into an Excel Document

1. Open the Excel document that you want to add the logo or picture to.
2. Click Insert and select Header Footer from the Text group.
3. Click inside the header or footer where you wish the picture to appear.
4. Click Picture under Header Footer Tools in the Header Footer Elements group on the Design tab.
5. Select your picture and click 'Insert.' The picture will appear where you clicked.

Read more ►

How to Delete Characters Over 40 Character Length in Excel

1. Click the 'Start' button on the taskbar, type 'Microsoft Excel' in the Search box and then press 'Enter.'
2. Press 'Ctrl O' and then choose the Excel document from which you want to delete characters over 40 characters in length on the dialog box that opens.
3. Type the formula '=LEFT(cell_number,40)' in the cell in which you want to delete characters over 40 characters in length. Replace, cell_number with the unique number of the cell, for example, A1.

Read more ►

How to Generate a Random Number in Excel

1. Open Microsoft Excel 2007, and start a new, blank spreadsheet or open an existing spreadsheet from your files into which you want to generate a random number.
2. Click the cell you want to generate a random number into so it is selected. The cell will have a thick black line outlining it.
3. Type '=RAND()' (no quotations) into the 'Formula' text box near the top of the Excel screen. This is the function that instructs Excel to enter a random number between 0 and 1 into the selected cell.
4. Press the 'Enter' key on your keyboard. The random number will be generated into the selected cell and you will be taken to the cell beneath it.
5. Continue the process of generating random numbers into your Excel cells using the same method as outlined above.

Read more ►

How to Fill the Cells With the Names of the Successive Months in Excel

1. Open a new Microsoft Excel 2010 spreadsheet.
2. Click on cell “A1” and type in the name of the first month you want to use. Excel can begin to fill successive months from any month, so you don’t necessarily need to have “January” be first. You can also add the year, if necessary. Excel will automatically add to the year as it adds each successive month. Press “Enter” when you are done entering the month.
3. Click on cell “A1” again, as Excel will change the selected cell once you pressed “Enter.” Move your mouse over the lower-right corner of the cell. Your pointer will change into a “ ” sign.
4. Click and hold the mouse button. Drag the mouse down the spreadsheet. As you move the mouse, a small pop-up window will display the month that will appear in the last selected cell, so you can tell how far you need to drag the mouse.
5. Release the mouse button when you have reached the last cell that you want to fill with month names. Excel will automatically add the names of the successive months to each cell, starting from the first month that you entered.

Read more ►

How to Calculate Weighted Averages for Grades in Excel

1. Type 'Grade' in cell A1, 'Weight' in B1 and 'Total Worth' in C1.
2. Type your grades in column A. For example, if you received a 95 and an 80, then type '95' in A2 and '80' in A3.
3. Type the weight each grade has in column B, next to its corresponding grade. In the example, if the first grade is worth 60% and the second grade is worth 40%, then type 60% in B2 and 40% in B3.
4. Type '=A2*B2' in cell C2. This weights the grades. Copy and paste this formula next to each grade.
5. Type '=sum(' in column C after your last grade, then highlight all the cells that have a number in coumn C and hit the 'Enter' key. The result that appears is your weighted average.

Read more ►

How to Use the AutoFill Function in Excel 2003

1. Open Excel.
2. Open your worksheet by clicking 'File > Open.'
3. If you don't have an existing worksheet, create a new one. Go to 'File > New.'
4. Enter your information. The AutoFill function will be able to fill in information in a series. When making a calendar, for example, you can type 'January' into the first box and use AutoFill to put the subsequent months in the next cells.
5. Place your mouse over the bottom right hand corner of the cell with 'January' (or whatever the first cell in your series is). When you see a small 'x' appear where your mouse arrow was, click and drag the 'x' to the cell where your series will end. The rest of the series should be filled in for you.

Read more ►

How to Reduce the Size of an Excel Workbook

1. Delete the extra default worksheets in a new Excel workbook. Each new workbook that Excel creates has three worksheets (Sheet1, Sheet2 and Sheet3). These sheets require additional memory and space on the hard drive when you save your workbook. Right click on the name tab (at the bottom of the workbook) for the second worksheet. Then choose Delete from the pop-up context menu. Repeat these steps to delete the third worksheet.
2. Reduce the size of graphics, line art or smart art that you have inserted or imported into the workbook. Graphics quickly increase the size of every saved Excel file. Click on any embedded graphic object to activate the Picture Tools menu. Compress, crop or decrease the color saturation and resolution of jpeg, gif, bmp, tiff and png files. You can also use an external graphics program to resize clipart and other file formats, and then insert the smaller sized object into your workbook.
3. Use TrueType fonts as the default font for your Excel worksheet labels. Custom fonts require additional memory allocations. Excel stores the instructions for your printer in the workbook. Therefore, your file size will increase with each custom font.
4. Apply text formats such as bold, italics and colors to individual cells only. If you select a row or column when adding formats, Excel applies the formatting to all cells in the selection. Since Excel has more than 200 columns and thousands of rows, the formatting will use Random Access Memory to manage the formats for the rows and columns.
5. Eliminate unnecessary comments, text boxes and other enhancements such as watermarks, cell shading and borders. All of them require additional space when you save the file. Headers, footers, page numbers, charts, hyperlinks, tables and range names in formulas also increase the size of an Excel workbook. Printing options such as gridline displays and repeated row and column headings can also be eliminated to reduce the size of your workbook.
6. Save your workbook in PDF format. This option is included in Excel 2007; most earlier versions require an add-in that you must download. Choose Save As in the File menu in Excel versions through 2003. It is under the Microsoft Office Button menu (left of Home) in Excel 2007. Select PDF from the menu. Then select the Optimize for minimum size radio button in the file Save dialog box. This creates a smaller PDF file that is ready to email or publish online.

Read more ►

How to Use a Data Form in Microsoft Excel 2007

1. Open the Microsoft Excel 2007 program on your computer and then click on any cell where you want the form to start.
2. Click on the arrow next to the Quick Access toolbar and then click on the 'More Commands' option. Select 'All Commands' and then click on the 'Form' button from the list.
3. Click on the 'Add' button and then 'OK.' Click 'Form' from the Quick Access toolbar, and then you can enter your data into the first row of the form.
4. Click on the 'New' button to add a new row and then enter your data. Press the 'Enter' key on your keyboard, and the row of data will be added to the bottom of the form.
5. Select any row that you want to remove and then click on the 'Delete' button. Choose 'Close' at any time to close out of the data form and return to your worksheet.

Read more ►

How to Find Duplicate Entries on an Excel Spreadsheet

1. Select the first cell in your column of data. This cell will serve as a template for the conditional statement that searches for duplicates in the file. After the cell is selected, click the 'Format' menu item and select 'Conditional Formatting.' This opens a new dialog window.
2. Select 'Formula Is' in the new dialog window. A text box displays, prompting you for an Excel formula. Enter the following code into the text box:=COUNTIF (A:A,A1) > 1The 'countif' function searches through the 'A' column and finds any duplicates (the formula assumes that A1 is your first cell. If your data begins at A2, replace 'A1' with 'A2' in the formula. Replace each 'A' with a 'B' to check column B, etc.).
3. Click the 'Format' button in the 'Conditional Formatting' dialog box. This opens a window prompting you for a color selection. Click a color you want to use as the highlight color for the duplicates. The 'countif' function finds the duplicates, and the format color is used to show you which cells are found. Click 'OK.'
4. With your cursor in the first data cell, click 'Copy' from the Edit menu. Press 'Ctrl spacebar' then click 'Paste Special' from the Edit menu. Select 'Formats' from the Paste Special dialog, then click 'OK' to copy the conditional formatting through the entire column.
5. Scroll down the column of data to find each duplicate. The duplicates are highlighted in the color you chose. You can either delete these duplicates, exclude them from future formulas or move them to another area of the spreadsheet.

Read more ►

How to Use Excel's DSUM Function

1. Learn the syntax of DSUM (database, field, criteria). Database is a cell range that specifies the database, field is a column in the database from which DSUM will add values and criteria is a cell range with the criteria to be used in selecting the values.
2. Define the database for DSUM. The first cell in the range will indicate the upper left corner of the database array and the second cell will indicate the lower right corner of the array. The first row of the array must contain the column names.
3. Specify the field for DSUM. It can include the column name enclosed in double quotation marks or a number representing the column's position in the database where 1 is the first column, 2 is the second column and so on.
4. Supply the criteria for DSUM. This cell range must contain at least one column name and one condition for that column. The condition will generally be of the form ='condition.' Thus, a value of ='=Apple' would select rows where the value in the specified column is equal to 'Apple.'
5. Enter the following in the first four columns of an Excel spreadsheet:Row 1: Tree, Height, Age and Yield;
Row 2: ='=Apple' and ='>10' (nothing in columns three and four);
Row 3: ='=Pear' (nothing in columns two, three and four);
Row 4: Tree, Height, Age and Yield;
Row 5: Apple, 18, 20 and 14;
Row 6: Pear, 12, 12 and 10;
Row 7: Cherry, 13, 14 and 9;
Row 8: Apple, 14, 15 and 10;
Row 9: Pear, 9, 8 and 8; and
Row 10: Apple, 8, 9 and 6.
6. Interpret the formula =DSUM(A4:D10,'Yield',A1:B2) as follows: The database is defined as the array A4:D10. Note the column names in the first row of this array. 'Yield' is the column name from which the values will be taken. The criteria are given by A1:B2 which indicates that the column named Tree must contain 'Apple' and the column named Height must contain a value greater than 10. DSUM(A4:D10,'Yield',A1:B2) will therefore be evaluated as SUM(14,10)=24.

Read more ►

How to Convert Columns of Text to Rows

1. Click the letter at the top of a column to select a column. Hold 'Ctrl' and click on more column headers to choose several columns.
2. Press 'Ctrl' and 'X' to cut the selected columns.
3. Right-click inside the first cell into which you want to paste the data as rows.
4. Select 'Paste Special' from the drop-down menu.
5. Select the 'Transpose' check box in the 'Paste Special' dialog box.
6. Click 'OK.' The columns will now appear as rows.

Read more ►

How to Share Password Protect Track Changes in Excel

Password Protection for Specific Cells
1. Determine the level of protection needed for your spreadsheet. If there are specific cells you do not want changed but others that can be, you can just lock cells. If the spreadsheet needs to be fully locked, or you want to prevent users from accessing the actual file, then file-level protection is appropriate.
2. Click 'Home' on the ribbon after opening a file in Excel.
3. Navigate to the Cells section, click 'format,' and then click 'Lock Cell.'
Password Protection for Workbooks
4. On the ribbon click 'Review.'
5. Navigate to the changes group and click 'Protect Workbook.' This will bring up a pop-up window.
6. Select 'Structure' or 'Windows' from the pop-up window. If you lock the structure, users will not be able to reorder worksheets. If you lock windows, users will not be able to change worksheet window sizes.
7. Choose and write down a password to lock the workbook. This is optional, but enhances the security of your worksheet.
Tracking Changes
8. On the ribbon click 'Review.'
9. Navigate to the changes group and click on 'Share Workbook.' This will bring up a pop-up window.
10. Select the check box next to 'allow changes by more than one user at the same time.' Then click on the 'Advanced' tab and type in the number of days you want Excel to keep the change history. Click 'OK.' Note that Excel defaults to 30 days of change history. This is sufficient for most users.
11. Navigate to the changes group and click on 'Track Changes.' Then select 'Highlight Changes.' This will highlight any changes made to a cell so that you will be able to review it later.

Read more ►

How to Do Stacked Charts in Excel

1. Enter the data for the stacked chart into an Excel spreadsheet. Clearly label the data in the first row so that it is easier to identify when you are making the chart.
2.
Highlighting the data tells Excel which data to include in the chart.
Highlight the data you want to include in the stacked chart. Include the data labels (typically placed in the first row). You can highlight by clicking your left mouse button, holding it down, and dragging to include all pieces of data. Alternatively, to include the entire spreadsheet in the chart, press 'Ctrl-A' to highlight everything.
3. Open the chart interface. In Excel 2007, click the 'Insert' tab, then click the down arrow under Column, and click 'All Chart Types.' In Excel 2010, click the 'Insert' tab and click 'Chart.'
4. Choose the chart type by clicking or scrolling to the corresponding section. The most common type of stacked chart is column, but you may also choose bar.
5. Choose stacked chart style by clicking the corresponding stacked style under the chart type. Verify that the style you have chosen is stacked by hovering over it and looking for 'stacked' in the tool tip description. Choose a 100 percent stacked style if you want all bars to be full and display the composition breakdown. Choose the style that most clearly displays your data and fits your taste.
6. Press 'OK' and verify the data.

Read more ►

How to Audit Formulas in Excel 2007

1. Open an existing Microsoft Excel document to audit. Double click on the 'My computer' icon on the operating system desktop. Then locate the existing Excel document and double click on that file.
2. Select the 'Microsoft Office Main Menu Button' located in the top right hand corner of Microsoft Excel. From that menu click on the 'Excel Options' button located at the bottom of the menu dialogue box.
3. Click on the 'Advanced' options button on the Excel options dialogue box.
4. Check all the options under the 'Display Options for This Workbook' section. Also, make sure that the 'For Object: Show' option choose 'All.'
5. Show all the formulas in an Excel worksheet by selecting all the cells. Do this by clicking on the top left hand corner of the worksheet. Once they are all selected click on the 'Formulas' tab and choose 'Show All Formulas' from the formula auditing section of the formulas tab.
6. Audit the formulas by choosing the 'Error Checking' button in the formula auditing section of formula ribbon. A user can also trace dependents and precedents by choosing the 'Trace Dependents' and 'Trace Precedents' option from the very same section of the formula auditing section of the formulas ribbon.

Read more ►

How to Subtract Dates in Excel 2007

1. Open Excel 2007 and enter the current date in cell A1. Enter another date in cell B1.
2. Highlight cells A1 and B1. Click the 'Home' tab and locate the 'Numbers' group. Change the number format to 'Short Date.'
3. Click cell C1 and enter a subtraction formula. Enter 'A1-B1.' Press the 'Enter' key to see the results.

Read more ►

How to Create a Toggle Switch in Excel 2003

1. Open Microsoft Excel 2003. Click the 'Microsoft' button on the top left corner of the window. Click the 'Excel Options' button in the lower right corner of the dropdown menu.
2. Click the 'Popular' tab. On the box to the right, check the 'Show Developers tab in the Ribbon' box. Click the 'OK' button on the bottom right of the window.
3. Click the 'Insert' button in the top center of the window. Click on the 'Toggle' button on the bottom right corner of the dropdown menu.
4. Click the left mouse button anywhere on the screen. The toggle button appears. Click and hold the left mouse button on the 'Toggle' button to move it. Your toggle switch is now created.

Read more ►

How to Merge Two Macros in Excel

1. Create a master macro by clicking on the 'View' tab in the toolbar. Click on 'Macros' and select 'View Macros.' Type the name of the master macro, such as 'Master,' in the 'Macro name' box. Click on the 'Create' button to launch the Visual Basic editor.
2. Place the cursor in the second line under the subroutine. For example, if the name of the macro you created is 'Master,' the first line is 'Sub Master().' Under this line, type 'Call,' followed by the name of the first macro you would like to run. For example, if you created a macro called 'GetWebQueries' that loops through a list of URLs, opening each one and copying and pasting the data from a table into a new worksheet, you would type 'Call GetWebQueries().'
3. Press 'Enter' to go to the next line. Type 'Call,' followed by the name of the second macro, such as 'Call Format(),' which would run the Format macro that formats each worksheet in a specified manner. Save the macro and close the Visual Basic editor.
4. Run the master macro by clicking on the 'View' tab. Click on 'Macros' and select 'View Macros.' Select the master macro and click on the 'Run' button. The master macro will run 'GetWebQueries' and the 'Format' macros in the specified order.

Read more ►

How to Filter Excel for Duplicate Names

1. Open your Excel spreadsheet.
2. Select one or more columns in which you suspect there are duplicate values.
3. Click on the 'Data' tab at the top of the screen, then the 'Remove Duplicates' icon.
4. Check the boxes with suspected duplicates in the dialog box that opens.
5. Click 'OK.' Another box will appear, telling you how many duplicates have been removed and how many unique values remain.

Read more ►

How to Remove All the Blank Rows in Excel 2007

1. Open Excel 2007 and select a workbook. Select the 'Office' button and click 'Open.' Search your network for the workbook. Click the workbook and select 'Open.' The workbook opens.
2. Select the first column header in your workbook. Select the 'Home' tab and click 'Filter' from the 'Sort Filter' button in the 'Editing' group. Notice the dropdown icons that appear in your column headers.
3. Select a column header that may contain blank data. Click the dropdown icon. Scroll down in the list and locate 'Blanks.' Remove the check. The blank rows from this column are removed.
4. Repeat this step for each column that may contain blank data. Once you have done this for each affected column, all of the blank rows will be removed from your Excel 2007 workbook.

Read more ►

How Do I Count Distinct Records in an Excel Pivot Table?

1. Add a column to the database with which you are working; give it a text heading of your choice and add this field to your pivot table.
2. Enter the following formula in the first cell of the column you created:=IF($A$2:$A2=A2)>1,0,1)where A is the column which you are searching and 2 is the first row of data in your pivot table.
3. Copy the formula down the entire column you created and the cells in that column will display the number of distinct records in that row.

Read more ►

How to Combine Contents of Two Rows of Cells in Excel 2007

1. Click the 'Start' button from the desktop on your computer, and then click the 'All Programs' option. Select the 'Microsoft Excel 2007' application from the list of programs.
2. Click the 'Microsoft Office' button from the top right corner of the program followed by the 'Open' option. Select the Excel 2007 worksheet file you want to combine cells with, and then click the 'Open' button.
3. Select the two rows of cells that you want to combine with your mouse. The cells should be highlighted.
4. Select the 'Home' tab, and then click the 'Merge and Center' option from the 'Alignment' group. The cells will then be merged together.
5. Click the 'Merge and Center' button again at any time to split the merged cells if you no longer want the contents to be combined.

Read more ►

How to Hide/Unhide in Excel 2007

1. Log on to your computer and open Microsoft Excel 2007. Open the spreadsheet.
2. Select a cell within the row or column you wish to hide. Click on the 'Cells' group and choose 'Format'.
3. Go to the 'Visibility' section and choose 'Hide' or 'Unhide' from the menu.

Read more ►

How to Make a PERT Chart in Excel

1. Launch Microsoft Excel 2010.
2. Click 'Insert' at the top of the window.
3. Click the 'Shapes' drop-down menu in the Illustrations section of the ribbon. Select your desired shape from the Flowchart section.
4. Click your mouse at the location in the spreadsheet where you wish to insert the object, and then drag the mouse until the outline of the shape is the desired size.
5. Click the 'Text Box' option in the Insert Shapes section of the ribbon. Click inside the shape you just created and use your mouse to draw a text box inside the shape. Note that you can also click a color for the shape from the Shape Styles section of the ribbon.
6. Type the necessary text for the object.
7. Insert additional shapes from the Shapes drop-down menu on the Insert tab, and then add text boxes to each shape as needed.
8. Click your desired connector arrow from the Insert Shapes section of the ribbon.
9. Click the shape that will serve as the anchor for the arrow, and then drag the arrow to the target shape for the arrow.
10. Add additional arrows to your chart as needed.
11. Add outlying text to your chart by clicking the 'Insert' tab at the top of the window and then clicking the 'Text Box' button in the Text section of the ribbon.
12. Click in the desired location on your spreadsheet for the text box, and then drag the mouse until your desired text box shape is displayed.
13. Type your text into the text box.
14. Add additional text boxes to your chart until the chart is complete.

Read more ►

How to Create a Microsoft Calendar

1. Open Microsoft PowerPoint, Word, or Excel. In PowerPoint, Word or Excel 2010, select 'File' and 'New.' In PowerPoint, Word or Excel 2007, click the Microsoft Office button on the ribbon and select 'New.'
2. Select the 'Calendars' heading under Office Online templates to find a template type. A list of calendar templates will appear. The calendar templates in PowerPoint and Excel 2010 and 2007 include calendars from 2007 to 2010, academic calendars and even an Advent calendar. Word 2010's templates also include 2011 calendars.
3. Click on the calendar type you want and, within the calendar types, select a specific template design by double clicking it to begin the download. The download will automatically begin without leaving the application.
4. Customize your calendar. In PowerPoint you can change colors and font themes by going to the 'Design' tab and selecting a different design template. In Word and Excel, theme changes are made from the 'Page Layout' tab. The new design theme will apply to the entire calendar.
5. Add in text and graphics. Insert birthdays, social gatherings and appointments by positioning your cursor to the appropriate day and start typing. You can select the 'Insert' tab to place photos, pictures or clip art in your calendar. All graphics can be moved and resized as you desire. Customization can be made from month-to-month and printed as 'current page only' for month by month printing or in its entirety as a wall calendar.

Read more ►

How to Import Excel Into MS Word

1. Select the data in Excel you want to import into Word. Press 'Ctrl-C' to copy the data. You can also right-click on the selected data and click 'Copy' from the context menu.
2. Select the location in your Word document to enter the copied data. Click the 'Paste' drop-down menu and select 'Paste Special.'
3. Select the 'Paste link' radio dial, choose 'Microsoft Excel Worksheet Object' from the list and click 'OK.'
4. Adjust the formatting of the new table to meet your needs.

Read more ►

How to Link Bloomberg to Excel

1. Close Excel. Download the Bloomberg Excel add-in (www.bloomberg.com). Click the 'Download' link, which will open a separate window. Click the 'Run' button.
2. Install the Bloomberg Excel add-in. Click the 'Start' button and select the 'All Programs' option. Click 'Bloomberg' and select 'Install Excel Add-In,' which will open a separate window.
3. Click the 'Install' button and then close the window when the installation process has completed. Open Excel to view the 'Bloomberg' tab on the menu bar.

Read more ►

How to Use Data Analysis in MS Excel

Excel 2007
1. Open Excel and navigate to a blank or existing spreadsheet file. Locate the Data Analysis tool by clicking the 'Microsoft Office' button, located in the top left-hand corner of your open spreadsheet. This opens a menu with a series of commands. Click the “Excel Options” button at the bottom of this menu.
2. Choose the “Add-ins” option, located in the column on the left side of the Excel Options menu. This will display a set of available add-ins that can be installed in your Excel program. Click the “Analysis ToolPak” add in, which often appears first on the list of add-ins. After selecting this add-in, click “OK.” The Data Analysis tool should appear in your Excel 2007 tool bar, located at the top of an Excel workbook file.
3. Select the Data Analysis tool to analyze a set of data in Excel by clicking the tool in your toolbar. This will open a smaller window that displays a set of statistical procedures and analysis tools.
4. Select the analytical procedure you want by clicking on it, then choosing “OK.” This will take you to the window for that procedure. Here you must specify the range of data you want analyzed by clicking and dragging across the cells, rows or columns that contain the data.
5. Indicate whether you want your results displayed on the same worksheet, in a new worksheet or in a new workbook by choosing the appropriate option. Click “OK.” Excel will then run the analysis and display the results in the manner you selected.
Earlier Versions of Excel
6. Unlock and install the data analysis tool by clicking the “Tools” menu, found in the toolbar at the top of an open Excel workbook file. This will display a drop-down menu of available options.
7. Select “Add-ins” from the Tools menu. This opens a small menu that displays the add-ins that can be installed for use. Choose 'Analysis ToolPak' by clicking the check box next to it, then click “OK.” The tool “Data Analysis” should then appear in the Tools drop-down menu, ready for use.
8. Select the Data Analysis tool to analyze a set of data in Excel by clicking the tool in your toolbar. This will open a smaller window that displays a set of statistical procedures and analysis tools.
9. Select the analytical procedure you want by clicking on it, then choosing “OK.” This will take you to the window for that procedure. Here you must specify the range of data you want analyzed by clicking and dragging across the cells, rows or columns that contain the data.
10. Indicate whether you want your results displayed on the same worksheet, in a new worksheet or in a new workbook by choosing the appropriate option. Click “OK.” Excel will then run the analysis and display the results in the manner you selected.

Read more ►

How to Set Workbook Share Options in Microsoft Excel 2003

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

Read more ►

How to Use AutoFit Tool in Excel

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

Read more ►

How to Convert Dates Times Into Decimals in Excel 2003

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

Read more ►

How to Delete Blank Lines in Excel 2007 Lists

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

Read more ►

How to Protect a Workbook in Microsoft Excel 2003

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

Read more ►

How to Copy Excel Macros From One PC to Another

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

Read more ►

How to Round Up to the Nearest 10 in Excel

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

Read more ►

How to Add or Subtract Percentages

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

Read more ►

How to Create a Bar Graph in Microsoft Works

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

Read more ►

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

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

Read more ►

How to Draw a Box Plot in Excel

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

Read more ►

How to Create a Grade Book Using Microsoft Excel

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

Read more ►

How to Group Images in Excel

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

Read more ►

How to Copy Paste Hyperlinks to Another Worksheet in Excel

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

Read more ►

How to Set Print Area in Excel 2007

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

Read more ►

How to Unlock a Password for Excel Spreadsheets

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

Read more ►

How to Troubleshoot Excel's Autofill

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

Read more ►

How to Set a Print Area in Microsoft Excel 2003

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

Read more ►

How to Activate a Toggle Button in VBA

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

Read more ►