Excel

Browse » Home » All posts

Thursday, June 21, 2012

How to Disable a Password in Excel 2007

1. Open the Microsoft Excel 2007 file on your computer that contains the worksheet or workbook that is password protected.
2. Click on the 'Review' tab, and then click on the 'Unprotect Worksheet' option from the 'Changes' group.
3. Enter your password if the 'Protect Workbook' dialog box appears.
4. Click on the 'OK' button, and then the 'Unprotect Worksheet' option will read as 'Protect Worksheet.'
5. Click on the 'Microsoft Office' button, and then click on the 'Save' button to save your changes to unprotect the worksheet.

How to Create Organizational Charts With Excel 2007

1. Open Excel to a blank spreadsheet.
2. Click the 'Insert' tab, then click the 'SmartArt' button.
3. Select the 'Hierarchy' group from the left pane. Choose an organizational chart layout you like. Click on each one to see a preview and a description of it in the right pane. Click 'OK' after you have selected the one you want. You should see a chart with text boxes and lines appear.
4. Select a shape in the chart, then click the down arrow next to the 'Add Shape' button in the Design tab to add more boxes to your organizational chart. You can select whether you want to add a shape before, after or next to the box you originally selected. Keep adding shapes to your organizational chart as needed; Excel will automatically add lines showing hierarchies.
5. Click inside each text box to edit the names of each person. If you selected an organizational chart with images, you can click on each image to add an individual's picture.
6. Select different colors or styles for your chart by clicking a different 'SmartArt Styles' design or by clicking 'Change Colors.' Move or resize each text box by clicking and dragging the box or the handles surrounding the box.

How to Calculate a Future Date in Excel

1. Open a new or existing Excel workbook and select a new worksheet.
2. Use row 1 for column labels. Type 'Start Date' into cell A1--column A, row 1. Type 'Plus 30 Days' into cell B1, 'Plus 60 Days' into cell C1 and 'Plus 90 Days' into cell D1. To display these column labels correctly, you need to widen columns A, B, C and D. To widen a column, select its right border and then drag it to the right.
3. Select--highlight--cells A2 through D3. Under the 'Home' tab, select the 'Cells' grouping, click 'Format' and then click 'Format Cells' to display the 'Format Cells' window. Click 'Date,' select 'March 14, 2001' and then click 'OK' to format the cells and close the window.
4. In cell A2, type '1/27/15' to display 'January 27, 2015.' Widen column A if you see only pound signs--#######.
5. Type formula '=A2 30' into cell B2 and then press 'Enter' to display 'February 26, 2015,' which is the date in cell A2 plus 30 days. Copy cell B2 and paste it into cells C2 and D2 to display 'March 28, 2015' and 'April 27, 2015.'
6. Type formula '=A2 7' into cell A3 and then press 'Enter' to display 'February 3, 2015.' Copy cells B2 through D2 and paste them into cells B3 through D3 to display 'March 5, 2015,' 'April 4, 2015' and 'May 4, 2015.'
7. Copy row 3 and paste it into row 4 to display 'February 10, 2015,' 'March 12, 2015,' 'April 11, 2015' and 'May 11, 2015.'

How to Use Conditional Statements in Excel

The Three Parameters of IF() Statements
1. The first parameter in an IF() statement is an equation or logical operator. For example, A1>A2.
2. The second parameter of an IF() statement is separated from the first by a comma, and is what is displayed if the IF statement is true. For example, 'Passing Grade'.
3. The third parameter of an IF() statement is separated from the second by a comma, and is what is displayed if the IF statement is false. For example, 'Failing Grade.'
4. Finally, here's a completed IF() statement: =IF(A1>A2,'Passing Grade','Failing Grade'). This statement compares the value of a number in cell A1 to a value in cell A2, and if it's larger, displays 'Passing Grade', and if equal to or less than, displays 'Failing Grade'.

Tuesday, June 19, 2012

How to Download Microsoft Excel 2003

