July 2013 - Excel

Browse » Home » Archives for July 2013

Sunday, July 28, 2013

How to Change Row Colors in Pivot Table

1. Open Microsoft Excel 2003, then open the workbook containing the pivot table you want to change the row colors.
2. Click the tab in the Excel workbook for the sheet that contains the pivot table. If workbook tabs are hidden, click the 'Tools' link in the menu bar, then click the 'Options' link in the drop-down menu to open the 'Options' dialog box. Click the 'View' tab in the dialog box, check the box next to 'Sheet tabs,' then click the 'OK' button.
3. Select one of the rows in the pivot table you want to change the color. Click the first cell in the row, then drag the pointer to the final cell. Do not select the row by clicking on the row number as this will result in the row color you apply extending beyond the limits of the pivot table.
4. Right click over the row you have selected and click the 'Format Cells' link in the context menu.
5. Click the 'Patterns' tab, then click the color you want from the set of colors displayed in the 'Cell Shading' section.
6. Click the 'OK' button to close the 'Format Cells' window and apply the color you chose to the row you selected. Repeat the process to apply color to other rows in the pivot table.

How to Remove Excel From Shared Mode

1. Click the Windows Start icon and select 'All Programs.' Click 'Microsoft Office,' then click 'Microsoft Excel.' The spreadsheet software opens.
2. Press the 'Ctrl' and the 'O' keys to open the 'Open' dialog window. Double-click the file you want to edit.
3. Click the 'Review' ribbon tab, then click 'Protect and Share Workbook.' This button opens a sharing configuration window.
4. Remove the check mark next to the 'Allow changes by more than one user at the same time.' Click 'OK' to save the changes.

How Do I Change Margins on One Page of a Document in Microsoft Word?

1. Highlight all of the text on the page where you want to change the margins.
2. Open the 'Page Setup' dialogue box.
3. Enter your desired margin sizes in each corresponding margin field.
4. Click the drop-down arrow next to 'Apply to' and choose 'Selected text.'
5. Click 'OK' to finish adding margins.

How to Create a Chart in Excel 2007

1. Create data that can be charted. Open Excel 2007, and either open an existing worksheet or use the default. Enter or create some data that supports the creation of a chart, for example the amount of sales by product over a period of time. This data should be in the form of a table, with the element values to be charted populating the left hand column and the data series, or information about the elements, in the cells across from each element. More than one series, for example the quarters in a year, can be included for each element. Title the data series across the top of the table and do not leave any blank spaces in the table.
2. Select the data to be charted. Left-click and drag a box around the data to select it. Make sure to include both the data and the labels.
3. Choose the chart type. Select 'Insert' on the Excel 2007 ribbon and then choose from the chart types listed in the 'Charts' section. For a chart type that's not visible, select the 'Other Charts' icon. The chart will be placed on the worksheet near the table of data. By default, the series will be listed across the bottom, or 'x-axis,' and the elements will be listed to the side. The element values will be listed on the left, or 'y-axis' of the chart.
4. Position the chart. Left-click on any white space on the chart, and move it to the desired location.
5. Format the chart. Right-click on the chart and a variety of options will appear in a dialog. Change the font displayed within the chart, change the chart type and select different chart data by selecting the relevant item from the list. To change the chart's appearance, select 'Format Chart Area,' and then select from the options to change the chart's 'Fill,' 'Border Color,' 'Border Styles,' 'Shadow' and '3-D Format.'

Saturday, July 27, 2013

How to Calculate IRR

1. Set up a new spreadsheet with each row showing the cash inflows and outflows by year. For example, Row 1 would have the cost of the new equipment at time zero (the date of purchase). Row 2 would have the expected manufacturing cost savings in year 1. Row 3 would have the savings in year 2, etc.
2. Choose 'Insert/Function' from the main menu. Select 'All Functions' and choose 'IRR' from the list.
3. A box will appear that asks you to define the values, and for a guess. Highlight your cash inflows and outflows in your spreadsheet to be the relevant values. For a guess, choose the default of .10. There will almost never be a need to make another, different guess, unless you are dealing with multiple IRRs.
4. The formula calculates your internal rate of return (IRR) in percentage format and inserts the answer into your chosen cell. If you do not wish the formula to round the percentage, choose 'Increase Decimal' on your toolbar to add decimal places.
5. Compare the calculated internal rate of return (IRR) to your benchmark rate for investments. For example, if your other alternative is to invest that initial capital outlay in another investment, you would compare the IRR to the rate you would earn on another investment. If the rate of return is higher than your benchmark rate, you would choose to invest in the project. If you are calculating IRRs on multiple potential projects, you would choose the project with the highest IRR.

How to Convert Mac Office 2003 Excel to Windows Excel 2003

1. Connect the flash drive to your Mac computer. In a moment you are going to see a desktop icon appear for the device.
2. Open Microsoft Office 2003 and load the file you want to convert over to Windows.
3. Click 'File,' 'Save' and select the flash drive as your save location.
4. Eject the flash drive by dragging it down to the trash can in the lower right corner of your desktop dock. Once the icon is gone you can safely remove the device from your Mac.
5. Connect the flash drive to your Windows computer. Launch Microsoft Office Excel 2003.
6. Click 'File,' followed by 'Open.' Select the flash drive and choose the Excel file you saved from the Mac. Click 'Open' and the file is opened into the program.

Friday, July 26, 2013

How to Learn Excel for Free on the Computer

1. Click 'Start,' click 'All Programs,' click 'Microsoft Office' and then click 'Microsoft Office Excel 2007' to display its Book1 - Microsoft Excel window.
2. Click the blue question mark — Microsoft Office Excel Help — button to display the Excel Help window. Scroll to 'Training' and then click on it to display a list of Training Topics. Click on 'Excel 2007 training courses' to display its window, which is subtitled: A roadmap to Excel 2007 training. Click on the title of any course listed here to display its website, read its title page, click 'Start this course' to display page 2, and then click 'Back' and 'Next' to navigate through its web pages. Most of these courses include practice exercises and self-evaluation tests. When you have finished a course, simply close your browser to redisplay the Excel 2007 training courses window in help.
3. Scroll to '1. GET FAMILIAR WITH EXCEL,' click 'UP TO SPEED WITH EXCEL 2007' to display its web page and then work through the course as described in step 2. Click 'Get to know Excel 2007: Create your first workbook' and then work through its course. Click 'Get to know Excel 2007: Enter formulas' and then work through its course. Click 'Learn how to figure out dates using formulas in Excel 2007' and then work through its course. Completing these four courses should help learn how to use the basic features of Excel 2007.
4. As needed, you can continue with the other courses in: 2. CREATE CHARTS IN EXCEL, 3. CREATE PIVOTTABLE REPORTS, 4. SHARE DATA WITH OTHER PEOPLE, and 5. LEARN GENERAL OFFICE SKILLS.

How to Spell Check Multiple Worksheets in Excel

1. Open Excel by double-clicking on the program's icon. Open the document with multiple worksheets that you want to spell check.
2. Click on the first bottom tab while holding down the 'Shift' key. Continue holding down the 'Shift' key and click on the remaining tabs to highlight all worksheets that need checking for spelling or grammatical errors.
3. Select the 'Tools' menu option and then choose 'Spelling.' A popup box appears asking, 'Do you want to continue checking at the beginning of the sheet?' Click 'Yes.'
4. Review each word that the computer doesn't recognize. The spell check takes you through each worksheet. The popup box gives you options for each spelling mistake: 'Ignore,' 'Change,' 'Add,' 'AutoCorrect,' 'Ignore All' or 'Change All.'
5. Choose the option that fits your needs for each word. Clicking on that option takes you to the next error.
6. Go through each potential error that pops up. A popup box then appears stating, 'The spelling check is complete for the selected sheets.' Click on 'OK.'
7. Hit 'Save' to save any changes you made to your worksheets. Then, click on one of the tabs highlighted so further changes you make don't affect multiple worksheets.

How to Calculate Compound Interest for Excel 2003

1. Title cell A1 'Principal,' cell A2 'Compounding Periods,' cell A3 'Yearly Rate,' cell A4 'Time Period,' cell A5 'Periodic Rate' and cell A6 'Total Interest.'
2. Enter the value for the amount of money you started with in cell B1, the number of times interest compounds each year in cell B2, the yearly interest rate in cell B3 and the number of years you will leave the money in the account in cell B4.
3. Enter the formula '=B3/(B2*100)' in cell B5 to calculate the periodic interest rate. You have to divide by 100 to convert from a percentage to a decimal.
4. Enter the formula '=B1*(1 B5)^(B2*B4)-B1' in cell B6 to find the total interest. Once you have entered the formula, the total interest for your specified time period will be displayed.

