Customizing Excel

This lesson explains how to change Excel “application” properties.

 

Summary of Key Points - Learning Objectives

Use Function keys.

Change colors and themes.

Customize printer settings.

Add icons to the Quick Access Toolbar

Customize the tabs that appear in the ribbon.

Use the forms tool.

Create and use a custom list.

Create and use add-ins.

Create and use a custom function.

 

Function Keys

Task: Experiment with each of these function keys.

Press the F1 function key to access the help menu.

Select a cell. Press the F2 function key to edit the cell contents. The cursor will be positioned in the cell in edit mode.

Press the F3 function key to show the list of range names in the workbook. If you press F3 and nothing happens, you probably don’t have any range names in the workbook.

Pressing CTRL +the F3 function key opens the range name manager dialog box.

When typing a formula, pressing the F4 function key locks and unlocks cell references. It has four options: locking both rows and columns ($C$4), locking rows only (C$4), locking columns only ($C4), and unlocking everything (C4). Having everything unlocked (called a relative cell reference) is the default setting. To use this shortcut, select a cell reference and keep pressing F4 until you find the setting you want.

The F5 function key opens the GoTo dialog box. You can use this box to jump to single cells or to range names.

Use the F7 function key to spell-check the worksheet.

The F8 function key is used to quickly select ranges. Click in the cell that you want to be the upper left corner of your range. Press F8. Click an another cell in the worksheet and Excel will highlight a range using the second cell as the lower right corner.  You can use arrow keys to change the anchored cell (click inside the range) or to extend the range (click outside of the range. To complete the range-selection process, press on the F8 function key again.

The F11 function key generates a chart from the selected data and displays it on a new worksheet.

Pressing ALT + the F11 function key opens The VBA editor.  You would use this to edit macros.

The F12 function key opens the file-save dialog box.

 

To change colors and themes

View this video.     Time = 2:36

https://www.youtube.com/watch?v=Axxmll3IoZw

 

 

To customize printer settings

From 10:45 to 13:45 in this video. The entire video is worth watching.

https://www.youtube.com/watch?v=sB0nJlcjFBs

 

To add icons to the Quick Access Toolbar

Task. Add an icon to the Quick Access Toolbar.

From 6:00 to 7:27 in the same video. https://www.youtube.com/watch?v=sB0nJlcjFBs

 

To show the Developer Tab

Task - Activate the Developer Tab.

The Developer tab is useful when working with Macros or Forms.

Click on Files -> Options -> Customize Ribbon -> Put a check box next to Developer -> click OK.

 

To Create a Custom Ribbon tab

Task – Create your own custom ribbon tab.

Open the workbook 2010Data.xlsx. http://bus91L.altervista.org/Data/2013Data.xlsx

Select the Students worksheet. Click on Files -> Options ->Customize Ribbon.

Select the New Tab button in the lower right.

New Tab and New Group will appear in the list of tabs.

Click on the minus sign next to the line that says New Tab (Custom) to hide the New Group (Custom) element.

Click on the Rename icon. In the dialog box, change the name to Forms and click on OK.

Click on the plus sign next to Forms element in the Tabs selection box. Click on the New Group (Custom) element to select it.

In the “Choose commands from” box (top center) select All Commands.

In the list of commands scroll down to find the Form… command. Click on it. 

Click on the ADD>> button in the middle of the dialog box. Note: You can add multiple icons to tabs in this same way, but we are only going to be adding the Form icon. in this exercise.

Click on OK. 

The dialog box closes and you will see the Forms tab in the ribbon.

 

To use the Forms tool

Click anywhere in the student worksheet data. Click on the Forms tab and click on the Form icon. The Form dialog box opens up and is ready to use.

 

To add a new student to the data-table:

Click on New. A blank form will appear. Fill in each of the fields and click Close.

 

To find and edit a record (row of data):

Open the Form dialog box. Click on Criteria.

A blank form will appear. Type in search terms in one or more of the fields.  Click on Find Next.

The first record that meets all of your criteria will appear in the dialog box. 

To see more records continue to press Find Next.

Make a change to the information in one of the fields. Click Close to change the record and close the dialog box. 

 

To find and delete a record (row of data):

Open the Form dialog box. Click on Criteria.

A blank form will appear. Type in search terms in one or more of the fields.  Click on Find Next.

The first record that meets all of your criteria will appear in the dialog box. 

To see more records, continue to press Find Next.

When you find the record you want to delete, press Delete.

 

To create a custom list

Task: create two custom lists, one by typing the list directly into the dialog box, the other by typing the list into the worksheet then selecting the range.

 

Click on Files -> Options ->Advanced Options.

Scroll down through the options listed on the right. Near the bottom you will see a button labeled “Edit Custom Lists.” Click on it. This dialog box should appear.

 

 

You can add a list two ways. One way is to type entries in the “List entries:” box.  Press the Enter key after entering each item. When the list is complete click on the Add button. You will see the new items appear in the “Custom Lists:” box.

 

If you already have a list typed into cells in the workbook, you can select them with a mouse and automatically create a list. Click on the box to the right of “Import list from cells:” Next click and drag across the cells in the worksheet that contain the items you want to include in your list. For example, cells E2:I2. Next press Enter. The range notation should appear in the box that contains the red and blue icon.

 

 

Click on the Import button to generate the list. You will see the new items appear in the “Custom Lists:” box.

 

Click on OK to close the Custom Lists dialog box and click on OK again to close the Advanced Options dialog box. The list you just created will be available in all Excel files you create in the future.

 

To use the list

Type any item in the list into an empty cell.

Move the cursor to the lower right corner of the cell to change it into the AutoFill handle.

Click and drag down for several rows or to the right for several columns and then release the mouse.   The items in the list should automatically fill in the cells you just selected.

 

To use Add-ins

Intro to add-ins     1:41

https://www.youtube.com/watch?v=eTQ6WTSveYI

Task – Activate the Analysis ToolPak Add-in. It should appear either as an icon on the right side of the Data tab, or as an icon in a new tab called Add-ins.

Take a screenshot to show the icon in the ribbon. Paste the screenshot into the CFU form. Crop the image so only the tab and icon are showing.

 

XL Stats

https://help.xlstat.com/customer/en/portal/articles/2062312-running-xlstat-the-first-time-excel-2007-2010-2013-2016-?b_id=9202

To use the XL Stats add-in to generate functions   0:57

https://www.youtube.com/watch?v=IrDxg2XJaaI

 

To create custom Functions and Add-ins   3:19

https://www.youtube.com/watch?v=eEKXks-ChsI

Task - Recreate the BMI custom function.

Task – Create your own custom function.

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name _________________­­______      Date________       Section   _____

Check for Understanding – Customize Excel

Y/N I know how to use Functions Keys.

Y/N I can change colors and themes.

Y/N I can customize printer settings.

Y/N I can add icons to the Quick Access Toolbar

Y/N I can customize the tabs that appear in the ribbon.

Y/N I can use the forms tool.

Y/N I can create and use a custom list.

Y/N I can create and use add-ins.

Y/N I can create and use a custom function.

 

Effectiveness Questions to discuss with your team

What do I know that I didn’t know this time last week?

 

 

What can I do now that I couldn’t do this time last week?

 

 

What could I teach others to know or do that I couldn’t teach them last week?