Buy an Older Version
1. Make sure that your computer is capable of running Excel 2003. The requirements are Windows XP or later, 128 MB of RAM and 400 MB of hard-disk space.
2. Log on to an online software store such as Brothersoft.com, Amazon.com or Excellent-software.net.
3. Type 'Excel 2003' in the search bar on the website.
4. Scroll through the results and select the program you wish to purchase.
5. Select the method of payment you wish to use. Most websites accept PayPal or credit card payments. Enter your payment details and then confirm the transaction.
6. Follow the on-screen instructions on how to begin your download.
Use a Free Trial
7. Log on to Brothersoft.com and select the download link.
8. Select the location for the file and then select 'OK.'
9. The file will download to the location you specified. Open the file by double-clicking it, to run the installation sequence.

How to Prevent a Microsoft Excel 2007 File from Opening

1. Open your spreadsheet in Microsoft Excel 2007.
2. Click the top-left 'Office' button, and select 'Save As.'
3. Click the 'Tools' drop-down menu, located to the left of the 'Save' button, and select 'General Options.'
4. Enter a password in the 'Password to open' field, and click 'OK.'
5. Re-type the password in the confirmation window, and click 'OK.'
6. Click 'Save' to secure your spreadsheet.
7. Click 'Yes' in the 'Confirm Save As' dialog windows, which asks if you want to replace the current file. This overwrites the unprotected file with the password-protected version.

How to Copy and Paste for Conditional Formatting in Excel

Copy Conditional Formatting
1. Select the Excel cell that contains the conditional formatting rules you want to copy. Hold the 'Ctrl' button and press the letter 'C' to copy the cell to the clip board.
2. Right click on the cell you want to copy the conditional formatting to. If you are copying it to a range of cells, left-click on the first cell and hold the mouse button down. Then move the mouse to the last cell and release the button. Finally, right-click on the selected cells.
3. Move your mouse over 'Paste Special' in the pop-up menu to reveal additional pasting options. Select the icon that has a percentage symbol and a paint brush. The icon is located under the 'Other Paste Options' area of the menu and reads 'Formatting (R)' when you move your mouse over it. Once you select this icon, the conditional formatting will now affect the new cells.
Combine Multiple Groups of Conditional Formatting
4. Select the top-left cell in the first group and hold the mouse button down. Move the mouse to the bottom-right cell in the second and release the mouse button, selecting the entire area. If the cells you need to select are not right next to each other, you can hold down the 'Ctrl' button to select additional cells and areas.
5. Select the 'Home' tab at the top of the screen and then press the 'Conditional Formatting' button in the 'Styles' area. Choose 'Manage Rules' from the menu that appears. You will see a screen with several of the same conditional formatting rules listed.
6. Select one of the duplicate rules and press the 'Delete Rule' button. Continue until you only have one conditional formatting rule remaining. Select the small box next to the 'Applies to' field, which will move your cursor back to the Excel worksheet.
7. Select the exact same area of the worksheet as you did before you brought up the conditional formatting rules manager. Press the small box again once you have completed your selection. Click 'Apply' at the bottom of the rules manager followed by 'OK.' All the selected cells are now a part of the same conditional formatting group.

How to Show Grid Lines When Printing in Microsoft Excel

Printing
1. Open the worksheet or worksheets in Excel that you wish to print. You can do this by clicking on theworksheet tab at the bottom of the screen. To select more than one worksheet, after you select the first worksheet, press Shift and then select the second or multiple worksheets.
2. Go to the 'Page Layout' tab on your menu bar for Microsoft Excel 97-2003 or interface for Microsoft Excel 2007.
3. Select the 'Sheet Options' group and click on the 'Print' check box under 'Gridlines.'
4. Preview the grid lines to view how they will print by pressing the 'Ctrl F2' keys to open the Print Preview window. Remember that grid lines are only designed to print around inputted data in a worksheet. To print grid lines around empty cells you must change the print area.
5. Select the empty cells on the worksheet that you want to include with grid lines as well. Go to the 'Page Layout' tab. In the 'Page Setup' group, select 'Print Area' and then click 'Add to Print Area.'
6. Go to the Microsoft Excel button for the 2007 version and click 'Print.' For Excel 97-2003, go to the menu and select 'Print.'
Troubleshooting
7. Go to 'Page Setup' and 'Sheet' tab. Make sure that the 'Draft' quality box is not checked.
8. Download the latest driver for your printer from the manufacturer's website if the grid lines still are not printing. Sometimes a problem with the printer driver interferes with printing instructions.
9. Change and apply borders to the cells if you are having trouble with grid lines.