How to Merge or Split Cells Data in Microsoft Excel

1. Open Excel 2010. Type some random numbers in cells A1, B1 and C1. Highlight cells A1, B1 and C1.
2. Click the 'Home' tab and select 'Merge and Center.' The cells will merge as one large cell displaying information from cell A1.
3. Split the merged cells by selecting the newly merged cell. Click 'Merge and Center' icon. The cells will split displaying three separate cells again.

How to Create a Dropdown List in Excel 2007

1. Type a list of entries for the drop-down list in a single column. For example, if you want a drop-down list for the days of the week, type 'Monday' in cell A1, 'Tuesday' in cell A2, 'Wednesday' in cell A3, 'Thursday' in cell A4, 'Friday' in cell A5, 'Saturday' in cell A6, and 'Sunday' in cell A7.
2. Click on the cell where the drop-down list will go.
3. Click on the 'Data' tab, then click on 'Data Validation,' then click on 'Data Validation' again.
4. Click on the 'Settings' tab in the dialog box.
5. Click on 'List' from the 'Allow' box.
6. Enter the cell references into the 'Source' text box. In the above example, you would type '=A1:A7.' The 'in-cell dropdown' check box should already be checked. If it isn't, make sure to check it.
7. Click on the 'OK' button. Excel will insert a list into the chosen location.

Thursday, July 25, 2013

How to Create a Spreadsheet Template in Excel

