Join David Rivers for an in-depth discussion in this video Customizing the user interface, part of Excel 2008 for Mac Essential Training.
In the previous lesson we took a brief tour of the user interface to get you comfortable in your surroundings and I mentioned that certain defaults could be changed not just temporarily but permanently if you wanted to. For example, if there were certain buttons on your Standard toolbar that you never used, you could remove them or if there were certain commands you use on a certain basis that didn't appear there, you could add them. Maybe the default view that you see here, which is our Page Layout view, is a view you never use; you prefer to work in Normal view. You can change that default and most of the changes if you are going to make them permanently are found in the Preferences section under the Excel menu.
So we go up to Excel, down the Preferences, it really doesn't matter what's on your screen in the way of a workbook. I have a blank workbook but anything could be open at this point. We are going to go to our Excel Preferences which is broken up into three categories; here you can see Authoring, Formulas and Lists and we got Sharing and Privacy down below. When it comes to your user interface, you are going to find most of those options here under the View section. So let's start there, we'll click on View. Now the view section is broken up in two multiple sections here as well; we've got Settings, we've got comment options, For objects, we've got Window options and you'll notice as we hover over these, we see a brief description down below about the heading or the topic that we are hovering over with our mouse.
So for example, Show formula bar, you can see displays the formula bar. Now if you work a lot with formulas in Excel, and you access the formula bar on a regular basis, you might want this showing just by default not just when you need to use it all the time. Look down below here, our Show status bar checkbox is selected, that's why we see this status bar at the bottom of our screen by default. But if you don't use it, you can turn it off just by deselecting the checkbox. I am going to leave it on. Here is our Page Layout view that's our preferred view for new sheet.
So when we create a brand new workbook and we got a brand new sheet in there, this is the view we are going to see but we could change it to Normal if we want it to and not that becomes our a new default. So every time we launch Excel and create a new workbook, create a new sheet, this is the view that it will be in. I am going to leave mine at Page Layout because that's what I like. I am not going to worry about the comments section; we'll come back to this perhaps later on when we work with comments collaborating with others in spreadsheet. Over here on the right-hand side where we got Window options, there is a lot of checkboxes that are selected.
Show function ScreenTips so when we start working with functions later on, you are going to see these little ScreenTips popup. Show sheet tabs, these are the tabs we see down below for Sheet 1, 2, and 3 which can't be renamed by the way. We'll do that later. There's our vertical scroll bar and horizontal scroll bars that we looked at in the previous lesson. If we want to show zero values, you bet. If I type in a 0, I want to see a 0 as oppose to a blank cell. At about outline symbols, as I hover over this, you can see down below displays outline symbols if the sheet contains an outline.
So when we are using outlines for example, where we've got headings and sub-headings and so on, we'll so those little symbols representing the outline. Show row and column headings, so we are seeing A, B, C, D and rows 1, 2, 3, etcetera down the left-hand side and the page breaks as well if I move this window out of the way, you can see the page breaks. Specifically in Normal view when we see that dashed line going down the right-hand side of our screen or across the bottom, those are the page breaks. Now we don't want to show formulas usually by default, the formulas will be typed in the formula bar but we'll see the answers in our spreadsheet.
So that's why that one is not selected but some people preferred to see the formulas as oppose to the answer. That's why you turned that on by default. Now we can use our Back button to go back to the first screen of preferences or click the Show All button right here to go back to this first Excel Preferences screen. I just want to take you to General for a second. When we click on General, there are some options in here that apply to your user interface. For example, down here Sheets in the new workbook represent 1, that's why when we started this up, put a brand new workbook we had one single sheet button.
If you typically, when you create brand new workbooks, like to have two or three sheets, you can change this number if you like. The other thing that happens, you'll see it over here in our Formatting palette is we get a default font and a default font size assigned to us which we can change of course but if we find ourselves changing it on a regular basis over and over and there is repetition there to the same font and the same font size, why not change it here ones, make it the default so we can choose from any of these fonts if we wanted to like Times New Roman, for example, and maybe change the size to 12 points that will become the new default as I click OK down below.
So I am going to actually change that back to Verdana and 10 points. Just so we are all starting on the same page. You can also choose where your files are going to be stored, so Preferred file location that becomes your default location for opening and saving files in Excel. At startup, open all files in, you can see down below here specifies the folder from which Excel will automatically open files at startup. So a number of options here to choose from, you may see some that don't make sense like this first one; Use R1C1 reference style.
Excel actually uses a reference style. We talked about it in the previous lesson where we go by column and row. So the very first cell is the address being A1. But if you prefer to use column 1, row 1, you can change to that reference style of that which you are accustomed to using. So these are a bunch of options that get very nit-picky but you can really specify exactly how Excel is going to work for you. You can change your user interface, you can change the number of options. If we go back up to Show All, you'll notice there's lots of options in here, we are going to be coming back to these like for AutoCorrecting and editing, when we are working with charts that are default, same thing goes for colors.
Down below when we start working with our formulas and lists later on, we'll probably come back here to see some of the defaults and see what we can change when it comes to calculations and checking errors and creating lists. Down below under Sharing and Privacy, there is a whole chapter dedicated in this title to sharing your work with others. So there are some Save options, for example. There are some Compatibility options, Security, Feedback and there is our Gallery that controls the behavior and appearance of that Elements Gallery we saw just in the last lesson.
I am going to click on this one because you can see that there are certain checkboxes that are checked off and other that are not. Magnify element thumbnails, so if I click Cancel down here and I go up to my Gallery and I click on Sheets, for example. As I move down over the thumbnails, you can see how they get a little bit bigger than that rest. That's at animation that takes up a little memory in your computer and if that's not a problem, you can leave that turned on. So let's go back to Excel Preferences and we'll go over to our Gallery to look at some of the other options.
We'll leave that one turned on. Show Elements Gallery at startup is not selected and we do see the tab so we can go to them but if you'd like to have it open right at the very moment you start up Excel and create a new workbook or open an existing workbook, you can turn that on. The Appearance is what we call Excel Green, that's the green you are seeing in the background and there is a little bit of Transparency in there you can set to 5%, the maximum being 10%. So that's how much you can see through the actual Elements Gallery in behind. So I am going to click OK down below just to close that up.
I am going to click on Sheets again to close up my Elements Gallery. Now you know how to adjust your UI, what about the toolbars. Well, we know we can go up to View, for example, come down to Toolbars if we want to show certain toolbars like the Formatting toolbar. So there it is and if we don't want it any longer, we go back up to View, down to Toolbars and we turn it off the same way we turn it on by clicking it. But you can actually modify toolbars and in this case, we don't go to the Excel Preference, we go up to View, we come down to Customize Toolbars and Menus.
So when we click here, a different window opens up; Customize Toolbars and Menus. You'll see checkmarks next to the toolbars or menus that are currently turned on. If the Formatting toolbars when you like to have on all the time, you can turn it on right here. Notice that it's docked right here underneath our standard toolbar, it's not what we call a floating toolbar. That's because there is checkbox here in the dock column. So if you don't mind having that floating, you can click on it and it's now floating around back here somewhere you can see it just up across the top here and we can move that around when it's floating.
But the default is docked and I like that if I am going to use the Formatting toolbar. Like I said, though, in the previous lesson with the Formatting palette, it's not so important to have that on automatically by default, so I am going to turn it off. There are other toolbars that can be turned on by default and the way the toolbars appear, it can be adjusted. Look down below at Show icon and Text by default that means look at the Save button, there is an icon looks like a floppy disk and there's the text down below, Save. So if you only wanted, for example, if we deselect that just the icon to show up, you can do that.
I don't mind having the text until get familiar with the icons. Show ScreenTips for toolbar commands that means as we hover those toolbar buttons, we see a little bit of a tip of what it's going to do. Shortcut keys also show up in the ScreenTips by default for me because this is selected and may not be selected for you and that's totally up to you. If I leave that selected and click OK, as I move over some of these buttons for example, the Print button, you'll see that Print one copy of this sheet is the tip and then in brackets Command+P is the actual keyboard shortcut for that.
Now when you turn on features like this in Excel, they actually apply to the other applications like Word and PowerPoint, for example. So I am going to go back to View, down to Customize Toolbars and Menus and I am going to turn that one off, I don't need to see the shortcut key in the ScreenTip, yours may already be off and you can just leave it like that if you want. Show typefaces in the font menus, so when you see the name of a font, you actually see it in that typeface so you get an idea about what you are selecting. I like leaving that one on for sure.
Now you'll notice up here that my menu bar has changed a little bit. I am going to move this dialogue box down and where I use to see File, Edit, View going all the way across to top, it's kind of cut off now, it's much shorter and I am seeing all of these options with little triangles next to them meaning that I can now manipulate the menus. For example, if I click on the File drop-down, there is all of those File commands but I can't actually select them like I can't save, for example, because I am in edit mode here, I am customizing toolbars and menus.
Meaning if I want to reverse the order of Save and Save As, I can click and drag. Now when I go backup here, you'll notice that Save As comes before Save. I am going to drag that down where it belongs. The other thing you can do is add commands. So if I go over to my Command button here, in those All Commands is the category selected by default, so I am seeing a listing over here of every possible command in Excel. But if I want to see File commands, such as New, Open, Close. Well, Close is not a button that I see on my Standard toolbar but it might be one I use on a regular basis when we know we can come up here to close a file by clicking the red button, but if you like a Close button, you can just drag it up there.
So we click, we drag and we drop where we want as a little separator showing up, I want it right beside Open and before Save, I let go and now I've got a Close button. If I don't want a button up here, I just drag it off and let go and it's gone. So these are all the File commands and if there's one in there that you use on a regular basis, for example, this Print button, I prefer this over the Quick Print button. We'll be talking about printing later on but when you click a Print button like this, by default everything just get send to your default printer.
You have no choice in the matter of how many copies or selected area, single page but when you go to the Print option with the ellipsis after it, you do get to choose from a number of options. So I'd like to drag that one up and take the Print button and Quick Print button off. That becomes my default. So anytime we click OK, we are actually creating a new default. This goes for all of the different categories, there're all of the different menu items we are used to seeing here; formatting commands, there is Tools, Data commands, Charting commands.
You can see there is quite a long list. You can even create your own menus, clicking New Menu here allows you to add commands to your own new menu. So I'll drag this up here, have a new menu that I could access in fact but didn't even want to see this menu, I could close it and use my own. So if you want to take the time to design your own menu, you can kind of neat. So I am going to click OK down at the bottom, it's going to close up that dialog box. The only change I really made to my toolbars and menus is this one right here, I now have Print button instead of Quick Print.
To change that back, remember you have to not go to the Excel Preferences but rather the View menu to Customize Toolbars and Menus and I am just going to change that quickly back to the way it was. So I am going to scroll down here to my Print options, I am going to drag Quick Print backup and take the Print button down. So later on when I am talking to you about printing, we need to have the same user interface. So I am going to click OK to save that change and back to my blank workbook in Excel and now you know how to customize your own user interface, your toolbars and your menus.
This will help you setup an environment that's best for you according to your needs when working in Excel.
- Customizing the user interface Using workbooks Adding and removing sheets Restricting input with validation rules Formatting workbooks Using formulas and functions Working with charts Adding, removing, and editing text Aligning and layering objects Creating PivotTable reports Sharing spreadsheets Creating custom templates
Skill Level Beginner
Q: How does one generate an average using cells in columns that are not consecutive i.e. a7,c7,e7...?
A: To get an average of non-contiguous cells, you can either select them individually, or type them in manually. Here are the steps involved:
1. Click in the cell where you want the average to appear
2. Start the function by typing: =average(
3. Now, either type the cells addresses (ie A7,C7,E7) or select each cell by clicking them while holding the Command key.
4. Close off the function with closing round bracket: ) and press Return key
You should see the answer in the cell where you entered the function. Checking the formula bar, your finished "formula" will look something like this: