Greg Creech, MCT, CompTIA CTT+, IAAP TEC16 Speaker
Let’s Excel @ Excel and learn analytical and productivity tools to help lessen frustration and increase your enjoyment of Microsoft Excel. While we will learn PivotTables and Subtotals–great analytical tools–we’ll use data manipulation tools, too, including multiple sorting and filtering and managing a huge data set. Here’s some of the areas we will cover in our time together:
- Download and create database tables in Excel
- Analyze information with PivotTables and SubTotals
- Create and use important functions for database fields including CONCATENATE, LEFT/RIGHT, and VALUE/DATEVALUE
- Use Text-to-Columns to separate data
- Best practices and shortcuts for working with Excel tables and databases
- Using the Name Box and Name Manager
What’s in a name? A whole lot! The Name Box and Name Manager
One of the tools we’ll learn is the Name Box and Name Manager. The Name Box and the Name Manager help in productivity by having functionality to help in these areas:
- Using Absolute Reference
- Navigating in an Excel Workbook
- Selecting cells, rows, and columns
- Naming a table or database to use for PivotTables and other applications, including Access
- Setting print area and selection for printing
- Creating a value to use throughout your workbook
Name that Tune (I mean Name that Table/Area in Excel)
The Name Box is in the upper left corner of your workspace area and is adjacent to the Formula bar. When using the Name box, you should not include spaces and you must press enter for Excel to accept your named cell or cells. You may want to avoid numbers, too; however, you may use numbers with care and always press enter. Here’s how to name cells, rows, columns, and areas of Excel:
- Select the area (rows, columns, cells, or an individual cell) of Excel you wish to name
- Click in the Name box and type a Name, such as Rate, Finance_Table, Payment
- Press Enter and viola, your area has a name
Now, you may use this name to select and navigate to this named range. For example, you may be in any other worksheet in your workbook and quickly navigate to and select the area. Simply click the drop down arrow on your Name box and you will see how the Name Box lists your names alphabetically. When you click the name in your Name Box list, Excel quickly jumps to that area and selects it for you.
No more scrolling and searching for specific areas in Excel and you won’t need to click and drag until your mouse falls apart to select a huge data table! And, liberal arts majors like myself, love the Name Box/Manager because we can use words to accomplish math. You can edit, delete, or add Names using the Name Manager in the Formulas tab of the Ribbon which we will learn in great detail at IAAP TEC16.
Greg Creech, MCT, CompTIA CTT+ began his corporate career at Southern Bell on January 5, 1981 as a Steno clerk. After leaving AT&T gracefully, he certified as a Microsoft Office Master Educator and has a 10 book Microsoft Office series entitled The Better Business Management series for Word, Excel, PowerPoint, Access, and Outlook. In 2007, he received the top honor from the University Continuing Education Association for having the top non-credit programs for ALL continuing education universities and colleges in the south, for his Microsoft Office Suite courses. In 2009, the Association of Continuing Higher Education honored Greg with the Faculty Award for his work at Emory University.
IAAP Edge 