Executive Training Solutions

You may have noticed the CUSTOM VIEWS button on the VIEW tab in Microsoft Excel, but not known what that button was used for (Custom Views are available in older versions as well, but not as obvious on the tool bar.)

Custom Views allow you to set print margins, hidden columns, and other view options so that you can have multiple views saved with your spreadsheet.

This can be useful if you and a co-worker often use the same spreadsheet, or you need one view for data entry and another for printing reports.

In Excel 2007, go to the VIEW tab, and choose the CUSTOM VIEW button in the WORKBOOK VIEWS group. At this point, a dialog box will pop up and you can ADD and name different views.  Note that these views are then applied to all worksheets in the workbook.

For more tips and tricks using Microsoft Excel, attend one of our classes.  We are holding a seat just for you!

Excel Pivot Tables are a powerful tool in Microsoft Excel, especially when you are working with large amounts of data. When I show students a range of 2000+ rows and create a pivot table that summarizes all that data in maybe 15 rows, there is sometimes skepticism that all the data has been pulled into the pivot table.  Other times, users need to know exactly what data has been summarized so that they can share that subset of data with others.

Excel has made it easy to find out what data feeds into each summarized number.  In the Pivot Table, double-click on the number in question.  A new worksheet is created, with a detailed list of the rows of data that made up that summarized number.

You can then print, or graph, that subset easily.

For more ways to use Microsoft Excel to help you work more efficiently, check out one of our Excel classes. We love helping students learn more about the software we love!

Microsoft Excel offers a wide range of database functions that allow you to analyze your data. One of the most common database functions is the DAVERAGE function.  (Database functions begin with a “D”).  The DAVERAGE function allows you to find the average of a certain field in your table that meets certain criteria.  This is different than the AVERAGE function which finds the average of all the numbers in a given range.

The syntax for the function looks like this:

DAVERAGE(database, field, criteria)

The database argument holds the range of your database.  The field argument is for the field you want to average.  It can be a cell reference, a field name inside quotations, or the number of the column that holds the range of data for that field. The final argument is the cell range that contains your criteria.

If you have a database that holds test scores, and you want to find the average of all passing grades, you would first set up the criteria range below your database data. That range would duplicate the field names in the first row, and you would place your criteria in the 2nd (and subsequent, if needed) rows.

For example, your data would be held in the range A1: C45. Test scores are in column C. In row 47, you would repeat the field names (likely FName, LName, Score). In row 48, you would place the criteria >59 to set the criteria of only scores of 60 and above.

The resulting function would look like:

DAVERAGE(A1:C45, “Score”, A47:C48)

and it would only calculate an average on those scores that are higher than 59.

For more tips and tricks for using Microsoft Excel, attend one of our Excel training classes. We look forward to welcoming you to Executive Training Solutions!