How to Create a Form in Excel 2007

1. Open the Microsoft Excel 2007 application. Click the “Microsoft Office” button and select “Open.”
2. Locate the Excel 2007 file stored on your computer for which you want to create a form. Select the file and click on the “Open” button.
3. Add any column headers to the columns within the spreadsheet. Click on any cell in the data table where you want to add the form.
4. Click on the arrow next to the Quick Access Toolbar and click on the “More Commands” option. Click on the “All Commands” option and select the “Form” button from the list.
5. Click on the “Form” button from the Quick Access Toolbar. The data form will appear as a dialog box within the spreadsheet.
6. Click on the 'New' button within the data form dialog box if you want to enter data for a new row. Enter the data into the new field that appears in the dialog box and the press 'Enter.'
7. Click on the 'Find Next' or 'Find Prev' buttons to navigate to each row in the data form dialog box. Click on the 'Criteria' button to enter any data you want to search for within the form.
8. Change data in a row in the data form dialog box by clicking on any row and entering the new data. Press 'Enter' and the row will be updated on the form and in your spreadsheet.

Monday, June 18, 2012

How to Find Out How Often a Number in a Set Appears in Excel

1. Highlight your data set. For example, if your set of numbers is in cells A1 to A50, highlight cell A1 with your mouse.
2. Choose 'Data' from the ribbon. Click on 'Advanced' to bring up a pop-up box.
3. Check the 'Copy to another location' radio button, and check the 'Unique records only' box. Enter the range of your data set. In this example, cells A1 to A50 would be entered as $A$1:$A$50. In the 'copy to' box, enter where you would like the data to be output. For example, enter $B$1:$B$50 (which will return the result in cells B1 to B50). Press 'OK.' This returns a list of numbers in your data set.
4. Enter the following formula into cell C1: =COUNTIF(A$1:A$50,B1). This counts the number of times the item in cell B1 appears in the data set from cell A1 to cell A50, and returns the result in cell C1.
5. Copy the formula in the cell from Step 4 (in this example, cell C1) for all cells in column B. For example, if you have 10 cells filled in column B, select cell C1 by clicking with the mouse, and drag the fill tool (the little black square in the bottom right corner) down to cell C10.

How to Use Hyperlinks in Dropdown Menus in Excel

Adding Links to Drop-down Menus
1. Access editing capabilities by double-clicking the cell in the menu that contains the URL that you would like to hyperlink.
2. Activate the URL by pressing 'Enter.' This will convert the text URL to an active hyperlink.
3. Click that now-active hyperlink one more time to complete the process.
4. Repeat Steps 1 through 3 for each menu item.
Alternate Method
5. Choose a menu cell, for example, cell B6.
6. Insert the following function into cell C6 (the cell below the chosen menu cell):=HYPERLINK(B6, 'Goto Link')
7. Change 'GoTo Link' to your choice of text.

How to Use Excel to Make a Percentage Bar Graph

