Before I get started, I had a reader mention that in versions 2007 and greater you can use Excel Tables to build dynamic data validation list which is definitely another great way of handling a validation list in Excel. (more…)
Over the next couple of weeks, I will be building a dynamic salesman dashboard that allows the user to interact with the spreadsheet to get the information that they need. This post will first focus on building a data validation list that will immediately make the spreadsheet interactive. (more…)
Now that the holiday season is over (almost 2 weeks ago now) I’ve finally got myself back into a routine and I wanted to start off the year with a quick tip for those of you looking on how to build a list of weekday dates for your planning purposes and to get the year started off right. This simple trick can be used to build a quick calendar to record daily sales, production numbers, number of orders shipped etc.
You’ll notice that when using the auto fill option located in the bottom right hand corner of the current cell that you’ve highlighted, that as you drag the mouse over other cells, the dates automatically populate in chronological order. The only problem here is that you only want to include weekdays and not all days of the week. This post will show you how to easily and quickly only show the weekdays in the calendar that you’re building. (more…)
Getting the answers to your most important business questions is imperative for achieving success. Being able to find the answers to your questions can quickly give you insight into what business processes and procedures need to be fixed in order for you to lower your costs and maximize your returns. In this post we look at using Excel Pivot Tables versus the SumProduct formula to calculate which month had the highest production scrap within a given year. Being able to get this information can allow management to dig deeper into why a given month had such a high volume of scrap and help address issues that may have caused the spike. (more…)
I use pivot tables everyday to answer pretty much any of the questions my colleagues may have about any type of information or data. The beautiful thing about pivot tables is that they allow you to quickly and easily summarize large sets of data where you will have grouped information for certain fields. (more…)