1. Create a workbook file with all the basic settings you use: sheets, default text (such as column and row labels and page headers and footers), formulas, macros and anything else you want in new workbooks based on the template. You probably don't want to enter a lot of data into the template, but you might.
2. Open the File menu and select Save As.
3. In the 'Save as type' box, click Template (*.xlt).
4. In the 'Save in' box, select the folder where you want to store the template. To create a normal template, select the Templates folder, which should have automatically opened when you chose the Template type in the previous step.
5. To make this template the default (so that it's automatically selected), save the folder in the XLStart folder. The XLStart folder should be located in the following place: C:\Windows\Application Data\Microsoft\Excel\XLStart.
6. In the 'File name' box, type a name for the template (like 'Budget template'). Use the name 'book' if you want this template to be the default.
7. Click Save.

How to Add Comments to an Excel Worksheet

1. Open the Excel spreadsheet you wish to change.
2. Select the cell into which you'll write your comment.
3. Choose Comment under the Insert menu.
4. Type your comment into the resulting box.
5. Finish typing and click outside the box to save your comment.

How to Make a Gantt Chart in Excel

1. Open a new Excel worksheet. For the purposes of this article, enter 'Task' in cell A1, 'Start Date' in cell B1 and 'Length' in cell C1. Type in a few lines of chart data under the appropriate headings. The first column names the tasks, the second column provides a start date, and the third column lists the number of days each task is expected to take.
2. Select the data table by clicking the first cell and dragging across all the cells you typed data into. Go to the Insert tab and select 'Bar' from the Chart panel. Click on 'Stacked Bar' under the 2D heading as the chart subtype.
3. Click on 'Select Data' in the Design tab under Chart Tools; the Select Data Source window will open. Remove any entries listed under Legend Entries (Series) by selecting them and clicking 'Remove.'
4. Click on 'Add' to bring up the Edit Series dialog box. Type 'Start Dates' in the Series name field. Delete any values in the Series values field. Click inside the field, then drag your mouse from the first data cell to the last data cell in the Start Dates column; you will see the data range appear in the field. Click on 'OK' to return to the Select Data Source window.
5. Repeat Step 4 to add a new data series. Name it 'Length' and select the data in the Length column. Return to the Select Data Source window.
6. Click on 'Edit' under Horizontal (Category) Axis Labels and drag your mouse from the first data cell to the last data cell in the Tasks column (not including the heading). Click on 'OK' to return to the Select Data Source window, and 'OK' again to return to the worksheet.
7. Double-click on the colored area in the left side of any of the bars. The Format Data Point box will open. Click on 'Fill' and change it to 'No Fill.' Click on 'Border Color' and change it to 'No Line.' Click 'Close' to apply the changes. The first color in the bars will be gone.
8. Double-click the category axis, which is the area where the task names are listed. The Format Axis dialog box will open. Under Axis Options, click the 'Categories in Reverse Order' check box. The chart order will be flip-flopped.
9. Double-click the value axis, which is the white area where the start dates are shown. The 'Format Axis' dialog will open. Under Alignment, change the Custom Angle to 45 degrees. Click 'OK' to apply these changes.
10. Click on the chart legend and press the 'Delete' key to remove it.

How to Create a Template in Excel 2007

1. Open Microsoft Excel 2007 on the computer.
2. Click the 'Microsoft Office' button from the main program menu, and then click 'Open.' Browse to and double-click the existing Excel workbook or worksheet that you want to use as a template. If you don't have an existing spreadsheet, click 'New' to create a new Excel document to use as a template.
3. Enter the information and add the elements that you want to include in the Excel template. This should include all the data, graphics, text, macros and formulas that the template requires to work properly.
4. Click the 'Microsoft Office' button on the main menu, and then click 'Save As.'
5. Type a name for the template in the 'File Name' box.
6. Click 'Excel Template' in the 'Save As Type' box. If the workbook contains macros that you need to include in the template, click 'Excel Macro-Enabled Template' in the 'Save As Type' box. Click 'Save.'

How to Expand a Row in Excel 2003

1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click 'Microsoft Excel' to open the software.
2. Click 'File,' then click 'Open.' Double-click the Excel file with the rows you want to edit. The file loads in the Excel editor.
3. Hover the mouse over the line below the row you want to expand. Notice the cursor displays an arrow. Click the row's grid line and expand the height.
4. Click the 'Save' button to save the changes. Repeat these steps for each row you want to edit.

How to Print in Excel 2007

Print Whole Worksheet
1. Open your document in Microsoft Excel. Click on a cell within the table to activate it.
2. Click the Microsoft Office button at the top of the worksheet. The Microsoft Office button will appear as a light blue circle with the Office logo inside. Click 'Print' or use the keyboard shortcut 'Ctrl' and 'P,' followed by 'OK.'
3. Click 'Table' under the 'Print What' option that will appear. Clicking 'Table' will print the entire chart.
Print Part of a Worksheet
4. Click on a cell and drag your mouse cursor to the rest of the table you wish to print. The table will highlight in a shaded color to indicate the highlighted cells.
5. Navigate your cursor to the 'Page Setup' group located inside the new 'Page Layout' tab at the top of the worksheet. Click 'Print Area.'
6. Click 'Set Print Area' to set the highlighted cells for printing. Print the worksheet by pressing 'Ctrl' and 'P,' followed by 'OK.'

Wednesday, July 24, 2013

How to Use Security Features in Excel

1. Enable security alerts from suspicious website links. Click the Microsoft Office button and choose 'Excel Options.' Choose 'Trust Center,' then click 'Trust Center Settings' and select 'Privacy Options.' Make sure you have 'Check Office documents that are from or link to suspicious Web sites' checked.
2. Select 'Enable Content' on the message bar if your workbook has external content. Choose to unblock the external content when the security dialog box appears.
3. Access the Trust Center to view your security and privacy settings for Active-X controls, add-ins and macros. Click on the Microsoft Office button and go to 'Excel Options.' From there, go to 'Trust Center' and then 'Trust Center Settings.'
4. Password-protect your workbook. You can choose to require a password before anyone can open or modify your workbook. Select 'Tools' from the 'Save As' dialog box. Click 'General Options' and input your passwords in the designated area.
5. Set the VBA / Macro Security to determine how VBA code and XML macros are executed. Go to 'Tools,' then 'Macros.' Set the security setting to at least medium.
6. Protect your worksheet from unauthorized editing by using Worksheet Protection. This allows other people to view most of the worksheet, but only edit certain cells. Click 'Tools,' go to 'Protection' and select 'Worksheet Protection.'

How to Include Grand Totals in Excel Pivot Charts

1. Open the PivotChart.
2. Click the 'Field List Button' image on the PivotChart toolbar if the Field List Button is not showing.
3. Click the field and go to 'Totals.'
4. Click 'Row Area' to add the grand total to the row area and 'Column Area' to add it to the column.

How to Insert a Template in Excel 2007

1. Hold the 'Windows' key down and press 'E' to open Windows Explorer.
2. Navigate to where you saved the template you want to insert into Excel.
3. Open another Windows Explorer window, type 'C:\Users\USERNAME\AppData\Roaming\Microsoft\Templates' in the top address field and press 'Enter.' When typing the address, change 'USERNAME' to your Windows username, which is listed beneath your picture on the 'Start' menu.Do not try to navigate into this folder by clicking folders. If you maintain Windows 7's default settings, you will not find the 'AppData' folder because it is a hidden system folder. However, by directly typing the location in the address bar, you bypass the hidden nature of the folders, which saves you the trouble of changing your Windows configuration.
4. Hold the 'Alt' key and press 'Tab' to return to the previous Windows Explorer window.
5. Click and begin dragging the Excel template file, which should have the XLTS extension.
6. Hold the 'Alt' key and press 'Tab' again to return to the template folder. Do not release your left mouse button yet.
7. Continue dragging the template file and drop it into the right pane by releasing the left mouse button. This inserts the template into Excel and makes it available for use.

How to Upgrade Microsoft Excel

1. Insert the install CD or DVD into the disk drive.
2. Enter your product key when prompted. This will occur after the install screen has popped up. If the install screen does not come up automatically, then navigate to your CD/DVD drive through 'My Computer' and double click on the Microsoft Office disk icon. The product key can be found on a sticker on the CD case inside your Microsoft Office box.
3. Accept the Microsoft License agreement.
4. Select 'Install Now' or 'Customize.' 'Install Now' will upgrade all previous versions of the Microsoft Office program that correspond to your recently purchased Office software. 'Customize' will allow you to specify which applications and features are upgraded.
5. Activate your product after the installation is completed. Activating over the internet is the quickest method if you have an internet connection. If you decline activation at this time, you will be prompted to activate each time you open an Office program.

Tuesday, July 23, 2013

How to Make a Basic Bookkeeping Spreadsheet in Excel

1. Examine your personal or business finances to determine the best way to use Excel. Different options are keeping track of accounts receivable and payable, including payroll, rent or utilities and any raw materials used in the production process.
2. Open Excel on your computer and create a new spreadsheet by clicking on the blank page icon at the top left of your screen.
3. Orient yourself with Excel's format. The page is made up of a grid which creates boxes. Information can be typed into each box.
4. Title the headings for your spreadsheet's rows and columns. For example, you may choose to use the first column to list all the dates in a given month, the second column to list any accounts receivable transactions and the third column to list any accounts payable transactions.
5. Fill in the information into the appropriate boxes. For example, if you or your company paid out $15,000 in payroll checks on the first of the month, you would write '15,000' in the corresponding box.
6. Add the total dollar amounts from each column -- in our example, you'd have a total for accounts receivable and a total for accounts payable. You can also calculate average expenditures and profits by selecting the 'Subtotals' option from the drop-down box under the 'Data' tab.
7. Sort information from high to low or low to high by clicking on the 'Sort' option from the drop-down box under the 'Data' tab; this gives you the option of seeing which entries had the highest or lowest values.

How to Paste Into PowerPoint and Remove Gridlines

1. Select the text, image or object that you want to include in your PowerPoint presentation. For example, highlight a range of cells in an Excel spreadsheet. Press 'Ctrl C' on your computer's keyboard.
2. Press 'Ctrl M' in PowerPoint to create a new slide and then press 'Ctrl V' to paste the object. If you copied a spreadsheet, for instance, it will paste in as a spreadsheet object. Note, that you can link or embed things such as spreadsheets, charts and Word documents.
3. Click on the 'View' tab in PowerPoint and click in the check box next to 'Gridlines' to remove the gridlines from the design view. If you want to remove the gridlines from a spreadsheet that you've pasted in, click inside the spreadsheet in PowerPoint. Press 'Ctrl A' to select all of the content in the spreadsheet. Click on the 'Table Tools: Design' tab and in the 'Table Styles' section click on the 'Borders' button and choose 'No Borders.'

How to Assign a Macro to a Cell in Excel

1. Right-click the sheet tab you want the change to happen in. The sheet tabs are at the very bottom of the worksheet.
2. Click 'View Code.'
3. Cut and post the following code into the worksheet:Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Address = '$A$1' ThenRange('A10') = 'Your text here'End IfEnd Sub
4. Change the code to suit your needs. The above code writes 'Your text here' into cell 'A10' when cell 'A1' is clicked. Change the 'A1' in 'Target.Address = '$A$1'' to the cell you want to click, then change Range('A10') = 'Your text here' to indicate the cell you want the event to happen in and the text you want to appear.

Monday, July 22, 2013

How to Create Frequency Relative Frequency on Excel Using a Pivot Table

1. Gather all of your data and compile it into a list on Microsoft Excel. Select and highlight the entire list of data points. Right click it and choose the option 'Sort from smallest to largest.' Now that the data points are in numerical order, the task of creating a frequency distribution table will be much easier.
2. Determine the interval size and the number of classes that will be used for your distribution table. For example you may have something like 5 classes of intervals: 1-5, 5-10, 10-15, 15-20, and 20-25. These classes will be used to organize the data points.
3. Start setting up the table on Microsoft Excel. In column A, set up the different classes and label the column as 'Class.' Column B will be for the 'Frequency.' Count up the number of data points that fall in each class interval, and state the frequency in column B. Select the empty cell below the list of frequencies in Column B and use the sum function to add up the values. This result will give you the total number of data points. In column C, set up the 'Relative Frequency.' Relative Frequency is calculated simply by dividing the individual frequencies by the total number of data values. Select an empty cell at the end of the 'Relative Frequency' column and perform the 'sum' function. The sum for the 'Relative Frequency' column should be 1.00.
4. Select the 'Insert' tab on Microsoft Excel, and select the PivotTable button. Use the selection icon and select the entire table. Place a check next to 'New Worksheet,' so that the table appears on a separate sheet. Click 'OK.' A column in the right side of the screen will appear. Place a check next to the following fields that you'd like to add to your report: 'Class,' 'Frequency,' and 'Relative Frequency.'
5. Use the 'Options' and 'Design' tabs to edit and format the PivotTable.

How to Open Excel Templates

1. Open Excel. Click 'File' if you are using Excel 2010 or click the 'Office' button if you are using Excel 2007. The Office menu will open.
2. Click 'New' on the Office menu. A window called 'New Workbook' will open. You can search for and open Excel templates here.
3. Look to the 'Templates' section on the 'New Workbook' menu. You can view recently used or installed templates here.
4. Click one of the Excel template categories to view an Excel template. Categories you can choose from are 'Blank and Recent' and 'Installed Templates.'
5. Select the template you want to open and click 'Create' or 'OK' depending on whether you are opening a recent template or an installed template. The template will open in a new workbook.

How to Compare Workbooks

Comparing Numeric Values
1. Open a new workbook, and name it CompareWorkbooks.xls.
2. Look at the values that you want to compare in both workbooks. For example, you may wish to compare cells B2 to F7.
3. In cell B2 (or the upper left cell) of the CompareWorkbooks workbook, insert the formula '=[Workbook1.xls]Sheet1!A1-[Workbook2.xls]Sheet1!A1.' If the Workbooks or sheets are named differently, change the formula as appropriate.
4. Copy the formula from that cell into all of the applicable cells in the CompareWorkbooks workbook. A '0' in a cell means that the cells in the two compared workbooks are identical. A value in the cell represents the difference between the values in the corresponding cells in the compared workbooks.
Comparing Almost Identical Workbooks
5. To compare workbooks that are virtually identical, open a new workbook, and name it CompareWorkbooks.xls.
6. Look at the values that you want to compare in both workbooks. For example, you may wish to compare cells B2 to F7.
7. In cell B2 (or the upper left cell) of the CompareWorkbooks workbook, insert the formula '=IF([Workbook1.xls]Sheet1!A1
[Workbook2.xls]Sheet1!A1,'DIFF VALUE',''). If the Workbooks or sheets are named differently, change the formula as appropriate.
8. Copy the formula from that cell into all of the applicable cells in the CompareWorkbooks workbook. If the words 'DIFF VALUE' appear in a cell, it means that the corresponding cells in the two compared workbooks are different.

Sunday, July 21, 2013

How to Graph a Trend Analysis in Microsoft Excel

1. Enter the data in Microsoft Excel on which the trend analysis will be performed.
2. Create a chart from the data entered. The chart must be an unstacked, two-dimensional chart that is formatted as bars, columns or lines.
3. Click anywhere in the chart. This will display the Chart Tools and add the Design, Layout and Format tabs at the top of the worksheet.
4. Click Trendline under the Layout tab in the Analysis grouping. This will create a drop-down showing the types of trend analysis that can be performed on your data chart.
5. Select one of the predefined trend-line options. The options are linear, exponential, linear forecast and two-period moving average. This will automatically graph the trend analysis in the chart.

