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 ►