Tag Archives: Excel

tech tips: Full Excel Potential Reached?

What is being offered to enhance my Excel skills?

EdTech Training is offering Excel 2010 Open Lab sessions on Central Campus, starting the first week of March. This open lab format series enables the completion of the Excel 2010 Step-by-Step book chapters, accessible in the campus library. Other Step-by-Step books available in the library are Access 2010, Outlook 2010, Word 2010, and PowerPoint 2010. These books are available only for employee checkout. The Step-by-Step books are extremely user-friendly. The chapter details include specific practice exercises one step at a time with screen shots. The exercise files are for the books are located at P:_TPDStep by Step Files in the San Jac network. The sessions detailed below are accessible for registration in Avatar scheduling system.

Excel 2010 Workshop Series:

Week 1 – Setting-Up a Workbook & Working with Data and Excel Tables – chapters 1-2

Week 2 – Performing Calculations on Data & Changing Workbook Appearance – chapters 3-4

Week 3 – Focusing on Specific Data by Filters & Reordering and Summarizing Data – chapters 5-6

Week 4 – Combining Data from Multiple Sources & Analyzing Alternative Data Sets – chapters 7-8

Week 5 – Creating Dynamic Worksheets by Using PivotTables & Creating Charts and Graphics – chapters 9-10

Week 6 – Printing & Automating Repetitive Tasks by Using Macros – chapters 11-12

Week 7 – Working with Other Microsoft Office Programs & Collaborating with Colleagues – chapters 13-14

Week 8 – Working with Other Users – review of the series materials and get assistance with specific projects

Excel 2010 Training Sessions:

Pivot Tables

Formulas Introduction

What happens if I am unable to attend all of the sessions in the workshop series?

The series is can be approached as individual, self-paced workshops. You are welcome to attend the specific sessions that cover chapters that you would like some additional assistance with the exercises. Attendance will be marked for each week of the series and certificates can be printed in Avatar.

Is there a way to request training for a specific group, date/time, or topic?

Yes. EdTech Training offers Training by Request.

tech tips: an example of automation with Excel

Excel is a powerful tool that can help automate some of the tasks you are currently doing completely manually, or with a combination of manual paperwork and spreadsheets.  Thus saving time, tediousness, and trees.

EdTech Training & Professional Development (TPD) wants to provide you and your staff with targeted training solutions that increase your performance and help you do more with less.  (And we’re all feeling a bit more of the “less” lately, aren’t we?)

Highlight the following file path and paste into your browser’s address window (or simply navigate to it from your San Jac desktop) to download the file and explore it:

file:///P:/TPD/genericautomationexample.xlsx

(If you have trouble accessing the form, email me and I’ll be happy to send you a copy.)

This workbook is an example of an automated form that I helped a faculty member design to save precious hours of keeping manual records, transferring them between people and semesters, deciphering handwriting, and then converting them to electronic format along the way. This form is still being tweaked, as it’s utilizing a combination of quite a few of the more advanced features in Excel–but we’re here to help!

While created in Excel 2010, the features were all still available in Excel 2007.  The form is designed so that someone can either choose a procedure from the drop-down box, or start to type in a procedure (see the list of procedures on the sheet called Table) and have it finish filling in the name of the procedure (once Excel has narrowed the possibilities down to one procedure), as well as some of the associated information with that procedure.

The primary features of this form are

  • it acts as a database using the VLOOKUP function to populate designated cells
  • in order to suppress error messages resulting from blank cells, it uses the IF and ISERROR functions together
  • it’s formatted with a Table Style
  • cells with formulas (in this case the columns such as L1, L2, L3, Core, etc.) utilize Locked Cell together with Protect Sheet so the formulas don’t inadvertently get overwritten
  • it auto-calculates totals at the bottom with the COUNTIF function (specific to count text entries)
  • the header information utilizes Freeze Panes so that it’s visible even with horizontal scrolling

If you feel your job or department could take advantage of automation with Excel, give me a call at x7286 or email lisa.lacombe@sjcd.edu to see if we can help with your technology learning needs.