5 Quick Formatting Tips

Sometimes we don’t have a lot of time to fancy up the presentation of data in an Excel spreadsheet. On these occasions, try these 5 quick formatting tips.

  1. Change column widths and row heights to provide extra visual white space instead of inserting blank columns or rows.
  2. Set alignment of each column appropriately for the data it contains.
  3. Use Word Wrap or Alt-Enter to make text spread across multiple lines in individual cells.
  4. Bold and increase font size of title. Merge and Center across multiple cells.
  5. Use borders and shading to further define columns and header information.

Take a look at the difference these 5 tips make:

BEFORE:Quick Formatting
AFTER:Quick Formatting

Get more formatting ideas here. If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.


Using Date Math in Excel to Calculate Payday Dates

Date_Math_8It’s the time of year that young, energetic college students are being interviewed for  summer internships. Today we selected our first summer hire and began the seemingly straightforward process of preparing the employment paperwork. The full packet grows heavier each year with the employee handbook, policy statements, a multitude of governmental forms, emergency contact form, and so on.

There is one part of the process that I find more than a little bothersome. The employment letter specifies the first day of employment and explains to the new employee the bi-weekly pay periods and the date that the first paycheck will be issued. Usually I pull up an electronic or printed calendar and go through a lot of manual gyrations, circling the paydays and counting back through the paycycle. Today I decided to spend a bit of time in Excel to automate this process.

Date Math becomes a “Calendar Calculator”

Date math refers to several functions in Excel which return the number of days between two dates or allows days to be added to or subtracted from a particular date. Our payroll is on a bi-weekly cycle with a one week delay. Each pay period begins on a Sunday and ends on a Saturday. Paychecks are issued on Friday. Using the month of March, if the pay period  starts on Sunday, March 2, it will end 14 days later on March 15. The processing will occur in the following week and the paycheck for that period will be issued on Friday, March 21.


My objective is to enter a new employee’s start day and have formulas calculate his or her first payday, the first date of the pay period, and the last date of the pay period. To begin I pick a random payday in the past – January 11, 2013. We’ll name this ORIG_PAYDAY in the formula. It could have been any prior payday as all future paydays will always be some multiple of 14 from any prior payday. Next the employee’s start date (START_DATE) is entered in Cell B2.


The formula build begins with determining the number of pay periods between the ORIG_PAYDAY and the START_DATE.


The INT() function rounds the result down to the whole integer, so 30.2142 becomes 30. Thus, the new employee’s first payday will be either the 31st or 32nd payday after our arbitrary ORIG_PAYDAY.

Since it will be infrequent that a start date will fall precisely on the day of a new pay period, we’ll also need the remainder, i.e., the number of extra days beyond the 30 pay periods. That function is MOD((START_DATE – ORIG_PAYDAY),14), yielding in this case: 3. The remainder for each possible start date is shown in red below:


Note that all of the possible start dates with a remainder of 8 or less will be within the 2nd week of the pay period and will be paid on March 21, the 31st pay period. However, any start date with a remainder of 9 or more will fall in the processing week which is actually the 1st week of the next pay period and will be paid on April 4, the 32nd pay period.

Putting it altogether using date math, we add to the original payday the product of  pay periods between the start date and original payday and plus one times 14 days.


The final calculations are easy-peasy. Subtracting 19 from the first payday returns the first day of the pay period and subtracting 6 returns the last day of the period.


Breaking long formulas down into their component parts can help make them less intimidating. Creating this formula by building those components one at a time took a good thirty minutes, a fair amount longer than my usual manual calendar counting. However, now I have the worksheet saved in the same folder in which I store the employment letter templates and in the future, it will take all of 15 seconds to open the file, type in the employee’s start date, and receive all three relevant dates.

If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.




How to Create a Custom Function in Excel

User_Defined_1I’ve always been intrigued by people who are outgoing enough to “flower power” their vehicles. While secretly wishing to have that sort of bravado, I have to admit to a shyer personality that could never handle such an attention-getting customization.

On the other hand, customizing Excel to match my preferences is right up my alley! In two previous posts, I illustrated how personal format preferences could be set as the default for new spreadsheets (here) and how the auto-formatting of hyperlinks could be removed (here). Today’s post will create a user defined or custom function for a calculation that is not available in Excel’s built-in functions.

