Microsoft Excel 2007 - Printing a Worksheet, Customize the Layout, Advance Features in excel 2007, Importing External Data, Keyboard Shortcuts for Excel-2007
| Printing a Worksheet |
| Print Preview |
| 1. Click the Office button. A menu appears. 2. Highlight Print. The Preview and Print The Document pane appears. 3. Click Print Preview. The Print Preview window appears, with your document in the center. |
 |
| Center Your Document |
 |
1. Click the Page Setup button in the Print group. |
| Print Properties |
| There are many print options. You set print options on the Page Layout tab. Among other things, you can set your margins, set your page orientation, and select your paper size. |
| Margins define the amount of white space that appears on the top, bottom, left, and right edges of your document. The Margin option on the Page Layout tab provides several standard margin sizes from which you can choose. |
| There are two page orientations: portrait and landscape. Paper, such as paper sized 8 1/2 by 11, is longer on one edge than it is on the other. If you print in Portrait, the shortest edge of the paper becomes the top of the page. Portrait is the default option. If you print in Landscape, the longest edge of the paper becomes the top of the page. |
 |
| Paper comes in a variety of sizes. Most business correspondence uses 8 1/2 by 11 paper, which is the default page size in Excel. If you are not using 8 1/2 by 11 paper, you can use the Size option on the Page Layout tab to change the Size setting. |
| Set the Page Layout |
 |
1. Choose the Page Layout tab. 2. Click Margins in the Page Setup group. A menu appears. 3. Click Wide. Word sets your margins to the Wide settings. |
| Set the Paper Size |
 |
1. Choose the Page Layout tab. 2. Click Size in the Page Setup group. A menu appears. 3. Click the paper size you are using. Excel sets your page size. |
| Print Worksheet |
 |
1. Click the Print button. The Print dialog box appears. 2. Click the down arrow next to the name field and |
| Customize the Layout |
| Split a Worksheet |
| You can split a worksheet into multiple resizable panes for easier viewing of parts of a worksheet. To split a worksheet: |
. Select any cell in center of the worksheet you want to split . Click the Split button on the View tab . Notice the split in the screen, you can manipulate each part separately |
 |
| Freeze & Unfreeze Rows and Columns |
| You can select a particular portion of a worksheet to stay static while you work on other parts of the sheet. This is accomplished through the Freeze Rows and Columns Function. To Freeze a row or column: |
. Click the Freeze Panes button on the View tab . Either select a section to be frozen or |
 |
| Hide & Unhide Worksheets |
| To hide a worksheet: |
. Select the tab of the sheet you wish to hide . Right-click on the tab . Click Hide |
 |
| To unhide a worksheet: |
. Right-click on any worksheet tab . Click Unhide . Choose the worksheet to unhide |
 |
| Advance Features in excel 2007 |
| Drop Down Lists |
| If you have to type the same data into cells all the time, then adding a drop down list to your spreadsheet could be the answer. In Excel 2007, this comes under the heading of Data Validation. |
| In the example below, we have a class of students on a drop down list. We only have to click a cell in the A column to see this same list of students. You'll see how to do that now. Here's a picture of your finished spreadsheet: |
 |
| In the image above, we can simply select a student from the drop down list - no more typing! We can also do the same for the Subject and Grade. |
| So, create the following headings in a new spreadsheet: |
| Cell A1 Student Cell B1 Subject Cell C1 Grade Cell E1 Comments |
| We now need some data to go in our lists. So, type the same data as in the image below. It doesn't need to go in the same columns as ours. But don't type in Columns A, B, C or E: |
 |
| The data in Columns F, G and H above will be going in to our list. |
| Now click on Column A to highlight that entire column: |
 |
| With Column A highlighted, click on Data from the Excel 2007 menu bar. From the Data menu, locate the Data Tools panel. On the Data Tools panel, click on the Data Validation item. Select Data Validation from the menu: |
 |
| When you click Data Validation, you'll see the following dialogue box appear: |
 |
| To create a drop down list, click the down arrow just to the right of "Allow: Any Value" on the Settings tab: |
 |
| Select List from the drop down menu, and you'll see a new area appear: |
 |
| Source means which data you want to go in your list. You can either just type in your cell references here, or let Excel do it for you. |
| To let Excel handle the job, click the icon to the right of the Source textbox: |
 |
| When you click this icon, the Data Validation dialogue box will shrink: |
 |
| Now select the cells on your spreadsheet that you want in your list. For us, this is the Students: |
 |
| Once you have selected your data, click the same icon on the Data Validation dialogue box. You'll then be returned to the full size one, with your cell references filled in for you: |
 |
| Click OK, and you'll see the A column with a drop down list in cell A1: |
 |
| However, you don't want a drop down list for your A1 column heading. To get rid of it, click inside of cell A1. Click the Data Validation item on the Data Tools panel again to bring up the dialogue box. From the Allow list, select Any Value: |
 |
| Click OK on the Data Validation dialogue box, and your drop down list in cell A1 will be gone. |
| The rest of the column will still have drop down lists, though. Try it out. Click inside cell A2, and you'll see a down-pointing arrow: |
 |
| Click the arrow to see your list: |
 |
| Select an item on your list to enter that name in the cell. Click any other cell in the A column and you'll see the same list. Adding a drop down list to your cell can save you a lot of time. And it means that typing errors won't creep in to your work. |
| Add an error message to an Excel 2007 |
| You can also restrict what goes in to a cell on your spreadsheet, and display an error message for your users. We'll do this with our Comments column. If users enter too much text, we'll let them know by displaying a suitable error box. Try the following: |
. Highlight the E column on your spreadsheet (the Comments column) . From the Data Tools panel, |
 |