How to Calculate Total Number of Rows in MS Excel

1. Log on to your computer and open Microsoft Excel by clicking on the 'Start' button, choosing 'All Programs' from the menu and selecting 'Microsoft Office.' Choose 'Excel' from the list of programs, then click the 'File' menu and choose 'Open' from the menu.
2. Hold the 'Ctrl' key down and tap the 'End' key on your keyboard. This will automatically move the cursor to the last row in the spreadsheet that contains an entry. If you simply want to know how many rows are in your spreadsheet you can use that row number. If you need to know how many rows actually contain data, move on to the next step.
3. Position your cursor underneath the last row that currently contains data and type '=COUNT(' hit the up arrow on your keyboard, then the period key. Hit the up arrow again and continue pressing the up arrow until you reach the top of the spreadsheet. When you have reached the top of the spreadsheet type ')' to close the calculation. You will see the number of rows that contain data.
4. Type the formula directly if you are working with a large spreadsheet. If you are working with a spreadsheet that contains a large number of rows, it will be easier to enter the formula directly. To enter the formula directly type '=COUNT(firstrow:lastrow)' For instance, if the first row in your spreadsheet is A1 and the last is Z1, the formula would be '=(COUNT(A1:Z1).

How to Remove Passwords in Excel 2007

1. Open the password-protected Excel 2007 file by double-clicking it from Windows or clicking 'Open' from the Office menu at the upper left of the screen.
2. Attempt to modify the document by double-clicking a cell and attempting to change a value. An error message will appear and warn you about your attempted action.
3. Click the 'Review' tab at the top of the screen.
4. Click 'Unprotect Sheet' and supply the document's password when prompted. Click 'OK' to submit the password.
5. Click the Office icon and then click 'Save.' The document will be saved in its unprotected state, and a password will no longer be needed to modify it.

How to Turn Excel 2007 Documents to Excel 2003

1. Open the Excel 2007 file that you want to convert into Excel 2003.
2. Click the 'File' button in the upper left hand corner.
3. Highlight 'Save As,' and then select 'Excel 97-2003 Workbook' as your save option. This will save your spreadsheet as '.xls', which is the Excel 2003 format.

Saturday, July 20, 2013

How to Write Macros in Excel 2003

1. Click 'Tools > Macros > Record New Macro...' in the top menu.
2. Choose a name for your macro and type it into the 'Macro Name' textbox. For example, if you want to create a button that sets a certain cell to a special format, you might name the macro 'Special Format Macro.'
3. Type the shortcut key you want to use to access the macro into the 'Shortcut Key' textbox. If the example, you might choose 'S' as your shortcut key.
4. Press 'OK.'
5. Make the changes you wish to record into the macro. For example, you could make the text of the cell bold and the borders green.
6. Press the stop button on the Stop Recording Dialog. The button has a blue square on it.

How to Label Category Axis (X) in Excel 2007

1. Open Excel 2007 and open a workbook. Click the 'Office' button and select 'Open.' Browse your files and locate the workbook you wish to use, and double click it.
2. Locate a range of data that contains row headers. Highlight the data and the row header information. The row header information will appear in the label category x axis. Click 'Insert' on the ribbon. Select a 'Line' chart to represent your data. The chart appears in your worksheet.
3. Click the outside border of the chart. The Chart Tools tab appears above the ribbon. Select the 'Layout' tab. Click the 'Axis Title' icon. A drop down list appears. Click the 'Primary Horizontal Axis Title.' Select 'Title below Axis.' Your horizontal or x axis is now labeled.

Friday, July 19, 2013

How to Create an Exploding Pie Chart in Excel

1. Start Microsoft Excel 2007 and open an existing spreadsheet that creates a pie chart or create a pie chart from existing data in the spreadsheet.
2. Click on the pie chart to select the entire pie chart. Be careful to click in the white area of the chart so the entire pie chart is selected. You can tell the whole pie chart is selected because it will be surrounded by a light blue line.
3. Hover your mouse over the piece of pie that you want to explode. Your mouse cursor will turn into a 4-headed arrow.
4. Click and drag the slice of pie outwards, away from the rest of the pie in the chart. Notice the blue outline that will grow outside of the pie chart. This line represents how far away the pieces will explode from each other.
5. Release the mouse button. The pie chart will now be exploded with all of the slices of pie broken away from each other.
6. Click and drag the individual pieces of pie to their new location if you would like to manually adjust the exploding pie chart pieces.

How to Get Rid of Circular References in Excel

Excel 2003
1. Open the 'Tools' drop-down menu at the top of the screen if the 'Circular Reference' toolbar is not displayed, then click 'Customize.'
2. Click on the 'Toolbars' tab at the top of the window that appears, then check the 'Circular Reference' check box if it is not already checked. Click the 'OK' button and close the window.
3. Click on and highlight the first cell in the 'Navigate Circular Reference' box within the 'Circular Reference' toolbar.
4. Examine the formula in the highlighted cell carefully for errors; i.e., referencing the cell itself. If you find no such mistake that could be the cause of the circular reference message, click to the next cell in the 'Navigate Circular Reference' box. (If the word 'Circular' is shown in the status bar without referencing which cell, switch to another worksheet.)
5. Continue through each cell, reviewing and correcting any circular references present in each until the status bar no longer shows the word 'Circular.'
Excel 2007
6. Click on the 'Formulas' tab at the top of the window.
7. Click on the arrow on the 'Error Checking' button in the 'Formula Auditing' box.
8. Select the first cell listed in the submenu under 'Circular References.'
9. Examine the formula in the cell closely and look for self-references. If you find no cause of the circular reference in the cell, click to the next one in the 'Circular References' submenu.
10. Repeat the previous step and review each cell in turn until the words 'Circular References' disappear from the status bar.

Thursday, July 18, 2013

How to Use Excel Query

Query a Database
1. Open the Excel worksheet into which you want to import data.
2. In Excel 2003, go to the 'Data' tab, point to 'Import External Data' and select 'New Database Query' to query a database. In Excel 2007 or 2010, go to the 'Data' tab and select 'From Access' in the 'External Data' group.
3. Select the database you want to query and click 'Open.' Select the table in this database from which you want to import data and click 'OK.'
4. Choose 'Table,' 'PivotTable Report' or 'PivotChart and PivotTable Report' under 'Select How You Want to View This Data in Your Workbook.'
5. Enter a range of cells in the current worksheet and click 'OK.' The query will import the database table you selected into the current worksheet.
Query a Web Page
6. Open the worksheet into which you want to import data from a web page.
7. In Excel 2003, go to the 'Data' menu, point to 'Import External Data' and select 'New Web Query.' In Excel 2007 or 2010, go to the 'Data' tab and click on 'From Web' in the 'Get External Data' group. The 'New Web Query' dialog box will open.
8. Enter the address of the web page you want to query in the 'Address' field and click 'Go.'
9. Click the arrow next to the table you want to import. If you do not see arrows, click the 'Show Icons' button on the 'New Web Query' toolbar. To import several tables, simply click the arrow next to each one.
10. Click 'Import.' Enter the range in the current worksheet where you want to place the data. Click 'OK.' The query will import the table or tables you selected into the current worksheet.

How to Make a Pie Chart on Microsoft Excel 2003

1. Open MS Excel 2003. Click on the 'File' menu at the top of the screen and select 'Open.' Browse through the folders on your computer to locate the file with the data. Click on the file to select it and click on the 'Open' button to open the file.
2. Click on the Chart button which is in a tool bar at the top of the screen. This button looks like a bar graph and will open the Chart Wizard dialog box. You can also click on the 'Insert' menu and then select 'Chart' to open the wizard as well.
3. Click on 'Pie' from the list of chart types in the first step of the 'Chart Wizard' box. Select the type of pie chart you want for your report. Click 'Next' for the 'Chart Source Data' box.
4. Click back into your spreadsheet and highlight the data you want to graph. This array will populate in the 'Chart Source Data' box. Click 'Next' to open the 'Chart Options' Box.
5. Click through the tabs at the top of the 'Chart Options' box to select options you want to display with your chart. This includes a chart title, legend, and labels. When finished, click 'Next' to open the 'Chart Location' box.
6. Select 'As Object In' if you want to place the chart in your current work sheet. Select which worksheet in the workbook you want the chart to display in. Click 'Finish' to display the chart.

How to Create Frequency Distribution for Executions by Year in Excel

