Excel hacks to transform your workflow

In credit management, your day-to-day work varies. One day you might spend hours determining the best terms for a customer with a rocky credit history while on others you might be settling a mounting pile of disputes. No matter what the day brings you, data is likely playing an important role in how you make those tough credit decisions.  

Why it matters: Data is important when working in credit, so it is critical that credit managers are well-versed in how to work efficiently and effectively in Excel. While it can be tricky getting comfortable with the program, taking the time to figure out just how much you can do using Excel can transform your daily work and ease your workload.  

“As credit managers, we are not just looking at spreadsheets; we are managing relationships and navigating the daily dynamics of risk,” said Sheri Tingle, speaker with PowerNow Training (Nashville, TN). “In my experience, being a credit manager is never a passive job. We are constantly looking for warning signs, and Excel is a great tool to stay on top of potential hotspots before they escalate.” 

Keeping a dataset organized helps keep the information clear, which is critical when there are multiple eyes reviewing the sheet. “When you are starting a new dataset, a little structure saves hours of cleanup later,” Tingle said. “Building a clean foundation ensures your reports, formulas and analysis stay accurate as the data grows.” 

The first step when you are starting a dataset is to choose the key pieces of information you want to include and create columns that highlight that data. “Keep one piece of information per column. For example, split ‘Full Name’ into ‘First Name’, and ‘Last Name,’ and arrange columns in a logical order,” Tingle said. “A clear plan up front keeps your dataset clean and analysis ready.” 

An organized Excel sheet takes continued effort, with small inconsistencies having the potential to skew calculations in your data. There are a few simple tricks that can keep datasets organized without taking up too much time. 

• Remove duplicates: “Nothing creates team turmoil in a report like seeing the same customer listed three separate ways with three different balances,” Tingle said. “I always go to the Data tab and click Remove Duplicates. It is the fastest way to ensure that everything is clean and totals are accurate.” 
• Keep an eye out for small errors: When working in large datasets, it’s easy to miss small mistakes in the numbers until your formulas begin rendering strange results. “Sometimes, data has hidden spaces that can make your formulas fail,” Tingle said. “I use the TRIM function. It cleans up the stuff you did not know was there.” 
• Convert data into an Excel Table: You can transform your dataset into a Table with the Ctrl + T shortcut. “Converting your range into a Table gives you automatic formatting, built-in filters and structured references that expand as your data grows,” Tingle said. “It is one of the easiest ways to keep a dataset organized and reliable.” 

An organized chart is a good first step, but it is also important to consider how you may want to present this data. Upon first glance, a dataset can look like a wall of numbers. Diving deeper, a credit manager’s trained eye can dig out all the hidden meanings lurking in these figures. But when sharing or presenting your dataset with other departments, it can be helpful to convert that information to charts.  

“I love using Dynamic Charts to provide a clear roadmap,” Tingle said. “When you show a visual instead of a data dump, you ensure the whole team is seeing the same reality. Do not forget to use Pivot Charts to organize the rows and rows of data.”  

Working efficiently in Excel means understanding how to use formulas within your datasets to calculate valuable metrics. There are a few tricks credit managers can employ to ensure their formulas are clear and effective. 

• Simplify your columns: It is easy to create one giant formula to yield the metric you are looking for, but it can be confusing and susceptible to small mistakes. “If a calculation is multifaceted, break it into two or three smaller columns,” Tingle said. “It is much easier to find an error in a small formula than in one that stretches across the whole screen.” 
• Create connections with IF Statements: Creating rules using the IF function can help translate number values, allowing even a quick scan of the dataset to communicate the important points of the data. “You can write a formula that says if a balance is over 90 days, display the word ‘URGENT,’” Tingle said. “This helps managers and team members recognize the warning signs of potential conflict hotspots immediately, without having to do the math in their heads.” 
• Don’t be afraid to leave comments: With several team members referencing the same Excel sheet, you might be asked to explain your formulas and the importance of the metric they yield. A quick comment on a cell can create more clarity within your dataset.  

When working within Excel, don’t shy away from exploring other technology to create new shortcuts and efficiencies. “Artificial intelligence (AI) is a great tool to leverage Excel’s horsepower,” said Sam Mickelson, CBF, credit manager at Multivac (Kansas City, MO). “For example, when you are trying to figure out how to answer a specific question you have about a dataset, you can ask AI for guidance on how to solve it or otherwise find more efficient ways to use Excel.”  

The bottom line: Creating efficiencies within your dataset while keeping the information concise and organized can help communicate data effectively. Accurate data is invaluable in the credit department. Taking the time to ensure that your dataset is precise and clear will allow the information to speak for itself without getting lost in a haze of calculations.  

For more Excel tips and tricks, attend Tingle’s session, “Excel Boost—Tools That Transform,” at this year’s Credit Congress and Expo in St. Louis. Register here.