A Custom Function to Calculate Percentage Change

It’s always been a mystery to me why there is not a built-in function to calculate percentage change between two values. Although it is simple to calculate, I use it often enough that creating a function could save a lot of time. We’ll use the following data to calculate the percentage change in the number of units sold from one month to the next.


To manually calculate the percentage change from January to February, in Cell C3, we’ll provide =(B3-B2)/B2. Essentially the original number divided by the net change in the original and new value yields the percentage change, in this case, 3.6%.


To transform this formula into a custom function, we’ll need to use the Visual Basic Editor.

1. Press Alt + F11. Select Insert on the Menu Bar. Select Module.


2. Enter the following code. Note that the name of the custom function and the two parameters – PerChange, Older, and Newer were chosen by me on the fly. My first choice was Old and New for the parameters but New was a reserved keyword, so I couldn’t use it.


3. Press ALT + F11 to return to the spreadsheet. The function may now be used in Cell C3 to calculate  the percentage change by identifying the Older and Newer Values in Cells B3 and B2, respectively.



Saving the Function to Use in Other Workbooks

To save this custom function (and others!) to be available in other workbooks, they will need to be saved in a file that is stored in the XLSTART folder.

1. Search for the location of the XLSTART folder in Programs file hierarchy. Using Windows Explorer, search for XLSTART kind:=folder. Make note of the location.

2. Click on File, Save As. Provide a name in the File Name. Select Excel Add-in in the Save as Type drop down options. Navigate to the XLSTART folder located in step 1. Click the Save button.

When Excel starts, the Add-in will be resident in memory and accessible to all workbooks.

If you found this post helpful, you may subscribe by email to receive more great tips, tricks, and templates in your inbox.



Creating Flashcards with Google Spreadsheets and Flippity

Flash_Cards_Excel_1Remember the days of learning pre-calculator math skills? Flashcards are such an iconic symbol of memorizing addition, subtraction, multiplication, and division pairs. Their purpose expanded through the years as I recall writing states on one side of index cards and their capitols on the other side and vocabulary words on one side, their definitions on the other.

Flashcards for Today’s Students

Certainly the learning tools available to our schoolchildren today encompass all sorts of devices. I came across a rather cool site this week which combines the age old flashcards concept with electronic efficiency. Flippity.net converts data in a Google™ Spreadsheet into personalized electronic flashcards. Instructions on the site are very straightforward. Just follow the link to copy their Google™ Spreadsheet template, fill in your own data, save with a new name, copy the link, and click GO! Couldn’t be easier.

If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.

Celebrating PI Day in Style with Excel

One of the radio stations I enjoy on my drive to work has a segment each day to announce the National {___} Day. Some represent historical facts, such as National Girl Scout Day on March 12 commemorating the first troop meeting on March 12, 1912. Others draw awareness to a serious issue, such as National Human Trafficking Awareness Day (January 11). Then there are the downright silly, e.g., National Step in a Puddle and Splash your Friends Day (also January 11).

I missed a big one yesterday. Did you celebrate National Pi Day on March 14? Pi (Greek letter “π”) is the mathematical constant 3.14159 which is the ratio of a circle’s diameter to its circumference. March 14 is chosen to celebrate this mathematical concept because this infinite number begins with the first three digits, 3 1 4. Check out the fun website of piday.org for ideas to celebrate Pi Day, such as having friends over to watch the movie Life of Pi.

Image courtesy of piday.org.

Using Pi in Excel

Before we get too off-topic, you may be happy to know that if you last used Pi in high school geometry, Excel stands ready to take care of the Pi calculation for you. The PI() function in Excel will provide the constant in calculations. Recently I was covering a piece of furniture that had a circular top. I knew the top was 24 inches in diameter, but I needed to know how long a trim piece would need to be to go around the outside edge or circumference. The formula =B1*PI() provides just the information I needed — 75.4 inches!



One More Reason to Celebrate Pi Day

The piday.org website shared one particular tradition that I will make part of my future PI Day celebrations. Using those first three digits, 314, look what happens when we take a mirror image:


Won’t you join me for a piece of PIE to celebrate PI Day?