Definitions

Defining a Name for a Constant or Formula is an excellent practice in Excel. It can simplify the use of your Excel workbook, (especially by others who may inherit the maintenance of the workbook), and add a touch of elegance as a bonus. 

This “elegance” is achieved by using descriptive Names, rather than cold cell references.

Defining a Name Is Very Straightforward:

1.   Select the Formulas ribbon and then click on Define Name

2.   Type the Name and Scope (typically the workbook…)

a.   Inserting a Comment is optional, but recommended in many cases

3.   in the Refers To dialog box, type the reference to the constant or formula and click OK

Although this practice of Defining Names may not seem like a Big Deal in your Excel life, it can add an easily accomplished additional touch of professionalism to your work. Give it a try!

Date-Related Data

As many of us know, Excel stores dates as the number of days elapsed since January 1, 1900, and times are stored as an additional fraction of the date number. Cells will, obviously, display the number or date and/or time, depending on the cell format that you choose.

What is cool, of course, is that you can use Date Functions to perform date calculations and extract the proper date-related data.

The following are Five of the Date-related Functions that are useful to nearly any Excel user:

1.   =DATE(year, month, day)

o   This function returns the Excel date number for the date indicated by the year, month, and day

2.   =YEAR(date) or =MONTH(date) or =DAY(date)

o   Returns the year, month, or day of the date

3.   =TODAY()

o   Returns the current date (as calibrated in the computer system being used)

4.   =WEEKDAY(date, start weekday)

o   Returns the week’s day number for the date specified

5.   =NETWORKDAYs(start, end, holidays)

o   Counts number of days between the start and end dates, excluding Saturdays Sundays and holidays

Handling Dates and Time in Excel can be a bit daunting. If you take a few minutes and master the foregoing Five Functions, however, it can make your Excel life easier.

A Small Excel Annoyance

Let’s face it, there are some small annoyances within many of the software programs we use. This is, of course, also true of a few in Microsoft Excel.

Take for instance the way Excel changes the column widths when you refresh the data in one of your Pivot Tables. You’ve got your table looking smart and professional, you refresh your data and, Bamm, it’s a bit of a mess!

So how do you maintain your chosen column width without having to reformat after every refresh? There are a few good ways of doing this, but the easiest and most effective is to do the following:

1.   Right-click your table and go to PivotTable Options

2.   Click on the Layout & Format tab

3.   In the Format grouping at the bottom, remove the checkmark from Autofit column width on update

Simple as that. Oftentimes, the best solutions are modest and elegant. Give it a try, and get rid of one of the small annoyances in your Excel work.

Turn the Tables

Excel Tables are, without a doubt, powerful tools for data analysis. If you are not familiar with Tables, or you just haven’t gotten around to using these comparatively new gizmos very much, I encourage you to take a closer look.

Here are Three of My Favorite Benefits of Excel tables:

1.   They’re a Breeze to Create

There are two essential ways to create an Excel Table: The first is to select your data, go to the Insert ribbon, click on the Tables down-arrow and choose (are you ready for this?) Tables! Okay, that’s cool, but the way the Cool Kids in School do it is to simply select your data and click Ctrl + T. Bamm, Done!

2.   Slice Those Tables!

Slicers are another one of the comparatively new tools in Excel that should not be overlooked. They are particularly effective at visualizing your data which, is a best practice in getting your message across without unnecessarily pouring through the numbers. Here’s what you do: Selector table, go to the Design ribbon, and select Insert Slicer. Nothing to it!

3.   Create Self-Expanding Charts

If you are like most Excel users, you have spent a great deal of time manually updating your chart data ranges when making repetitive reports. This is a total drag and needless work if you use Tables. If you set up your chart based upon data in Table format, any additions that you make to the data within the table will automatically be reflected in the chart. Now I ask you, How Cool Is That? 

Excel Tables. Make a New Year’s resolution to integrate them into your daily Excel routine. Happy New Year’s, All!