Pass Your Microsoft Excel Certification Easy!

Prepare with top-notch Microsoft Excel certification practice test questions and answers, vce exam dumps, study guide, video training course from ExamCollection. All Microsoft Excel certification exam dumps & practice test questions and answers are uploaded by users who have passed the exam themselves and formatted them into vce file format.

1.4 Customise Options and Views for Worksheets and Workbooks

1. 1.4.1 and 1.4.2 Hide or Unhide Worksheets, Columns and Rows

To hide a complete worksheet in order to prevent other users from viewing the data or formulas used there. You can hide as many worksheets in the workbook as you want in any sequence. However, you must have at least one unhidden worksheet in the workbook. Simply right-click on the worksheet tab you want to hide and select Hide. Notice that there is no indication that any worksheets are hidden. When unhiding worksheets, you'll be presented with the dialogue box displaying all hidden worksheets for this workbook. From this list, you can select the worksheet you want to unhide. Like so. In some instances, you may want to only see certain columns or rolls, and to hide others, it's possible to hide and unhide roles or columns. This is different from the Delete option, since while the Columbia roll can't be seen, it still exists and none of the data is removed from the worksheet. To hide rows or columns, select the row or rows or column or columns and then click the right mouse button and select the Hide options. So if I select these four rows and then hide them, notice that the numbers of the hidden rows are not in sight. Also, notice that there is a double line where the rows have been hidden. The same is true for columns. If I select these two columns and hide them, you can see the effect. There is effectively no change in the way the worksheet operates. Any formulas that reference the hidden rows or columns will be unaffected by the fact that they have been hidden. To unhide any hidden rows or columns, select the row or column headings on either side and then select Hide. An alternative way to do it is to right-click the double line and select "Unhide." You can also drag the edge of the double line to reveal a hidden column row, but this is a little tricky and I don't recommend it. The easiest way is the first one. Select the column rows on either side and then select "Unhide."

2. 1.4.3 Customise the Quick Access Toolbar

The Quick Access Toolbar is located here above the Ribbon and by default contains the Save, Undo, and Redo command buttons. The Quick Access Toolbar gives you very fast access to whatever commands you place into it, so it's really useful to be able to customise it. For the commands you use most frequently, there are three places where you can customise the Quick Access Toolbar. The easiest one to use is located on the toolbar here. Just click on the icon, and you get this list of commands to select from. Just click on the one you want, and it will be displayed on the Quick Access Toolbar like so, so you can see that the icon for email has been added. It's as simple as that. If you click on the More Commands button, you get a more comprehensive selection based on the Excel Options dialogue box. This actually controls all of the possible customization options for Excel, but we only want at this point to use the Quick Access Toolbar option. Here you can add or remove any feature from the Quick Access Toolbar if it's available in this dialog. You can also get to this dialogue by right-clicking on the ribbon itself, or by going to the File tab on the Backstage View and Options. And once again, we have the Excel Options dialog. So there are a number of ways to get at the Quick Access Toolbar customization options, but the easiest one is just to click on this icon. You can also move the Quick Access Toolbar to a different position directly below the ribbon. By selecting this command from the pull-down menu, you can see the effect, which in some ways makes the existence of the Quick Access options a little clearer. In my opinion, you can see that the commands that are available are categorised to reduce the need to search. Let's say I want the Format Painter command to be easily available at all times. Then I simply select it and press the Add button. Now, when I press Okay, you can see that it's available for Quick Access. Then, to remove it, I simply reverse the highlighted process and press the Remove button. And now it's no longer on the Quick Access toolbar.

3. 1.4.4 Change Workbook Views

