For the past week or two, I have been working on building an online course through Udemy and it has been consuming most of my free time so I haven’t had a whole lot of opportunity to post anything here on my blog. That being said, I’ve been looking around for ways to generate random logical sample business data to help build up data sets and samples for my course. As a result, one thing that I’ve started to do is use the Index Randbetween CountA formula to pull data from one table located on a separate tab/worksheet that may hold a smaller set of data that will be required to build a full sample data set. (more…)
This is a guest post by CPA David Ringstrom from Accounting Advisors, Inc. David covers some functionality from Excel tables in 60 seconds. His video shows you how to expand tables, add total rows, autofill data and shows us how the filter option becomes immediately available.
If you are encountering any issues with not being able to see the video directly from the website (videos don’t seem to render with the new IOS7), you can access the video here – Excel in 60 Seconds.
I want to send out a big thanks to David for taking the time to put something together!
Excel Pivot tables can be a pretty handy tool for summarizing and making sense of your data. In this example, I’ve built a small table to show you how you can use pivot tables to calculate value differences between different dates. This can come in handy especially when capturing and reviewing day over day, month over month or year over year changes. In the case below, I’ve shown how you can monitor daily changes in your inventory valuation levels to capture changes in stock levels or value. You can then use this information to do further investigation from a transactional level to determine why your stock levels may have changed in that day (i.e. cycle counts, purchase order receipts, work order completions, customer RMAs/returns etc.). (more…)
A How To Video Tutorial For Using Formulas in Excel Tables
The idea to provide a quick tutorial on how to use summary formulas within Excel Tables came from Joe Stephens. Joe commented the other day on my blog mentioning that you could use the built in header column/fields names within an Excel table to create summary formulas. That being said I thought it would be a great idea to give a quick video tutorial on the subject. As Joe mentioned, this functionality is only available in Excel 2007 or later. Please enjoy the video and feel free to leave a comment with any suggestions or questions! (more…)
Build a Quick Set of Name Ranges for your Fields Using This Excel Shortcut
Let’s keep this short and sweet and under 60 seconds.
Type Ctrl+A to highlight your range of cells/table.
Once all cells including the column headers are highlighted, type Ctrl+Shift+F3. This will bring up the following text box. Because typically business intelligence software dumps the data into column format, you are going to want to select “Top Row” here which is where your field names are located (if they were on the left, bottom or right, you would select the option which you would want to name the range for). (more…)