1. Open a new Excel 2010 worksheet. Select cell 'A1' and enter the years for your data into column 'A.' When you have all the years entered, select cell 'B1' and enter in the corresponding number of executions in column 'B.'
2. Click on cell 'C1.' Enter in the bin ranges that you want to sort the frequency by into column 'C.' These numbers will represent the top end of the ranges. If you enter '50,' '75' and '100,' your frequency distribution will have three categories: the executions per year that range between 0 and 50, the executions per year that range from 51 to 75, and the executions per year that range from 76 to 100. Enter as many bin range numbers as you need.
3. Click the 'Data' tab at the top of the screen. Select the 'Data Analysis' button from the toolbar. Select 'Histogram' from the list of options that appear and click 'OK.'
4. Place your cursor into the 'Input Range' field. Select cell 'A1' and hold down the mouse button. Drag the mouse to the last cell that holds information in column 'B' and release the button.
5. Place your cursor into the 'Bin Range' field. Select cell 'C1' and drag the mouse down until you reach the last cell that holds data in that column.
6. Place a check next to 'Chart Output' if you want to get a graphical representation of the frequency distribution. Place a checkmark next to 'Cumulative Percentage.' Click 'OK' to create the frequency distribution. It will appear as a new worksheet on the bottom of the Excel window. Click on the worksheet to see the frequency distribution and its associated histogram.

How to Add DataPig Buttons to the Excel 2007 Ribbon

1. Open Microsoft Excel 2007. Select the “Office” button and “Open” from the menu. Click on the file named “Custom_UI_Builder.xlsm.” Click the “Open” button.
2. Click the drop-down arrow in column “A” located under “Tab to Use.” Select the name for the Ribbon Tab where you want the button to appear.
3. Type a name for your tab in column “D” under “Tab Name” if you selected to put the button in “My Own Custom Tab” in column “A.” Otherwise, leave this cell blank.
4. Type group name in column “E” and a button label in column “F.” Select the button size using the drop-down box in column “G.”
5. Click the “Find Image” button. Select an image from the “MSO Image Finder” window.
6. Repeat Steps 2 through 5 in each row until you’ve defined the cells for every button you want to create.
7. Click the “Output Now” button. Select the radial button next to the type of output you want. Click the “Go” button.

How to Use a List Box in Excel

1. Select the cells on which you want to use the current list box.
2. Click 'Data' from the top menu; then select 'Validation' from the drop-down menu.
3. Select 'List' from the Allow drop-down menu.
4. Enter the data you want in the list boxes into the Source text box, separated by commas if you don't want to display options elsewhere on the worksheet.
5. Click the image of the grid with an arrow to take back to the spreadsheet, then highlight the data you want in the list box to create list box options from existing cells.
6. Check the 'In-cell Dropdown' box.
7. Press the 'OK' button to create the list box.

Wednesday, July 17, 2013

Filter Mode Is Stuck on Microsoft Excel

1. Select the column or columns, by clicking the letters at the top of the spreadsheet, that you have filtered. Click the 'Data' menu at the top of the window, move your mouse over 'Filter,' and select 'AutoFilter.' This will remove the filter from your data.
2. Click the 'Tools' menu, then select 'Options' from the drop-down menu. Select the 'Calculation' tab at the top of the Options window.
3. Click the box next to 'Manual,' and click 'OK' to close the window. Excel will no longer perform calculations automatically.
4. Select your data, and click the 'Tools' menu. Move your mouse over 'Filter,' and select 'AutoFilter.' Click the arrow at the top of the column that you want to you for filtering your data. Select your desired option, and the data will be filtered, but the message in the status box will tell you how many records you have filtered, instead of being stuck saying 'Filter Mode.'

How to Save on 2007 Excel for 2003 Without Macros

Save a Single File in Compatibility Mode
1. Launch Excel 2007 and open the document you want to save for Excel 2003 accessibility.
2. Click the 'Office' button, in the upper-left corner of the screen. This is the round button with the Microsoft Office logo on it, which turns gold when you hover over it with your mouse pointer.
3. Click 'Save As' and select a location to save your document on your computer.
4. Enter a file name in the 'File name' field.
5. Select 'Excel 97-2003 Workbook (*.xls)' from the drop-down menu in the 'Save as type' field.
6. Click 'Save.'
Set Compatibility Mode as Default
7. Launch Excel and click the 'Office button' in the upper left corner.
8. Select 'Excel Options' from the bottom of the menu window.
9. Select 'Save' from the left menu bar.
10. Choose 'Excel 97-2003 Workbook (*.xls)' from the drop-down menu in the 'Save files in this format' field.
11. Click 'OK.'

How to Auto Fill Rows in Excel

Sequences
1. Launch Microsoft Excel or create a new, blank worksheet to experiment with.
2. Click the first cell in the first column (Cell 'A1').
3. Type the number '1' in the cell and press 'Enter' to store the cell contents and move to the next cell in the column (Cell 'A2').
4. Type the number '2' in the cell and press 'Enter' to store the cell contents and move to the next cell in the column (Cell 'A3').
5. Type the number '3' in the cell and press 'Enter' to store the cell contents and move to the next cell in the column (Cell 'A4').
6. Click cell 'A1' and hold down the mouse button while dragging the mouse pointer down to cell 'A3.' Release the mouse pointer to select the first three cells.
7. Move the mouse to the lower-right corner of cell 'A3' while the three cells are selected until the mouse pointer changes to a small black cross with no arrows.
8. Click and hold the mouse button and drag the mouse pointer down the column for a few cells and release it. Notice that Excel automatically continued the sequence of numbers in the column based upon the selected numbers. The same feature also works with dates.
Formulas
9. Launch Microsoft Excel or create a new, blank worksheet to experiment with.
10. Click in the first cell in the second column (Cell 'B1') and type the word 'Rent' in the cell. Press the 'Tab' key to store the cell contents and move to the next cell in the row.
11. Type the word 'Power' and press 'Tab.' Repeat this process for the words 'Food' and 'Phones.'
12. Click in the second cell in the first column (Cell 'A2').
13. Type 'January, 2012' in the cell and press 'Enter' to store the value and move to the next cell down the column.
14. Click the cell 'A2' and point at the lower-right corner of the cell until the mouse pointer turns into a small black cross with no arrows.
15. Click and hold the mouse button and drag the mouse pointer down to cell 'A13' to fill in the date sequence automatically.
16. Type the number '750' in cell 'B2' to represent the rent for January of 2012. Press 'Tab' and type the number '75' to represent the power bill, and press 'Tab.' Repeat using '200' for food and '120' for phones.
17. Click cell 'B2' and hold down the mouse button. Drag the mouse pointer across to cell 'E2' and release the button.
18. Point the mouse pointer at the lower-right corner of cell 'E2' until the mouse pointer turns into a small black cross with no arrows.
19. Click and hold the mouse button down and drag the mouse pointer down to cell 'E13' to copy the values of all four columns to each of the months in the list.
20. Click cell 'F2' and click the 'AutoSum' button in the 'Editing' group on the home tab. Press 'Enter' to store the formula.
21. Point the mouse pointer at the lower-right corner of cell 'F2' until the mouse pointer turns into a small black cross with no arrows. Click and hold the mouse button down and drag the mouse pointer down to cell 'F13' to copy the formula for the sums of the rows.

Tuesday, July 16, 2013

How to Format Text As All Capitals in Excel 2007

Converting the Lower-Case Text to Upper-Case Text
1. Open your workbook in Excel 2007.
2. Click in the cell immediately below the cell containing lower-case text.
3. Type, without the quotation marks, '=UPPER(' and click the cell containing the text you want to capitalize, then type ')' and then press 'Enter.'
Replacing the Lower-Case Text with Upper-Case Text
4. Click the cell containing the new capital letters to select it.
5. Right-click, then choose 'Copy.'
6. Click the cell containing the original lower-case text, right-click again, then choose 'Paste Special....' Under 'Paste,' choose 'Values,' then click 'OK.'

How to Edit a URL in Excel With Macros