To the View tab and then to the Workbook Views group. You can see that there are a number of different ways in which you can view workbooks. In Excel, the default is the normal view, which is how the information is displayed by default and the one that you are most used to. By now, only the worksheet, roles, columns, and headers appear. There is usually no way to see where the worksheet would be broken when you want to print it. Remember that only the finest amount of information would be printed on every page. So Excel has to have some means to break up the worksheet into different pages. For printing, these are called page breaks. You can control where the page breaks will be inserted into your worksheets by using the other available views, such as the Page Break Preview view here or by viewing the worksheet in the Print Preview option from the Print command. The Page Break Preview view is really useful because it displays a watermark on each page showing the page number for each page. It also shows where the page breaks occur by displaying these blue lines to mark the boundaries of the pages. If you want to change where the page breaks are occurring, then you just put the cursor over the line, and you can drag and drop the position of the page break as required. The Page Layout view displays the workbook as it would appear on the printer. For example, you can see which roles and columns will appear on each page, how wide the margins are, and the contents of the header and footer on each page. The vertical and horizontal rulers are also displayed to show the placement of all output on the page. Note that this view is only a mockup of the printout but doesn't take into consideration the specific capabilities of your printer. On the other hand, the Print Preview view, which you access from the backstage, previews your worksheets on the screen more accurately because Excel can then identify what type of printer you're using. The Custom Views option allows you to save your current page layout settings with a name so that you can reactivate them later at any time. If I activate it, you can see that I can add a new view and give it a name for later use. So if I add DM view as a custom view, that view is available from the Custom Views menu when I access it at a later point. One other view option that doesn't appear in the WorkbookViews group in the Ribbon is the full-screen view at the upper right of the Excel window. I can click on the Display Options button and then click Auto Hide Ribbon to enable this view. This option maximises the Excel window to the screen, turns off the Ribbon Quick Access Toolbar and Status Bar, and gives you more space to view the worksheet data. To return to normal ribbon view, click the Ribbon Display Options button, and then click Show tabs and commands.

4. 1.4.5 Change Window Views

We're going to look at all the options available under the View tab and the Windows group of commands. The New Window button enables you to open another view of the active workbook. This is useful when you want to look at different parts of the same worksheet, or different worksheets in the same workbook, without having to scroll around the worksheet to view cells that are far apart. So when I click on the new window button, you can see that I'm still viewing the same information, but that a second copy of the workbook has been opened. If I wish, I can have one worksheet open in one window and a different one opened in the second window. Every open workbook will have its own window on the Excel screen. You will quickly run out of available screen space if you use the new window feature to create a second or more view of any of the worksheets. The Arrange All button in the Ribbon enables you to quickly reposition all open workbook views on the screen at the same time. This dialogue box provides a number of options for arranging the windows on the screen. The tiled option rearranges the worksheet windows on the screen so that all windows are as square as possible and positioned next to each other. The horizontal option positions the worksheet windows on the screen so that each window stretches across the entire width of the Excel screen. Excel lays them horizontally so that you can see as many columns as possible in each worksheet. The vertical option arranges the window so that each window stretches from the top to the bottom of the Excel screen. Excel lines up these windows vertically so that you can see as many rows as possible in each worksheet. The cascade option rearranges the worksheets so that each one is on top of the other and slightly offset to give you a three-dimensional view of all open workbooks. This kind of arrangement is best when you do not want to reduce the size of each worksheet window and can flip between the open windows as needed. In all of these few options, the active workbook is the one with the thin green border around the window edges, and the workbook file name is displayed in bright white in the title bar. The other open workbooks have the workbook name displayed in a lighter shade of grey in the title bar, and the border around the window titles is gray. There is no limit to the number of windows you can have open at the same time. However, the size of your computer monitor will limit how many and how much of each you can view at the same time. Of course, you can change the position and size of the windows manually by positioning the cursor on the property and changing the size and position. if you put the cursor anywhere in the ribbon area. You can drag the window around the screen by holding down the left mouse button and dragging, letting go when you've repositioned the window where you want it to be. You can also resize a new window by placing the cursor on the edges of the window and dragging the edges to resize. While you're holding down the left mouse button, I'll just close the second window by clicking on the small red icon so that we're back to the one active worksheet in Excel. Now we look at the split option. This feature enables you to have just one instance of your workbook open, but to be able to view the current active worksheet using two or four window panes. You can move around in each of the split window views to see different or the same parts of the worksheet without having to constantly shift from one end of the worksheet to the other, or you can move roles and columns around to see the related data closer together. The operation is similar to using four different cameras simultaneously, with the ability to manipulate each one semi-independently of one another. You can point to any parts of the worksheet, whether they are close or far away from each other. Where the split occurs depends on where the currently active row or column is. If you've selected a complete row or column by clicking on the header for either, then the screen is split into two when you click on the Split button. For example, if I select row four and click on Split, then you can see that the screen is divided up into two horizontal panes. Now I can access each window separately because each had their own independent vertical slider bar Here, after splitting the window, the split will stay at that location even as you move around the worksheet. The panes will scroll if you try to move to a cell outside of the current window. You can move the split by positioning the cursor over the split bar and dragging it to a new position like so. If the active selection is anything other than a complete row or a complete column, the screen is split into four panes like so, and you have independent control of each pane. You can only alter the position of either the horizontal or vertical slip bar by using the mouse. To move a slip bar, position your mouse pointer over the bar. When the doubleheaded arrow appears, press and hold the mouse button and move the bar to the new location. To remove the split, just click once on the Splitbutton, or drag the vertical Slitbar to the far left or far right of the worksheet, or drag the horizontal Slitbar to the top or bottom of the worksheet. Alternatively, if you double click on either of the split bars, they will be removed. Next, we look at the idea of freezing panes, which can be extremely useful as your worksheet grows in size. As this happens, it will become increasingly difficult to view all of the data on the screen at the same time. More importantly, you will reach a point where you will no longer be able to keep the row and sort of heading down the screen as you scroll down or across your large worksheets. To help you overcome this, Excel allows you to lock the titles on the screen while you scroll to a remote part of the worksheet. You can lock the top row or rows, or the leftmost column or columns, by freezing panes. This feature provides you with three options from which to choose. The Freeze Panes option uses the active cell to serve as an anchor when the feature is activated. All rows above and all columns to the left of the active cell remain in view until the feature is turned off. You may freeze more than one row or column in place. You can also freeze just the top row or just the leftmost column or columns. Freeze Top Row When you select this option, the selection of the active cell is not important. Excel will always freeze only the one row currently displayed at the top of the worksheet window. Note that if the top row currently displayed on the screen is not row one, for example, row 20, Excel will freeze that row even though your column titles may be in row one. So in this example, the column titles are in row three. If I scroll the rows so that row three is at the top and I select the freeze top row option, you can see that the column titles are fixed in position as I scroll through the worksheet's fixed first column. Again, when you select this option, the selection of the active cell is not important. Excel will always freeze only the one column currently displayed at the far left of the worksheet window. Like the freeze top row option, be sure to verify that the left column currently displayed is the correct one to freeze. Finally, the hide option hides the current worksheet window, and the unhide option is activated when any worksheet is selected.

