How to Create a Form Using Microsoft Excel

1. Insert form. Open a new workbook in Microsoft Excel. Press the 'Alt' and 'F11' keys on your keyboard to open Microsoft Visual Basic. Double click on 'Sheet 1' (Sheet 1) under Microsoft Excel Objects in the left window pane under VBAProject. Go to the Insert menu and select 'UserForm' to insert a form.
2. Name form. Go to the Properties window in the left window pane and click on 'UserForm1' next to (Name). Type a new name for your form. Hit 'Enter.'
3. Add text box. Click on the 'Textbox' button in the Toolbox toolbar. Click on a place in the form where you would like to add a standard-sized text box. You can adjust the size of the box using the handles. Go to the Properties window in the left window pane and click on 'TextBox1' next to (Name). Type a new name for your text box. Hit 'Enter.' Repeat this step for additional text boxes you would like to add.
4. Add label. Click on the 'Label' button in the Toolbox toolbar. Click on a place in the form where you would like to add a standard-sized text label. Adjust the size of the box using the handles. Go to the Properties window in the left window pane and click on 'Label1' next to (Name). Type a new name for your label. Hit 'Enter.' Repeat this step for additional labels.
5. Add buttons. Click on the 'Command' button in the Toolbox toolbar. Click on a place in the form where you would like to add a standard-sized button. Go to the Properties window in the left window pane and click on 'CommandButton1' next to (Name). Type a new name for your button. Hit 'Enter.' Change the caption to the text you would like to appear on the button such as 'Login.' Repeat this step for additional buttons you would like to add.
6. Add code. Select the button, go to the View menu and select 'Code.' Enter code functionality for the button. Go to the View menu and select 'Object' to go back to the user form. See the link in Resources below for examples of button codes.
7. Test form. Go to the Run menu and select 'Run Sub/UserForm' to run the form.

Read more ►

How to Create a Pivot Table From External Data in an Excel File

1. Save your external data into a file. The most common type of file is an external database.
2. Pull up a blank spreadsheet within Excel 2003. Click 'Data' in the drop-down menu and scroll down to 'PivotTable.'
3. Click 'External Data Source' and select 'PivotChart Report with PivotTable Report.' Once you make these selections, another pop-up menu will appear.
4. Select 'Get Data,' which will bring up another window. Select the file or database you will be choosing from. These include Access, Excel, dBase, and ODBC.
5. Click OK and close. Use default value for all other prompts and click Finish. The pivot table will automatically appear.

Read more ►

How to Copy Formulas Down in Excel Lightning Fast

1. Open the Excel 2010 spreadsheet where you want to add your formula.
2. Select the first cell where you want the formula to appear and type in your formula. Press 'Enter' when you are done.
3. Move the mouse to the cell where you just entered your formula. Move the pointer over the bottom-right corner of that cell and it will change into a ' ' sign.
4. Click and hold the mouse button. Drag the mouse down the spreadsheet until you reach the last cell where you want the formula to appear. Release the mouse button, and the formula will instantly copy itself down into every cell.

Read more ►

How to Use a Financial Calculator to Get Compound Interest

1. Use the compound interest financial calculator on Moneychimp (moneyChimp.com). Fill out the boxes on screen with information about your current loan including the principle, the interest rate, the annual addition and the years to grow. Input the number of times your interest is compounded. Click 'Calculate' to view the total amount of your loan or investment with compound interest added in.
2. Use the financial calculator on 1728.com. Fill out the boxes at the bottom of the financial calculator page with information about your current loan, savings account or investment (whichever applicable). Select the appropriate terms from the boxes on screen and click 'Calculate' to view the future value of your loan, account or investment with compound interest added in.
3. Use a regular financial calculator with the appropriate formula. The formula for compound interest is 'Principal x ( 1 Rate )years.' Replace each term with the dollar amount related to your loan, account or investment information. The total amount of money that you solve for is the value of your account, loan or investment with compound interest added into the principle.

Read more ►

How to Create CSV Files in Excel 2007

1. Open your spreadsheet. Click on the 'Office' button in the upper left-hand corner.
2. Select the 'Save As' option from the pull-down menu. Click on the 'Other Formats' button.
3. Choose the 'CSV (Comma delimited)' option.
4. Type a name for the CSV file.
5. Check the location of the file, making sure you remember where the file will be saved.
6. Click on 'Save.' You can now close the Excel file.
7. Open the CSV file to make sure there weren't any problems with the export.

Read more ►

How to Make a Graph in Excel Edit the Legend