Replace Characters
1. Click the 'File' menu, click 'Options,' then click 'Customize ribbon.' Click the 'Developer' check box, then click 'OK' to close the 'Options' window. Word will display the 'Developer' tab.
2. Type the following formula into any cell in the current workbook. This formula creates a hyperlinked URL in the active cell.=HYPERLINK('http://www.whitehouse.net', 'White House')
3. Click the 'Developer' tab's 'Visual Basic' button to enter the VBA programming environment, then paste the following program into the window. This program edits a URL by using the VBA 'Replace' function. The first argument of this function is the string in which you want to make a replacement. The second argument is the text you want to replace and the third argument is the replacement text itself.Public Sub editURL()Dim URL, url2URL = ActiveCell.Formulaurl2 = Replace(URL, 'White House', 'Oval Office')ActiveCell.Formula = url2End Sub
4. Click any statement in the program, then click the 'Run' menu's 'Run' command to execute the program.
5. Click the 'Excel' icon on the taskbar to return to Excel. Notice that the 'Oval Office' text now appears in place of the original 'White House' text, indicating your program's editing of the URL was successful.
Replace Entire URL
6. Click the 'Developer' tab's 'Visual Basic' button to enter the environment for creating VBA programs.
7. Use the instructions from Step 2 of the previous section to enter any URL in a worksheet cell. For example, type '=hyperlink('http://www.whitehouse.gov', 'The Oval Office')'.
8. Paste the following program into the window. This program creates a completely new URL in the active cell.Public Sub editURL()ActiveCell.Formula = '=hyperlink(''http://www.nea.gov'', ''National Endowment for the Arts'')'End Sub
9. Click a statement in the program to select the program.
10. Click the 'Run' menu's 'Run' command to execute the program. Return the Excel by pressing 'Alt F11.' The 'White House' URL you typed in Step 2 is now replaced by a link to the home page of the National Endowment for the Arts.

How to Learn Excel Free

1. Enroll in Microsoft's Excel training courses. Microsoft offers free courses for Excel 2010 to Excel 2003. Courses are self-paced, and at the end of the course you will take a quiz to test your knowledge.
2. Subscribe to Excel training podcasts. On Apple's website, you can find many podcasts for Excel training. You will need iTunes installed on your computer before you can view and subscribe to the podcasts.
3. Sign up for HP Learning Center. The company Hewlett-Packard offers free courses on several software programs. They are in partnership with Microsoft and able to offer free courses in all Microsoft Office programs. Their Excel courses range from beginner to advanced.
4. Visit Baycon Group. Baycon Group's website is suited to people who prefer to print out their lessons. They offer four in-depth lessons in Excel 2007 and tutorials on Excel 2003 and Excel 97.
5. Get advanced training. If you're familiar with the basics of Excel, the website Excel Tip will take your knowledge of Excel to the next level. Excel Tip offers a free Excel 2007 course, plus hundreds of Excel tips and techniques.
6. Download Excel templates. These templates will not only show you what Excel can do but also are another free way to learn Excel features.
7. Visit a library. If you prefer books to computer screens and printouts, check your library to see if they have an Excel course book.

How to Use Two Pointers for Excel's Vlookup Function

1. Input the data into the range of cells you want to reference. For the sake of this example, we'll assume the data is in cells D10 through G15. Make sure the data in cells D10 through D15 are your index values (names of clients, for example).
2. Enter the following formula in cell C1: '=VLOOKUP(A1,D10:G15,B1,FALSE)'
3. Input an index value, matching one of the entries in D10 through D15, in cell A1.
4. Input a number between 1 and 5 in cell B1. Cell C1 will use the index value from A1, and the column number from B1, as two pointers to pull the data from the range you specified in step 1.

Monday, July 15, 2013

How to Calculate Days in Excel 2007

1. Open a new blank Excel spreadsheet.
2. Place a date in cell A1. Use the format mm/dd/yyyy and make sure the date is the year 1900 or later. For this example, you could use '07/04/2011.' In an existing spreadsheet, the format of the cell may not be 'Date.' To change the format to 'Date,' click 'Format/Cells...' and select the Number tab and then click 'Date' under 'Category:.'
3. Place a date in cell A2. For this example, you could use '07/24/2010.'
4. Type the following formula in cell A3: '=A1-A2' then click 'Enter.' Cell A3 will contain the number of days between the two dates. In this example, the answer will show 365. If cell A3 shows a date instead, change the cell format to 'Number' and select 'Format/Cells...' Click the Number tab and then select 'Number' under 'Category.'

How to Make Blank Rows Between Populated Rows in Excel

1. Double-click the Excel file into which you want to insert a new row to open the file in Excel 2010.
2. Click the row heading at the left side of the window for the bottom row of the pair that you want to split. For example, if you want to insert a row between rows 1 and 2, click the number '2' at the left side of the window. After clicking the row heading, the entire row should be highlighted in blue.
3. Click the 'Home' tab at the top of the window.
4. Click the 'Insert' drop-down menu in the Cells section of the ribbon at the top of the window.
5. Click 'Insert Sheet Rows.'

How to Create Forms Using Excel

1. Open a workbook in Excel. If you know which workbook you want your form to belong to, then now is the time to open it. Use the File menu to access the workbook.
2. Click on the 'Tools' menu, select 'Macro' and click on 'Visual Basic Editor.'
3. Click 'Insert,' and then select 'UserForm.'
4. Drag a command button onto the form. You need at least three of these for this example.
5. Put names on your command buttons and labels. Click 'View' and 'Properties Window.' Click on one of the command buttons that you placed on the form.
6. Name the command button that will read 'OK' by clicking on the name setting in the Properties window and typing 'cmdOK'. Click on the 'Caption' setting in the Properties window and type 'OK'.
7. Click on another command button. Name this one 'cmdCancel,' and set the caption to read 'Cancel' in the Properties window.
8. Click on the third command button, name it 'cmdClearForm' and change the caption setting to read 'Clear Form.'
9. Hit the 'F7' function key on your keyboard to bring up the code window.
10. Click on the drop-down lists at the top of the code window. Click on the top-left list to open the subprocedure named 'User Form' and click on the other drop-down list to select 'Initialize.'
11. Delete the subprocedure listed above that reads: UserForm_Click() procedure.
12. Type the following code into the code window (do not type over the blue text; just add the black text):
Private Sub UserForm_Initialize()
txtName.Value = ''
txtPhone.Value = ''
With cboDepartment
.AddItem 'Employees'
.AddItem 'Managers'
End WithYourCourse.Value = ''
optIntroduction = True
chkWork = False
chkVacation = False
txtName.SetFocus
End Sub
13. Enter your code into the Cancel button. Double-click the Cancel button to open the code window. Type your code so that the code window reads: Private Sub cmdCancel_Click()
Unload Me
End Sub
14. Open the code window for the Clear Form button. Remember to double-click the 'Clear Form' button.
15. Type your code into the code window so that it reads:
Private Sub cmdClearForm_Click()
Call UserForm_Initialize
End Sub
16. Double-click the 'OK' button to add the following code in the code window:Private Sub cmdOK_Click()
ActiveWorkbook.Sheets('YourWork').Activate
Range('A1').Select
Do
If IsEmpty(ActiveCell) = FalseThen
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value
ActiveCell.Offset(0, 3) = cboCourse.Value
If optIntroduction = True Then
ActiveCell.Offset(0, 4).Value = 'Intro'
ElseIf optIntermediate = True Then
ActiveCell.Offset(0, 4).Value = 'Intermed'
Else
ActiveCell.Offset(0, 4).Value = 'Adv'
End If
If chkLunch = True Then
ActiveCell.Offset(0, 5).Value = 'Yes'
Else
ActiveCell.Offset(0, 5).Value = 'No'
End If
If chkWork = True Then
ActiveCell.Offset(0, 6).Value = 'Yes'
Else
If chkVacation = False Then
ActiveCell.Offset(0, 6).Value = ''
Else
ActiveCell.Offset(0, 6).Value = 'No'
End If
End If
Range('A1').Select
End Sub

How to Use Excel for an Inventory Accounting System

1. Click the 'Microsoft Office' button, and then click 'New.'
2. Click 'Inventories' from the list in the left-hand column.
3. Double-click the inventory sheet you would like to use. For example, you could choose 'Book Inventory' or 'Parts Inventory.' The inventory will download and open automatically in Excel. There are dozens of templates you can choose from, including inventories for home and business. The actual number of templates you can access will depend upon your version of Excel and if you have downloaded any templates from the Internet.
4. Click on any items contained within brackets to customize the spreadsheet. For example, you might click [Company Name] to enter your company name.
5. Follow the instructions on the template to fill in the inventory list. Once you have populated the template with your items, Excel will use built-in template formulas to calculate the number of items in your inventory. For example, a home inventory template may have an auto-summation formula in the 'Total Items' box of your worksheet, which updates automatically with each entry you make into the spreadsheet.

How to View Headers and Footers in Microsoft Excel 2003

1. Scroll to the “View” tab on the command bar; the “View” submenu will open.
2. Under the “View” submenu, select “Header and Footer” to open the Header and Footer Properties box.
3. View the headers and footers by looking at the “Header” preview box at the top and the “Footer” preview box at the bottom. If you find that you wish to make any changes, simply click on the “Custom Header” or “Custom Footer” buttons to amend them.
4. If you have made any changes, click on the 'OK' button to implement them. You can also click on this button to close the box if you have not made any changes.