5. 1.4.6 Modify Document Properties

Files contain metadata, that is, information about the files, such as the date and time, creators, the last time it was modified, who created it, and the size of the file. Excel workbooks can contain additional metadata such as the company name, the name of your computer, the number of times the workbook was modified, andcomments This metadata, which is also referred to as the document properties, can be useful for sorting, organizing, and finding workbooks, especially when you have a large number of them. You can access these advanced properties by clicking on the File tab and going to the Backstage view. You can now click on the pulldown beside the properties here in order to view the workbook's advanced properties. This dialogue box has five tabs that describe all of the workbook properties, some of which you can edit and customize. So, looking at the General tab, all of this information is automatically added by Excel, such as the file size, the name, and location of the file. The Summary tab can be used to enter customised information about the workbook, such as the title of the workbook, which is not the same as the title of the file, the name of the author, and so on. All of these can be customized. The information in the Statistics tab cannot be modified and is created automatically by Excel, such as the date and time of access, data modification, and so on. The same is true of the Document Contents tab. You can select up to 27 predefined document properties in this tab. So for example, if I want to indicate that this workbook was checked by Ann Murphy, then I just highlight that property, enter the information, in this case the text "Ann Murphy," and then press the Add button. I've now modified the Walkbook properties to indicate who it was that checked the document. When you've added a custom property, then you can modify it by highlighting the property in the list and pressing the Modify button, which becomes activated when you've selected an item from the list of properties.

ExamCollection provides the complete prep materials in vce files format which include Microsoft Excel certification exam dumps, practice test questions and answers, video training course and study guide which help the exam candidates to pass the exams quickly. Fast updates to Microsoft Excel certification exam dumps, practice test questions and accurate answers vce verified by industry experts are taken from the latest pool of questions.