Monday, April 17, 2023

Create Actual vs Budget Line Chart with Label


This chart is a great way to visualize something like how your actual budget lines up with your projected budget. You need a visual and one of the best ones is the humble line chart. You see a line chart almost every day if you follow financial news, so you know most businesspeople have seen it. Creating the line chart with labels is a great tool for tracking your budget and ensuring that you stay on track.

Monday, April 10, 2023

Highlight High and Low Sales on Bar Chart


A Bar Chart can be the most common type of chart you show. Why not spice things up by leading your audience to certain parts of the chart. If you’re charting sales or productivity numbers, you may want to focus on the high numbers as well as the low numbers.
The highest and lowest sales can be highlighted on a bar chart by using different colors for the bars. For example, the highest sales can be represented by a green bar, while the lowest sales can be represented by a orangish bar. This will help to make the data more visually appealing and easier to understand.

Monday, April 3, 2023

Find Errors in Your Excel Formulas


You’ve written formulas, thought it was all set and hit the Enter key and BAM! Excel gives you an error. Short formulas are easy to troubleshoot but long ones, especially when they reference multiple cells are a sometimes a pain to figure out. You could go through the formula look at this cell and that cell, clicking through it but there’s some tools in Excel that will help make it easier and keep you from smashing your head when you don’t know where that #Value error is coming from. Now, these tools show up in the most appropriate Ribbon tab called Formula. It’s the Evaluate formula and error checking commands and they will make your excel life easier.

Monday, March 27, 2023

Create Risk Assessment Chart - Google Sheets


Do you like risks? Most people would say No...as long as it’s a calculated risk. But do you know what is risky? Not identifying risks and putting some rating on them. This is especially true if you’re in an organization and project managing activities. Don’t be that guy or gal that does that. Let your people know what kind of risks may happen to your projects. What’s better is if you can categorize them too. Like with Low, Medium, and High. What you need is a risk assessment matrix and a nice free tool to create this is Google Sheets. I mentioned putting the Formula so here it is with the table row data starting in Row 9 and column C & D with respectively Priority and Impact headers: =IF(OR(AND(C9 = "Low", D9 = "Low"), AND(D9 = "Medium", C9 = "Low"), AND(C9 = "Medium", D9 = "Low")), "Low", IF(OR(AND(C9 = "Medium", D9 = "Medium"), AND(D9 = "High", C9 = "Low"), AND(C9 = "High", D9 = "Low")), "Medium", IF(OR(AND(C9 = "High", D9 = "High"), AND(D9 = "High", C9 = "Medium"), AND(C9 = "High", D9 = "Medium")), "High")))

Monday, March 20, 2023

Create a Dynamic Calendar in Excel


Maybe you’re putting together a dashboard and want to have a one-month view for your audience. Also, you want to give them the option of entering some date and the calendar dynamically shows that month or you’re throwing a party and you want to know when to plan it. You just like to have dates show up in a grid format just like how you’d see a printed calendar with the days of the week and what date falls on one of the most important days of the week Friday or Saturday. In this video I’ll show you how to create that dynamic calendar you always wanted. Also, you may also want to fool proof it by making sure whatever gets entered in the input cell is a date and not some text. I’ll show you how to do that with the data validation function near the end of the video. There are some neat things you can do with the SEQUENCE function, which is a dynamic array function available in Microsoft 365. I think it’s also available in Excel 2021 but check the web to make sure. If you’re into adding something that could wow your audience or boss, or you just like to make cool things in Excel, try creating this dynamic calendar on your own!

Monday, March 13, 2023

Why You Should Use Excel Advanced Filter For This


Do you have complexed filtering to do on your table data? Is your boss looking over your shoulder while giving your instructions on let’s filter for this and that and include these two, three, or four other things? You’re probably thinking how am I going to do this? Put it in a Pivot Table? Write a VBA script? Hold on...there’s already something in Excel that can do this advanced filtering. It’s even been around since the late 2000s...and it’s appropriately called.... Advanced Filter. You’d be surprised what type of complex filtering can be done with a feature that’s been around since Excel 2007, but it’s there. Once you realize how useful it is, it’ll be your secret tool for complex filters. Even though Microsoft wows us with new filtering features, like the dynamic array function FILTER just know that you can still do some pretty cool stuff with the good old advance filter feature. See FILTER function at https://www.youtube.com/watch?v=pjr5cljnvxU

Monday, March 6, 2023

Separate Text and Numbers in Excel


When you’re cleaning up some data, maybe you get something where the number and text are combined and need to be separated or split into columns. It could be something as easy as when a fixed set of numbers come after text or a little more complicated where some set of variable numbers come first. Or ...it could be something as hairy as when a bunch of numbers and text are joined together in multiple random lengths. Do you have to write complex formulas or VBA to clean this up? No way...it can be done with a few keystrokes and maybe even just one hand. I’ll show examples of how we could separate the number and text in native Excel and then with Power Query.