Sunday, July 14, 2013

How to Change the Margin in a Header in Excel 2007

1. Click the 'Office' button in the upper left-hand corner of the screen.
2. Click 'Print,' then 'Print Preview.'
3. Select 'Show Margins.'
4. Click on the header margin, and drag the mouse to where you want your new margins.
5. Click 'Exit Print Preview.'

How to Filter a List in Microsoft Excel With Limits on the Number of Rows

1. Open the workbook in Excel and click the arrow beside the column header row in the list. Next, proceed to Step 5. Alternately, if you have not yet applied AutoFilter to your list, proceed to Step 2.
2. Select the data you would like to filter. Left-click the mouse and drag it to cover the range of cells.
3. Select 'Data' from the toolbar menu. Next, select 'List' and then click 'Create List.'
4. Check the box 'My list has headers' if applicable. Next, click 'OK.' (Note: If you select this option and your list does not have a header, the first cell in the list will be assigned as the column header cell. If this option is unchecked, the default name 'Column1' will be assigned as the header row.)
5. Right-click the arrow next to the column header row, and then select '(Top 10...).'
6. Click the list box in the middle of the Show region located in the Top 10 AutoFilter dialog box.
7. Type in a number from 1 to 10. Alternately, use the down arrow next to the list box in the middle of the Show region, and scroll to pick your desired number.
8. Select 'Items' in the far right side of the Show region of the Top 10 AutoFilter dialog box, and then click 'OK.' (Note: You can alternatively select 'Percent' instead of 'Items.')
9. Test your AutoFilter list. To do this, click on the arrow next to the column header row, and then click '(Top 10...).'

How to Unshare a Workbook

How to Unshare a Workbook in Microsoft Excel 2003
1. Open the Microsoft Excel 2003 application on your computer and then click the “File” option from the top toolbar menu. Click on the “Open” option.
2. Locate the Excel 2003 file that has the workbook you want to stop sharing. Click on the file and then click the “Open” button.
3. Click the “Tools” option from the top toolbar menu and then click the “Share Workbook” option.
4. Click the “Editing” tab from the “Share Workbook” dialog box. Click on the box next to the “Allow changes by more than one user at the same time” option so that it is deselected.
5. Click the “Yes” button to stop sharing the workbook. Click the “File” option and then click the “Save” option to save all of your changes.
How to Unshare a Workbook in Microsoft Excel 2007
6. Open the Microsoft Excel 2007 application on your computer. Click the “Microsoft Office” button and then click the “Open” option.
7. Locate the Excel 2007 file that contains the workbook you want to stop sharing. Select the file and then click the “Open” button.
8. Click the “Review” tab and then click on the “Share Workbook” option from the “Changes” group.
9. Click the “Editing” tab from the “Share Workbook” dialog box. Click on the box next to the “Allow changes by more than one user at the same time” field so that it is deselected.
10. Click the “OK” button and the workbook will no longer be shared. Click the “Microsoft Office” button and then click the “Save” option to save your changes.

Saturday, July 13, 2013

How to Import a Word 2007 File to Excel 2007

1. Open the Microsoft Excel 2007 spreadsheet file that you want to import a Word 2007 file into.
2. Click on the 'Microsoft Office' button, and then click on the 'Open' option. The 'Open' dialog box will appear on your screen.
3. Select the 'Text Files' option, and then double-click on the Word 2007 file that you want to import. The text import wizard dialog box will appear.
4. Click on the 'Delimited' option if your Word 2007 text includes commas or tabs that separate the fields, or click on the 'Fixed width' option if the fields are aligned in columns.
5. Select the number of the row where you want the text to be placed, and then click on the 'Next' button. Click on the 'Next' button again.
6. Select an option below the 'Column data format' heading, such as the 'General,' 'Text,' 'Date' or 'Do not import column' option. Click on the 'Finish' button.
7. Click on the 'Existing worksheet' option, and click on the 'Collapse' button to select a cell range where you want the text to be placed. Click on the 'OK' button, and your text will be imported.

How to Disable Add

1. Open Microsoft Excel 2003.
2. Click 'Tools' on the top navigation bar and then click the 'Add-ins' option.
3. Un-check the check box next to the add-in you want to disable.
4. Click the 'OK' button.
5. Close and restart Excel. The add-in is disabled.

How to Do Macros in Excel 2003

1. Open up the Excel 2003 file in which you want to create a macro. Select the 'Tools' menu at the top of the screen, then move your mouse to the 'Macro' option and select 'Security' from the pop-up menu. Select either medium or low in order for macros to operate correctly in the worksheet. Low will always prompt you if you want to run the macro, while medium will not. Click 'OK' to continue.
2. Click on the cell where you want the macro to start. You will be given the option later to use relative references, and if you are then you will need to have selected the starting macro location beforehand.
3. Click on the 'Tools' menu again and move your mouse over 'Macro.' Choose 'Record New Macro' from the list of options. A small record macro window will appear.
4. Enter in the name of your macro into the 'Name' field. The name must begin with a letter and cannot be a cell reference, like 'A1' or 'Z12.' Type in a shortcut key in the required field so that Excel will run the macro when you press the key. This key must be a letter, and is case-sensitive. If you want this macro to be available on every workbook, change the 'Store macro in' box to read 'Personal Macro Workbook.' Finally, type in a description of the macro, so that you will remember what it does. Click 'OK' when you are done.
5. Decide whether you will be using relative references in your macro. When you use relative references, any movement around the spreadsheet will be relative to whatever cell is selected when you run the macro. For example, if you are starting at cell A1 and you select cell A2 in the macro, if you are using relative references, Excel will simply select the cell below the selected cell when someone runs the macro. Without relative references, the macro will always select cell A2 regardless of what cell the user has selected. Relative references are set 'off' be default, so if you want to turn them on, click on the small button to the right of the square 'Stop' button in the macro window. Relative references will stay on until you click the button again to turn them off.
6. Perform the macro actions on your spreadsheet. The macro will record every move that you make. Click the square 'Stop' button when you are done making the macro.
7. Hold 'Ctrl' and press the letter key you assigned to the macro when you want to run it.

How to Use Descriptive Statistics in Excel

1. Type your data into the spreadsheet. For example, you might type a list of heights into column A or a list of egg prices in column F.
2. Click on a blank cell in a spreadsheet.
3. Click on the 'Formulas' tab on the Excel toolbar, then click on 'More Functions' in the Function Library. Click on 'Statistical' and then mouse over the function for a description, and if it is the function you need, click on it to open a dialog box for the function. For example, 'Median' returns the median, or the middle number in the data set.
4. Type the location of your data into the text box. For example, if your data is in cells A1 to A3, type 'A1:A3' into the 'Number 1' text box.
5. Click on the 'OK' button. Excel will perform the calculation and return the answer in your chosen cell.

How to Graph Two Datas Scatter Plot in Excel

1. Open the Excel spreadsheet containing the two columns of data you want to turn into a scatter plot. Scroll down to the bottom of the column and click the lowest cell containing the data. Drag up and across until both columns are highlighted.
2. Navigate to the 'Insert' tab at the top of the spreadsheet, which is positioned to the left of the 'Home' tab. Click the 'Scatter' button in the 'Charts' heading near the top of the screen.
3. Click the icon in the drop-down menu for the specific type of scatter plot you want to use, such as 'Scatter With Only Markers,' 'Scatter With Smooth Lines and Markers' or 'Scatter With Straight Lines.'
4. Click the scatter plot and drag it to any location on the spreadsheet. Click either of the plot's four corners and drag in any direction to resize the plot, making it larger or smaller to fit into any collection of cells.

How to Use Excel to Calculate Beta Coefficient

1. Create a new spreadsheet in Microsoft Excel. Type the historical data for the stock in question and the benchmark in two separate columns.
2. Find the percent change of the data for the benchmark and the stock using the following formula:=((Cell2-Cell1)/Cell1)*100Cell 1 indicates the previous period data point, and cell 2 indicates a current period data point. Drag the formula with the mouse down the two columns.For example, if the stock's daily stock price was $100 per share in June 2010 and is $125 in June 2011, the $125 is cell 2 and the $100 is cell 1. Subtract 100 from 125 to get 25. Divide 25 by 100, which equals .25. Multiple this by cell 1 or 100. The percent change for this particular stock is 25 percent.Complete this formula for the SP 500 as well, which is the benchmark. When finished, you will have a percent of change for your stock and the SP 500.
3. Figure out the beta coefficient by using the 'SLOPE' function in Excel. The slope function is '=SLOPE(range of % change of equity, range of % change of index).' For example, if there were daily changes to Apple's stock price in cells A1:A260, and the daily changes to the SP 500 were in column B1:260, the function would be '=SLOPE(A1:A260,B1:B260).'