Create a Chart
1. Open the Excel worksheet that contains the data for the chart.
2. Click and drag to select the data for the chart.
3. Click the “Insert” tab on the command ribbon.
4. Click the arrow for the preferred chart type in the “Charts” group. Examples of chart types include 'Column,' 'Line' and 'Pie.' A gallery of chart thumbnails appears.
5. Click to select the chart type. A chart displays over the Excel worksheet. The “Chart Tools” ribbon shows three tabs: “Design,” “Layout” and “Format.”
6. Format and style your chart with the commands on the “Design,” “Layout” and “Format” tabs. For example, the “Design” tab contains options for “Chart Layouts” and “Chart Styles.”
Edit the Chart's Legend
7. Click the Excel chart. The 'Chart Tools' ribbon appears.
8. Click the “Design” tab in the “Chart Tools” ribbon.
9. Click the “Select Data” button in the “Data” group. The “Select Data Source” dialog window opens.
10. Click the “Add,” “Edit” or “Remove” options in the “Legend Entries (Series)” section. Update the legend. Click “OK.”
11. Right-click the legend box. A dialog box displays options for “Legend Options,” “Fill,” Border Color,” “Border Styles,” “Shadow” and “Glow and Soft Edges.”
12. Click to select the preferred options to format your chart’s legend.
13. Click “Close.”

Read more ►

How to Automatically Shade Every Other Row in Excel 2007

1. Open the spreadsheet you want to shade the rows of in Excel.
2. Hold the 'Ctrl' key on your keyboard and press 'A,' releasing both keys.
3. Click 'Home' on the top menu in Excel 2007.
4. Click 'Format as Table' under 'Styles' and click 'OK.' This shades the rows for you automatically.

Read more ►

How to Make Excel Reports

1. Open the Excel program to display a new worksheet.
2. Click the “File” tab on the command ribbon to display a list of commands.
3. Click “New,” then a gallery of “Office.com Templates” appears.
4. Click the “Reports” link, then a list of folder icons appears.
5. Click the preferred folder such as “Financial Records,” “Logs” or “Human Resource Records.” A gallery of thumbnail images appears.
6. Click the preferred report image to display a larger image in the preview pane to the right.
7. Click “Download” in the right pane. The template copies to the new worksheet.
8. Click in a cell and type your data. Use the Excel editor commands to customize the look of this report. For example, select a 'Font' and 'Fill Color' command on the 'Home' tab.
9. Save this Excel Workbook. On the “Save As” dialog box, click a file location in the left pane. For example, “Desktop” or a named folder. Type a file name in the “File name” text box. Click “Save.”

Read more ►

How to Set Margins in Excel

1. Start Microsoft Excel and open an existing spreadsheet on which you would like to set the margins.
2. Choose the 'File' menu and click 'Page Setup' to open the 'Page Setup' dialog box. The 'Page Setup' dialog box can be used to set up your page just how you would like it to appear.
3. Click on the 'Margins' tab at the top of the 'Page Setup' dialog box to show the margins for the spreadsheet that you currently have option.
4. Set the 'Left,' 'Top,' 'Right,' 'Bottom,' 'Header' and 'Footer' margins by clicking within the text box underneath each option to highlight the current margin setting.
5. Type a new measurement for whichever margins you would like to change or use the arrows to move up and down throughout the measurements you can select for each option.
6. Notice as you update each margin the preview in the center of the screen will change to reflect your new margin settings.
7. Use your mouse to click the 'OK' button to close the 'Page Setup' dialog box and set the margins you have chosen for your current Excel spreadsheet.

Read more ►

How to Create a Currency Converter With Microsoft Excel

1. Select the currency you want to convert to.
2. Find the current conversion rate. Sites such as Yahoo's currency converter will list the latest currency conversion rate for the various international currencies. Several sites offer this service, so perform an Internet search for the site you feel most comfortable with.
3. Open Microsoft Excel. Label the first three columns in the Excel spreadsheet as 'Current Currency,' 'Currency Conversion Factor' and 'Converted Currency.' You don't have to label the columns as indicated here, but for instructional purposes, it makes it easier to identify the column's contents. You can label them A, B and C if you'd like.
4. Insert the amount of the currency you wish to convert in the first column (Current Currency). If you're converting $453 US dollars into another currency, insert that amount in the first column.
5. Place the currency conversion factor in the second column.
6. Create a formula in the third column. Insert this formula in the third column ' column1/cell1 * colum2/cell1.' In this example, assuming you did not skip a row after your title, your dollar amount will be in A2 and your conversion amount will be in B2; therefore, your formula will read ' A2* B2.' Be sure to hit the 'Enter' key once you've put in the formula. It will automatically convert the currency.
7. Add formatting. If you want your converter to look nice, format the column by bolding, underlining or adding color.

Read more ►

How to Import More Than 65,536 Rows in Excel 2003