| When you select Text Length from the list, you'll see three new areas appear: |
 |
| What we're trying to do is to restrict the amount of text a user can input into any one cell on the Comments column. We'll restrict the text to between 0 and 25 characters. |
| The first of the new areas (Data) is exactly what we want - Between. For the minimum textbox, just type a 0 (zero) in there. For the maximum box, type 25. Your dialogue box should then look like this: |
 |
| To add an error message, click the Error Alert tab at the top of the Data Validation dialogue box: |
 |
| Make sure there is a tick in the box for "Show error alert after invalid data is entered". |
| You have three different Styles to choose from for your error message. Click the drop down list to see them: |
 |
| In the Title textbox, type some text for the title of your error message. |
 |
| Now click inside the error message field and type some text for the main body of your error message. This will tell the user what he or she did wrong: |
 |
| Click OK on the Data Validation dialogue box when you're done. |
| To test out your new error message, click inside any cell in your Comments Column. Type a message longer than 25 characters. Press the enter key on your keyboard and you should see your error message appear: |
 |
| As you can see, the user is prompted to Retry or Cancel. But our title (Too many characters) is at the top, our Stop symbol is to the left, and our Error message is displaying nicely! |
| Data Validation |
| To set Data Validation in Excel do the following steps. |
. Create a result card for a student. . select all cells in which validation rule is to be set . . click on Data menu. . click on Validation. |
| In excel 2007 click on Data menu and click on Data Validation in Data Tools group. |
| A data validation Dialog box will appear as shown in figure below. Figure also shows the result card of student. |
 |
| Now we have to set a validation rule that no student can get more than 100 marks. |
| So in the settings tab of Data validation Dialog box, |
| choose whole number in allow field and 0 to 100 range in Minimum and Maximum fields as shown in figure above. |
| If you want to show a message to user to help in data entry, you can do this in Input Message tab. This message appears when focus is on the cell. |
| In Error Alert tab, enter error message. When user enters value in the cell, excel validates this value against validation rule and displays error message if value does not stratify the rule. There are three Style available in Error Alert tab, Stop, Warning, Information. Stop do not allow user to navigate away from cell without entering correct value. Warning and Information just intimate user and focus is shifted to next cell. |
| To test this enter a 150 in cell c7 and press enter, an error message appears and will keep on displaying until you provide correct value or cancel as shown in figure below. |
| Note that the value in the cell C6 is violating validation rule. This is because validation rule was set after data entry in C6. Excels allows this but provides a way solve this problem. |
| Importing External Data |
| From Access |
| If you are new to Access or Excel, you may not know that you can import data from an Access database into Excel. The steps below describe a quick and easy way to accomplish this. |
| 1. Open the appropriate Excel workbook. 2. From the Data menu, point to Import External Data, and then click the Import Data option. |
|
| 3. From the Select Data Source dialog box, click the New Source button. 4. From the Data Connection Wizard dialog box, click ODBC DSN, and click Next. 5. Click MS Access Database, and click Next. 6. From the Select Database dialog box, locate the database file you want to import, and click OK. 7. Click the name of the table containing the data you want to import, and click Next. 8. Type a name and description, and click Finish. 9. In the Select Data Source dialog box, click the data source you just created and click Open. 10. In the Import Data dialog box, specify where you want to put the data, and then click OK. |
| Web Integration |
| The next web query we'll do will see an import of full HTML formatting. When you're finished, you'll see why this can be a problem. |
| . At the bottom of Excel 2007, click on Sheet1 . On the fresh worksheet, click inside cell A1 . Click on the Data menu, then on click From Web on the Get External Data panel . In the New Web Query Address box, type the following Address (don't click the Go button just yet): http://mkdtutorials.com/ |
| Click the Options button in the top right of the dialogue box: |
 |
| This time, select Full HTML Formatting, as in the image above. Click OK, then click the Go button. |
| Excel 2007 will bring back your data. Click the yellow box with the arrow in it to select all the data: |
 |
| Click the Import button at the bottom when your dialogue box looks like the one above. |
| When you see the Import Data dialogue box, just click OK. The data will then be imported into Excel: |
 |
| The problem with importing full HTML is that some of that fancy formatting you did won't convert very well in Excel. In the image above, our Latest Prices heading has been mangled! |
| In other words, you may have to spend time re-formatting your spreadsheet. |
| To get the full heading back, for example, highlight the first row, from A1 to G1. Click on the Home menu, and then locate the Alignment panel. Click Merge and Centre. |
| But that's it for Web Queries. They are quite simple to do, and can come in handy if you're out on the road. |
| Import Text into Excel 2007 |
| Nothing equals Excel for crunching of numbers, but what if your data isn't already in an Excel spreadsheet? It's as simple as using the Text Import Wizard. |
| Open a blank or existing Excel 2007 spreadsheet. Click the Data tab on the Ribbon and choose "From Text". |
 |
| Choose the text file you want to import and double click. This will open the Text Import Wizard. Choose your data type and which row you want to start the data at and click next. |
 |
| Step 2 of the Wizard allows you to manage the break lines between your data. |
 |
| Step 3 of the Wizard allows you to choose column formats, provides data preview, and other advanced settings. When everything is organized click on Finish. |
 |
| A final window will be displayed asking where you wish to put the data into the sheet. Here you can see I chose A6. Click OK. |
 |
| Your Text Data will now be input into the Excel sheet! |
 |
| Keyboard Shortcuts for Excel-2007 |
| Know the Various Keyboard Shortcuts |
 |
 |
 |
Post a Comment