Select Page

# Month: March 2017

## How To Turn A Column Into A Table Using Formulas

In a previous post I showed How To Turn A Table Into A Column Using Formulas, and in this post we’re going to explore how to do the inverse action and turn a column into a table.   Get The Completed Workbook You could do this in a number of different ways but these are the two that make the most sense given a column of data comprised of small blocks of related data like in the example. In this example every three rows of the column relate to one person. We could convert this to a table where each...

## How To Separate Data In A Cell Based On Line Breaks

Get The Workbook While entering or editing data in Excel it is possible to add line breaks in the cell using Alt + Enter. This can be a convenient way to display data in Excel, but is often not great for any type of data analysis.   For example, it might be more convenient for the reader to display an address in a cell like this: John MacDougall 123 Fake Street Any Town Canada Rather than like this: John MacDougall, 123 Fake Street, Any Town, Canada   In this post I’ll show you how to separate the data in...

## Random Sales Data

Get The Data While learning new functions or exploring new features like pivot tables or charts, it’s always good to have some data handy to use. Here’s some random sales data for 2016 to help in your learning.   What’s in the data? Sales Representative – 5 different sales representatives including 3 in the East and 2 in the West. Location – State the sale occurred in. 8 Different States in the data. Region – Region the sale occurred in, either East or West. 4 States in the East and 4 States in the West. Customer – Customer’s name on the...

## Bingo Template

Get The Template Next time your company has a social or fundraising bingo event breakout this Excel Bingo template on the projector to call out the bingo game. This bingo template features a Next button, which randomly selects the next number from the remaining numbers that haven’t been drawn yet. Numbers that have been drawn already will appear in a darker shade on the bingo card for player to see. The Clear button will reset the card for the next game, but don’t worry about accidentally pressing this during a game as it will prompt you to confirm your choice...

## How To Convert A Date Into A Calendar Quarter

Get The Completed Workbook If you work in finance and most other fields, it probably happens a lot that you need to know what calendar quarter a date falls under. Unfortunately there is no Excel function available to return this information based on a given date. Using this formula we can convert any date into a calendar quarter.   =CHOOSE(ROUNDUP(MONTH(B3)/3,0),"Q1","Q2","Q3","Q4")   The MONTH(B3) part of the formula takes the date and returns the numerical month value of the date, so for example MONTH("2014-07-15") would return a value of 7. We then use ROUNDUP(Month/3,0) to get the numerical value of...