Tag Archives: Excel 2010

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.

Date_Math_8

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.

Date_Math_4

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.

Date_Math_9

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

=INT((START_DATE – ORIG_PAYDAY)/14).

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:

Date_Math_3

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.

Date_Math_5

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.

Date_Math_6

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.

Cheers,

Annie

Custom_Function_1

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.

Custom_Function_2

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%.

Custom_Function_3

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.

Custom_Function_4

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.

Custom_Function_5

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.

Custom_Function_6

Custom_Function_7

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.

Cheers,

Annie

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.

PI_DAY_Excel_1
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!

PI_DAY_Excel_4

PI_DAY_Excel_5

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:

PI_DAY_Excel_2PI_DAY_Excel_3

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

Annie

 

Using FIND, LEFT, and RIGHT Functions to Extract Data in Excel

FIND_LEFT_RIGHT_Excel_6

Top of my to-do list this week were preparations for a special event planned for next month.  Our invitees are pulled from our database using certain criteria. Though spouses may not meet the selection criteria, we do want to include spouses in the invitation. Our database generates a list as follows:

FIND_LEFT_RIGHT_Excel_1

Invitations  may be generated now with the data just like it is. Our word processing program will mail merge directly from this list in Excel. On the other hand, the name tags would look very goofy with both spouses’ names. In order to accurately create the name tags, we need to separate the first names into two columns, Name 1 in column C and Name 2 in column D. Fortunately, the word and is consistently used and we’ll use the FIND, LEFT, and RIGHT functions to find the names relative to "and."

Extracting Name 1 using LEFT() and FIND()

The LEFT() function has two parameters. The first is the source cell in which the data is located. The second is the number of characters that is to be returned, counting from the left side of the source. The most straightforward example might be a cell that contains a 7 digit part number (e.g., cell A1) with the first two characters representing the product category. The formula =LEFT(A1,2) would return the 2 digit product category.

Our need is slightly more complicated. Since different names have different lengths, we’ll use FIND() to calculate how many characters to return. The formula  =FIND(" and ",B2) returns the answer of 9. That tells us that the first name is 8 characters long and the space before “and” is in position 9. We’ll nest the FIND() function within the LEFT() function and end up with

=LEFT(B2,FIND(" and ",B2)-1).

Copying the formula to all rows identifies another complication. In the Keiler household, there is only one person so our formula returns an error.

FIND_LEFT_RIGHT_Excel_2

One solution is to use ISNUMBER to evaluate whether " and " exists in the cell and if TRUE, follow our above formula. If FALSE, the Name 1 result generated by the formula will be the same as the Salutation (column B).  Putting it altogether:

=IF(ISNUMBER(FIND(" and ",B2)),LEFT(B2,FIND(" and ",B2)-1),B2)

FIND_LEFT_RIGHT_Excel_3

Perfect!

Extracting Name 2 using FIND() and RIGHT()

The RIGHT() function is similar to LEFT(), returning a specified number of characters from the right side of the source data. Since FIND(" and ",B2) yields a result of 9, we can add 4 to calculate the position of the space at the end of "and." This means that Name 2 begins in the next position. Now we just need to calculate the total length of the cell’s contents to determine the length of Name 2. The LEN(B2) function gives us the total length as 17.  Since we know from the FIND() portion of our formula that Name 1 plus " and " equals 13 characters, 17 minus 13 equals 4 which is the length of Name 2, Mary, in the Robertson family. It’s easy to see when we count it out as follows:

FIND_LEFT_RIGHT_Excel_4

Nesting each function we have:

=RIGHT(B2,LEN(B2)-(FIND(" and ",B2)+4))   Copying it through all rows, here we go…

FIND_LEFT_RIGHT_Excel_5

ARGH! There’s that error again! We forgot to test if the Salutation included and before embarking on the calculation. Fortunately, we can use the same logic of the ISNUMBER function to test that first. If there is no " and ", the cell should be left blank, otherwise the second name is extracted to Column D.

=IF(ISNUMBER(FIND(" and ",B4)),RIGHT(B4,LEN(B4)-(FIND(" and ",B4)+4)),””)

The names are now all separated and name tags can be prepared.

FIND_LEFT_RIGHT_Excel_7

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

Cheers,
Annie