In partnership with
Hey everyone — it’s John from On The Pulse.
How are you all doing this week?
In this edition of On The Pulse, we’ll show you 3 must-learn tips for cleaning up data like a pro in Microsoft Excel!
This week’s tips include:
1️⃣ ✨ Transform Your Data With TOCOL
2️⃣ ✨ Use the TRIM Function to Clean Up Text
3️⃣ ✨ Use the UNIQUE Function to Identify Distinct Values
Let’s Go!👇👇👇
Also — I’d love to hear what you think of On The Pulse. Whether you love it or hate it, or there’s something new you’d like to see included, hit “reply” and share your thoughts!
👋 Video Requests
What video content would you like to see? Leave us a comment on our YouTube community page and we’ll give you a shout-out if we select your video suggestion!
Beat Black Friday with BILL
Get the deal of the year for you and your business when you choose the BILL Divvy Card + expense management software, AND an exclusive gift when you take a demo. Move over, Black Friday.
Choose BILL Spend & Expense to help your business:
Reap rewards with reliable cash back rates
Create virtual cards that help protect from fraud & overspending
Control spending with customizable budget controls
Take a demo by the end of the month and take home a Nintendo Switch, Apple AirPods Pro, Samsung 50" TV, or Xbox Series S—your choice1.
1Terms and Conditions apply. See offer page for more details.
BILL Divvy Card is issued by Cross River Bank, Member FDIC, and is not a deposit product.
✨ PRO TIPS FOR EXCEL IN 2024
✨ Tip 1: Transform Your Data With TOCOL
The TOCOL function in Excel allows you to easily convert a range of data from multiple rows and columns into a single column. This is invaluable when preparing your dataset for analysis, especially when using tools like Pivot Tables.
The syntax:
TOCOL(range, [ignore], [by_row])
Range: The range of cells you want to convert (e.g., A1:C4).
Ignore: An optional parameter to skip certain values.
Enter 0 to ignore blanks.
Enter 1 to ignore zeros.
Enter 2 to ignore text.
Enter 3 to ignore logical values (TRUE/FALSE).
Enter 4 to ignore errors (e.g., #N/A, #VALUE!, etc).
Enter 5 to ignore both errors and blanks.
By_row: An optional parameter (TRUE or FALSE) indicating whether you want to read data column by column (FALSE) or row by row (TRUE). The default is column by column.
TOCOL Example:
Imagine you have a data range A1:C4 :
Apply the Function:
Enter
=TOCOL(A1:C4)into an empty cell.
Result:
We entered
=TOCOL(A1:C4)into cell D1 and Excel transformed the range into a single column.
Bonus Tip
To SUM the values we’ve just extracted, click into the first empty cell under the new column and press Alt and = on your keyboard. Or if you’re using a Mac, press Option and = on your keyboard.
✨ Tip 2: Use the TRIM Function to Clean Up Text
When working with text data, extra spaces can create significant issues during analysis. The TRIM function removes unnecessary leading, trailing, and double spaces from text entries.
The syntax:
TRIM(cell/range)
Remove any spaces before the first character in the text.
Remove any spaces after the last character in the text.
Replace multiple spaces between words with a single space.
TRIM Example:
Imagine a table containing text with unwanted spaces.
To trim the spaces from cells A1 to C4, enter =TRIM(A1:A4) into an empty cell and hit Enter.
Result:
Bonus Tip
Combine cells using the TRIM function; for example: =TRIM(A1 & “ “ & A2)
✨ Tip 3: Use the UNIQUE Function to Identify Distinct Values
When analyzing datasets, duplicates can lead to misleading insights. The UNIQUE function helps you easily extract distinct values from a list, allowing for cleaner analysis and better decision-making.
The syntax:
UNIQUE(range)
Range: The range of cells or array from which you want to extract unique values (e.g., A1:A4).
UNIQUE Function Features:
Identifies Distinct Values: Automatically filters out duplicates from the specified range, giving you a list of unique entries.
Dynamic Updates: If the source data changes, the unique list updates automatically, ensuring you always have the latest distinct values.
Works with Multiple Columns: Can extract unique combinations from multiple columns, making it versatile for complex datasets.
UNIQUE Example:
Imagine a table containing duplicate names.
To get a list of unique names from cells A2 to A7, enter the following formula into an empty cell and hit Enter:
=UNIQUE(A2:A7)
Result:
Bonus Tip
Combine the UNIQUE function with other functions for deeper insights. For example, to get a count of unique values, use:
=COUNTA(UNIQUE(A1:A6))
✅ WRAPPING UP!
That’s all for this edition of On The Pulse.
Thanks for reading and see you soon! 👋