Excel

Browse » Home » All posts

Thursday, July 25, 2013

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.

Subscribe to: Posts (Atom)