1. Open Excel to a blank spreadsheet.
2. Enter your numbers in cells in table form and add labels for each column and row. For example, if you want to compare men to women and the percent who smoke vs. the percent who do not smoke, you would create a 2x2 table. You would label one column 'Men' and one column 'Women.' To the left, you would label the first row 'Smoke' and the second row 'Don't smoke.' When entering numbers in each cell, add the percent sign to let Excel know that these are percentages. For example, you should enter '32%' instead of '32.'
3. Select the table you created by clicking and dragging to highlight the cells.
4. Click the 'Insert' tab on the ribbon.
5. Click 'Bar' in the Charts group. Select a bar style that suits your needs. Excel will automatically create a bar graph with percentages for either your X or Y axis.
6. Click inside the chart to customize colors, sizes, fonts and background. Click the 'Design,' 'Layout' or 'Format' tabs in the Chart Tools section of the ribbon to select custom features.

How to Add a Watermark to an Excel Spreadsheet

Insert Your Watermark
1. Place your cursor where you would like the watermark to appear. Choose to put this in a header or footer or in the middle of a page.
2. Choose 'Picture' under the 'Insert' menu.
3. Browse for and select your image. Hit 'OK.' Your image should now be on the spreadsheet.
Adjust the Coloring to Make the Image Look Like a Watermark
4. Click on the photo. This will make the picture toolbar appear.
5. Click 'Format Picture' on the toolbar.
6. Under 'Image Control/Color,' select 'Watermark.' This will lighten the colors in your image to look like a watermark.

How Can I Make a Dynamic Drop

1. Launch Excel 2003 and click the Tools menu. Select Macro and then Visual Basic Editor. Insert a new module by clicking the Insert menu and selecting Module. Type the following to create a new procedure:Private Sub createDropDownList()On Error GoTo Err_createDropDownList:
2. Type the following to dynamically create a new ComboBox control in the active Excel worksheet:With ActiveSheet.OLEObjects.Add(ClassType:='Forms.ComboBox.1', Link:=False, _DisplayAsIcon:=False, Left:=70, Top:=60, _Width:=100, Height:=25)With .Object.AddItem 'Item List 1'.AddItem 'Item List 2'.AddItem 'Item List 3'End WithEnd WithThis code will also add three items to the ComboBox control.
3. Type the following to exit the procedure and handle errors:Exit_createDropDownList:Exit SubErr_createDropDownList:MsgBox Err.DescriptionResume Exit_createDropDownList:End Sub
4. Press 'F5' to run your procedure. You will see a new ComboBox control created in your worksheet.

How to Copy Paste a Range to a VBA Excel Workbook

1. Create two different workbooks in Excel. The first workbook will contain the range that you want to copy while the other one is where you will paste the range into. Keep the workbook that currently has the range open. The other workbook doesn't have to be open to complete the rest of the steps.
2. Open the Visual Basic Editor. Click on 'Tools > Macros > Visual Basic Editor' in Excel 2003 or earlier. In Excel 2007, you will need to show the 'Developer' tab first. Click on the 'Office' button and then 'Excel Option.' Click the checkbox next to 'Show Developer tab on the ribbon' under the 'General' tab and then click 'OK.' Click on the 'Developer' tab and then click 'Visual Basic' to open the editor.
3. Click on 'This Workbook' in the left-hand pane to open it in the code window. Add the following code to the code window:Public Sub CopyRange()Workbooks('Book1')._
Worksheet('CurrentSheet') _
Range ('A1:C10').Copy_
Destination:=
Workbooks('Book2')._
Worksheets('PasteSheet')._
Range('A1')
End SubChange the information in the parenthesis to match your workbooks. 'Book 1' is the workbook that you are currently working in. 'CurrentSheet' is the worksheet that has the range you want to copy. 'A1:C10' is the range of cells that you want to copy. 'Book2' is the workbook that you want to paste the range. 'PasteSheet' is the worksheet that you want to paste the range into. 'A1' is the beginning cell that you want to paste the range into.
4. Save and run the code. Click on 'Tools > Macro > Macros' in Excel 97 to 2003. In Excel 2007, click on the 'Macros' button on the 'Developer' tab. See the 'CopyRange' function in the list of macros. Click on it and then click 'Run.' The code will run and copy the range from one worksheet and paste it into another.

Subscribe to: Posts (Atom)