Friday, July 12, 2013

How to Add an Axis Title to an Excel Chart

1. Start Microsoft Excel 2007 and open a spreadsheet from your files that contains a chart to which you would like to add an axis title.
2. Select the entire Excel chart by clicking in the white area of the chart. A light blue line will surround the entire chart indicating it has been selected.
3. Choose the 'Layout' tab at the top of the Excel screen and locate the 'Labels' group within the 'Layout' ribbon.
4. Click the 'Axis Titles' button in the 'Labels' section of the 'Layout' ribbon to display a drop-down list.
5. Point to 'Primary Horizontal Axis Title' to create a title for your 'X'-axis or point to 'Primary Vertical Axis Title' to create a title for your 'Y'-axis.
6. Click to choose where you want the selected title to be displayed in relation to the rest of the chart. The selections you see will depend on how your chart is formatted. A default title for the chart will appear the in the location you have chosen.
7. Add your own title by clicking on the default title and typing your new title with your keyboard. Press the 'Enter' key when you are finished typing the title.

How to Open .Xlsx Files in .Xls

1. Download the Microsoft Office Compatibility Pack to your computer.
2. Launch the older version of Excel.
3. Click the 'File' menu and select 'Open.' Use the 'Look in' menu to find the folder in which the XLSX file is saved. Highlight the XLSX file name and click 'Open.'

How to Create a Stacked Column Chart With the Data Plotted in Rows Using Excel

1. Open the Excel 2010 file that hold the data that you want to use in a chart.
2. Check the data to ensure that you have headers in the first column. If you don't, right-click on the letter above the first column and choose 'Insert.' Type in a header for each row into the column you just created.
3. Click on any cell that is part of your data field. Excel will automatically expand the selection to all adjacent cells when you create the chart. If you need to chart a specific area of your data, click on the top left cell of the area you want to chart, then hold 'Shift' and click on the bottom right cell.
4. Select the 'Insert' tab at the top of the screen. Locate the 'Charts' area, and click on the 'Column' button to bring up a pop-up menu that includes all of the column chart options. Select the 'Stacked Column' option to create your chart.
5. Right-click anywhere on the chart, and choose 'Select Data.' A small window appears on the screen. Click the 'Switch Row/Column' button in the middle of the screen, and then click 'OK.' Your graph changes to show each row of data as a stacked column.

Thursday, July 11, 2013

How to Put a Command Button on a Spreadsheet in Excel

1. Open the spreadsheet in Excel that you want to add a command button to. Click the 'Office File' button on the main ribbon and click 'Excel Options.' Enable 'Show Developer tab in the ribbon' if it isn't already checked and click 'OK.'
2. Click the 'Developer' tab, then click 'Insert.' This brings up the list of controls that you can insert. Click the 'Button' link from the list of options.
3. Click in the spreadsheet where you want the command button to be placed.
4. Choose a macro from the list to assign to your command button, then click 'OK.'
5. Right-click the button and click 'Format Control' to format how the button looks.

How to Make Ledgers on Excel

1. Open a new Microsoft Excel spreadsheet. Type 'Opening Balance' into Cell A1. Type the opening balance into cell D1.
2. Type the name of the first credit or deduction into cell A2. Type the amount -- positive for additions, negative for deductions -- into cell C2. Type the formula =(A1 C2) into cell D2.
3. Input the names of amounts additional additions and credits as you become aware of them. Each time click cell D2 and drag the black border around it down into the 'D' column of the row where you notate the new transaction to display the new running balance in the 'D' cell of that row.

How to Password Protect an Excel 2007 File

1. Open the Excel 2007 file. Click the 'Office Button' located in the top-left corner.
2. Select 'Prepare' from the list. Click the option to 'Encrypt Document.' Enter a password. Click 'OK.' Re-enter the password. Click 'OK.'
3. Select 'Save As' from the list. Click 'Tools' located in the lower-left corner of the 'Save As' box. Select 'General Options' from the list. Set a password to open or modify the file. Click 'OK.' Re-enter the password. Click 'OK.'
4. Click the Microsoft Office Button and select 'Save.'

How to Insert a Tick Mark in Excel

1. Reveal the Developer tab, if necessary. In Excel 2007, click the 'Office' button and choose 'Excel Options.' In the Popular options set, check the box labeled 'Show Developer tab in the Ribbon.' In Excel 2010, click 'File' then 'Options.' Choose 'Customize Ribbon' in the Categories pane and check 'Developer' in the list of tabs.
2. Click the 'Developer' tab. Choose 'Insert' and from the Form Controls group choose the small box with a check mark in it.
3. Draw a box with the cursor to define the location and size of tick mark and its label. These remain changeable.
4. Click on the text, marked 'Check Box 1,' delete that text and add your own.
5. Right-click anywhere in the form and choose 'Format Control...' to set the form's formatting properties, such as color and lines. Choose the 'Control' tab to determine whether the box begins with a tick or without one. Add 3-D shading if you like.

How to Copy VLookup to Multiple Rows

1. Open the Excel 2010 file where you want to copy the VLookup function down to multiple rows.
2. Click on the cell that contains your VLookup formula. Place your cursor into the formula bar located just above the spreadsheet so that you can edit the formula.
3. Place dollar sign symbols in front of any ranges that you don't want to change when you copy your formula down. Excel uses relative references when copying formulas, so if your VLookup function referenced the range A1:D10 and you copy that formula down one cell, the range would change to A2:D11. By placing dollar signs in front of each reference element, such as $A$1:$D$10, you ensure that the reference will not change when you copy the formula. This is especially important when copying VLookup formulas, as the table range should remain constant. Press 'Enter' when you are done making changes.
4. Click the cell that contains the VLookup formula. Move your mouse to the small, black box, called a fill handle, in the lower right corner of the cell. Your mouse cursor will change into a plus sign when you are correctly positioned over the box.
5. Click and hold the mouse button on the fill handle. Drag the mouse down the spreadsheet until you reach the last row where you want the VLookup formula to reside. Release the mouse button, and the formula will be instantly copied to all the rows between the original cell and the cell where you released the button.

How to Enable Scrolling in Excel 2007

1. Open the Microsoft Excel 2007 file on which you want to enable scrolling.
2. Click on the 'Microsoft Office' button from the top of the page and click on the 'Excel Options' button.
3. Click on the 'Advanced' button from the left side of the Excel Options dialog box.
4. Click on the box next to the 'Zoom on Roll with Intellimouse' option so it's deselected.
5. Click on the 'OK' button, and you should now be able to scroll throughout your worksheet.

How to Change the Case in Microsoft Excel 2007

1. Open the Excel worksheet that has the case you want to change.
2. Decide which case you want to use. You can choose from uppercase, lowercase or proper case (the first letter is capitalized).
3. Use a blank cell, row or column next to the cell you want to change. If there is not a blank cell, row, or column, right-click next to the cell you want to change, select 'Insert' and choose whether you want to insert a new row or column. You can delete this later. If you need to change the entire spreadsheet, start with the column available at the end of the spreadsheet.
4. Enter '=' and the function for the case you want to change to in the blank cell. Use 'LOWER,' 'UPPER,' or 'PROPER.' For example, if you wanted to switch to lowercase, you would enter into the cell '=LOWER.'
5. Next to the case function enter a parenthesis. Inside the parenthesis, enter the cell reference for the first cell where you want to change the case. For example, if you are changing the case of the text in cell A1 to lowercase, you would have entered '=LOWER(A1)' at this point.
6. Highlight the cell you entered the function in and the rest of the row(s) or column(s).
7. Click the 'Home' tab located at the top of the screen and click the 'Fill' button. Select 'Down,' 'Right,' 'Up,' or 'Left' depending on the direction of the cells in which you're changing the case. If you need to change cells in more than one direction, then click 'Fill' again and select the next direction. The text with the case change will appear.
8. Highlight the cells with the case change, right-click, and select 'Copy.'
9. Click the first cell with the old case, which would be cell A1 in the example. Click the 'Paste' button and select 'Paste Special.' Select 'Values' in the box that opens and click 'OK' to paste the converted text. This will paste the new case into all the cells.
10. Delete the duplicate cells by highlighting them and pressing the 'Delete' button. If you had to insert a new row or column, select the row or column, right-click, and click 'Delete.'

Subscribe to: Posts (Atom)