My Top 3 Picks of Most-Used Excel Features
Excel is my default analytical tool of choice. It’s the one I’m most comfortable with and can move the fastest in.
A big reason I can move so quickly when transforming or analyzing data in Excel is because of tips and tricks I’ve picked up along the way and now incorporate regularly into my workflow.
Here are 3 Excel features that I use to save time in almost every spreadsheet or analysis I create:
Pick #1: Text to Columns Wizard
Have you ever experienced this setback:
You’ve just downloaded a CSV file that has a column containing date information but, for some reason, Excel isn’t recognizing the dates. You’ve tried changing the cell format to reflect a date type but nothing changes.
Ugh… this is a common problem that used to trip me up, too.
So, what’s going on here? Often in this scenario, the data we see visually as DATE information is being recognized as text by Excel which means it cannot be organized or leveraged as DATES (therefore it can’t be sorted or used for calculations). This is where the Convert Text to Columns Wizard can come to the rescue.
Once you initiate the Wizard function, it takes you through a three (3) step process to help you define and parse data.
In step 3, there is a built-in option that makes it seamless to tell Excel that the data being interpreted as text is actually dates. Once you select the Date button, you have the option to identify the order it is organized in (YMD, DMY, etc). Then you hit the Finish button and your data is converted and can now be leveraged as DATES!
And once you get comfortable using the Convert Text to Columns wizard, you begin to find so many other applications for it…
Need to strip out country code from a column of phone numbers? No problem. Have a unique identifier that you want to break into individual segments (xxx-xxxx-xx)? This wizard quickly and simply automates this, too!
Here’s a link to Microsoft support where you can learn more about the Convert Text to Columns Wizard: HERE
Pick #2: Format Painter
Design or aesthetics have become an integral part of my data analysis workflow.
As hard as I try not to, I am definitely one of those people who gets sidelined by formatting issues like mismatched fonts, misaligned text, or unappealing colors. In the past, this has meant spending a significant amount of time trying to keep formatting consistent as I’m working through my analysis build.
Then I learned about the Format Painter tool.
This tool allows the format from one cell or element to be copied and then applied to another area. So instead of having to repeat multiple formatting steps like selecting font type, size, color, alignment etc, I can set one cell exactly the way I’d like it and then paste that to any other area.
This tool condenses all the steps that can come with formatting into two simple clicks, allowing me to move through a spreadsheet much quicker.
I’ve also found this can be used in charts and graphs where I want to apply consistent text formatting across multiple visuals in a report or dashboard.
For example, once I set the format for the text of an axis or graph title, I can copy and apply it across every visual in my dashboard saving me lots of time.
Here’s a link to Microsoft support where you can learn more about using the Format Painter: HERE
Pick #3: Conditional Formatting
I hesitated initially to include this one on my top 3 because I know from experience that working with Conditional Formatting in Excel can be tricky.
Unlike Picks 1 & 2, conditional formatting takes significantly more effort to set up and maintain, especially when you start to leverage more complex rules.
However, I’ve ultimately decided to include it on my Top 3 because the advantage I get from using it seems to outweigh the work it takes to set up… and I keep going back to it.
As I mentioned in Pick #2 above, I’ve learned that visual elements are really important to me when I’m exploring and analyzing data. So, when I first learned about conditional formatting, it was a quick and dynamic way to create heatmaps that would highlight patterns and trends in aggregated data.
But more recently, I’ve found myself leveraging conditional formatting to explore and group information in raw data tables.
My process usually begins with creating a helper column that I use to populate a new feature or category. Sometimes I begin by manually entering a category for each row of the raw data, other times I create formulas that leverage other fields to help categorize the data.
But when the initial pass at categorization is done, I find it useful to be able to go back and visualize how the new categories I’ve created are distributed and that’s where conditional formatting comes in. I often turn to the “Use a formula to determine which cells to format” rule type, which allows me to conditionally format an entire row based on the new category I created.
Once these conditional formatting rules are applied, I have a visual indicator in my raw data table of how the new categorization I’ve created is distributed. I find this immensely helpful and drives a better understanding of the correct level of granularity needed for my newly created categories.
Here’s a link to Microsoft support where you can learn more about using Conditional Formatting and its almost infinite uses: HERE
Wrapping Up
As I’ve grown my skill and confidence working in Excel, the biggest takeaway for me has been to BE CURIOUS.
If there’s something I want to do or wish I could do faster or easier, my reaction now is to get curious and begin looking for a better way.
I search, I read, I ask a mentor, I take a course.
All these sources have helped me and guided me along the journey to develop my Excel skills. In fact, I would say it’s now key to my overall confidence in Excel… even if I can’t immediately identify how to tackle a new problem, I know HOW to go find the answer.
I hope my Top 3 here will help YOU move faster in Excel and help build your confidence, too!
Interested in learning more about Excel from Stacy and the Maven pros?
Master Excel in just 10 weeks here: https://bit.ly/3SdcAxd
Admissions for our immersive programs are now open!!
Key Deadlines:
Super Early Bird — Save 25%: Friday, January 19th
Early Bird — Save 10%: Friday, February 2nd
Admissions Close: Wednesday, February 7th
Here’s what you can expect from each immersive…
✔️ Guided portfolio projects!
✔️ Access to your own private learning community!
✔️ Weekly, 1-hour live sessions with recordings!
✔️ Live support from expert instructors!
✔️ Unlimited Maven access extending two months past the end of the program!
…and all of this is built into a program that’s designed to fit into your busy life, at a more affordable price.
Space is limited; secure your seat today!