1. Click 'Tools,' select 'Macro' and choose 'Macros.'
2. Type a name for your macro in the 'Name' field, such as 'LargeFileImport,' and click 'Create.' The Visual Basic Editor will open automatically.
3. Double-click '(Name) Module' in the 'Properties' window and type 'LargeFileModule.'
4. Click the ' ' icon next to 'Microsoft Office Excel Objects.'
5. Double-click 'LargeFileModule' to open the 'Code' window.
6. Copy and paste the following into the 'Code' window:Sub LargeFileImport()'Dimension VariablesDim ResultStr As StringDim FileName As StringDim FileNum As IntegerDim Counter As Double'Ask User for File's NameFileName = InputBox('Please enter the Text File's name, e.g. test.txt')'Check for no entryIf FileName = '' Then End'Get Next Available File Handle NumberFileNum = FreeFile()'Open Text File For InputOpen FileName For Input As #FileNum'Turn Screen Updating OffApplication.ScreenUpdating = False'Create A New WorkBook With One Worksheet In ItWorkbooks.Add template:=xlWorksheet'Set The Counter to 1Counter = 1'Loop Until the End Of File Is ReachedDo While Seek(FileNum)
7. Click 'File' and select 'Close' to close the Visual Basic Editor.
8. Click 'Tools,' select 'Macro' and choose 'Macros.'
9. Select the 'LargeFileImport' macro from the 'Macros' dialog box and click 'Run.'
10. Enter the name of your file (myhugedocument.txt, for example) in the dialog box that appears. Excel will import the data, splitting it into multiple worksheets in order to circumvent Excel's line limit.

Read more ►

How to Restore All Hidden Columns in Excel

1. Open the spreadsheet in Excel.
2. Click on the arrow in the top left corner of the Excel spreadsheet. This highlights all data in the workbook.
3. Click 'Format' in the 'Cells' menu along the top of the page. A drop-down menu appears.
4. Click 'Hide Unhide' under the 'Visibility' heading, then click 'Unhide Columns.' Changes are applied instantly and all columns are visible.

Read more ►

How to Create a Simple Checkbook Register With Microsoft Excel

1. Open Microsoft Excel. Type in 'Check Register' in cell A1. Center this across the columns you will be using, from A through G. Select cell A1, hold down the shift key and select cell G1. After you highlight it, go to 'Format' and 'Merge Cells.' Then select 'Center' which is under 'Format' and 'Cells.'
2. Skip a row and make the headings starting in cell A4. Type in 'Item Number,' move to the next column and type in 'Date,' move to the next column and type in 'Description of Transaction,' continue moving and typing the headings as follows: 'Payment,' 'Done,' and 'Deposit.' Go to cell G3 and type in 'Balance.'
3. Format the cells. In cell G4 select 'Format' and 'Cells' under 'Numbers' select 'Currency' and the way you want the dollar amount shown in the category. Go to cell B6. Hold the shift key down and page down a few times. Then select 'Format,' 'Cells' and 'Date' and then the way you want the date to look. Do the same for the 'Payment,' 'Deposit' and 'Balance' columns but format them for money. Alternatively, you can format them as numbers with two decimals.
4. Select the 'Balance' column again and put in the following formula in cell G6: '=G5 F6-D6'. Copy the formula down by using 'Auto Fill' or 'Copy' and 'Paste.'
5. Start entering your data beginning with your current balance in G4, and then adding each transaction as you make it. Don't forget to put in debit transactions as well as any bills that you pay online. Add in all your deposits too.
6. Play with and test the numbers, as you want to be sure that it's working correctly.

Read more ►

How to Number the Rows in Excel 2007

Fill Column
1. Select the first cell in spreadsheet.
2. Type the starting value in that cell.
3. Type another value in the next cell to start sequence.
4. Select cells with values that you've typed inside.
5. Drag 'fill handle' to include number of cells that you want in your sequence.
ROW Function
6. Type '=ROW(A1)' into the first cell of the range you want to sequence. This will give you the number 1 in that cell.
7. Type specific number after '=ROW(A1)' to start numbering at a certain point. For example, typing '=ROW(A1),'2010-000' will create a starting number of 2010-001.
8. Drag 'fill handle' to include number of cells that you want in your sequence.

Read more ►

How to Use MS Excel 2007 to Calculate Sum

1. Enter values into a row or column of cells.
2. Navigate to the cell you want the sum to appear. You may pick any cell, even one nowhere near the cells you want to sum.
3. Click the 'AutoSum' button in the Editing Box at the far right of the Home tab. Excel will enter the Sum Function in the active cell, including a range of cells to add.
4. Make certain the cell range is correct (notice the dashed box for a visual representation of the cell range). Press the Enter key to confirm the Sum function.
5. Drag through another range of cells if the range Excel entered is not the range you need summed. In other words, left click into the first cell you want in the equation and move the mouse through the additional cells. Press the Enter key to confirm the function.

Read more ►