Excel tips and tutorials
Things have been hectic this week. I got a new laptop, with Window 8, and have spent hours installing software, and getting all my settings the way I like them.
I’ve also installed the latest version of Excel 2013, and am using it for my daily work, so there’s lots of new stuff to play with and learn.
Continue reading “Add Picture to Excel Comment”
Are you still working on your budget for next year? I’ve just updated my budget template, and added a page on my website, to describe how it works.
The old version was created in 2002, so it was definitely time for an update! This is a simple budget layout, but might give you some ideas for working on your own workbook.
There is a menu sheet at the front of the workbook, with two named cells – Location and Start Date. The information that you enter in those cells is used on the other sheets in the workbook.
There are four navigation buttons, to take you to the data entry sheets and the report sheets. And those sheets have a Menu button, to bring you back to the menu sheet.
When you’re in the planning phase, you can enter your budget categories and forecasts on the Forecast sheet.
Later, when you have Actual numbers, you can enter those on the Actual worksheet.
To see how things are going, you can check the Year to Date report, which shows the Actual amounts, up to the current month, and Forecast amounts for the remaining months.
Conditional formatting colours the columns with Actual data, to it’s easy to see where it’s been entered.
The final report shows the variance between the forecast amounts and the actual amounts. Again, conditional formatting colours the columns with Actual data.
To see the details, and to download the sample file, please visit the Forecast vs Actual – Variance page on my Contextures website. The file is in Excel xlsm format, and the workbook contains macros.
__________________
Last week, I shared a tip for using a scroll bar to change the date range in a report. The scroll bar selects the end date for the report, and the columns to the left show the two previous months.
It works very nicely, without programming, and makes it easy to view a different date range.
This week, we’ll add a chart that shows the data for the selected date range. Then, we’ll change the worksheet heading to a formula, so it shows the selected dates. Finally, we’ll link the chart title to the heading cells, so it also shows the selected dates.
Oops! You’ll notice that I forgot to change Axis Title, that was added at the left side of the chart. If you use one of the Quick Layout options for charts, watch for those little extras that they might add.
You can see the steps in the video, at the end of this post.
You can see another example of linking a chart title to a worksheet cell in my post on pivot table report filters.
Instead of a simple link, you can use the IF function to affect the result, as in the formula shown below.
To see the steps for creating the chart, and adding the dynamic title, you can watch this short video tutorial.
_____________________
This week, I’ve been working on some dashboards, and want to make it easy for people to select a date range for the report.
I experimented with drop down lists and slicers, and finally settled on a good old-fashioned scroll bar. You can click or drag the scroll bar to select an end date, and see three months of sales data, and the total.
Note: The technique does NOT require programming and is fairly easy to set up.
The scroll bar on the Summary sheet is linked to a named cell on another sheet, and that number is used in an INDEX / MATCH formula, to calculate the end date.
The date headings have formulas that show the selected end date, and the two prior months.
The sales data is summarized in a pivot table, by report month, and region.
The summary table uses the GETPIVOTDATA function to pull the correct data, based on the region name and the date.
The IFERROR function returns a zero, if the data isn’t found in the pivot table.
To download the sample file, and see the written instructions, please visit my Contextures web site: Select Date with Excel Scroll Bar
__________________
What’s a quick way to combine the items in two table? For example:
How can you create a third table that has all the Table 1 items combined with each of the Table 2 items?
I’ve done this type of item combining with programming before, but this time I used Microsoft Query, to do the work for me.
Add the two tables to the query, with no join line between them, and the results show each item in table 1 connected to each item in table 2.
To see the details for setting this technique up, and refreshing the results table, please visit the Cartesian Join in Excel Using MS Query page on my Contextures website.
The instructions on that show all the steps for creating the MS query, and then sending the query results to Excel, and finally, refreshing the table if the source data changes.
_____________________
You can mock me if you want to, but every year I use my Excel Christmas planner to keep track of all the things I want to do, over the holiday season.
I make a few tweaks to the planner every year, and in this year’s version there is a column to note where you hid a present, after you’ve brought it home.
That should prevent those Christmas Eve panics, while you try to find everything. Not that I’ve ever gone through that – I’m only thinking of you! 😉
This year, the stores are even advertising Black Friday and Cyber Monday sales in Canada, so I’ll be able to use the Black Friday planning sheet, to find a few bargains.
Based on the prices that you enter, the worksheet calculates which store has the best price for each item, and which store has the most deals.
Note: If prices are the same at multiple stores, the first store will be shown in the “Best Price” column.
To download the file, you can visit the Excel Christmas Planner page on my Contextures website.
Happy Thanksgiving, and good luck finding those awesome Black Friday sales tomorrow and in the Cyber Monday sales!
____________________
Do you use the sparklines that were introduced in Excel 2010? Last week, I was building a dashboard, and wanted to show sparklines for expenses and revenue.
If you need to get a total in Excel, based on criteria, there are a few different ways that you could do it. Today, we’ll take a look at how DSUM and Excel Tables sum with multiple criteria.
Continue reading “DSUM and Excel Tables: Sum With Multiple Criteria”
It’s week two in the free online Infographics and data visualization course, led by Alberto Cairo, and I’m working on this week’s assignment.
The discussion this week is about a New York Times graphic that shows the number of times that words were used at national conventions.
Steve emailed me this week, to see if I could help with a lookup problem. He needed to find a discount rate in a lookup table, based on a product code and a date range.
Here is how I solved the problem in Excel 2010 (using fake data), and please let me know if you have a different solution.
On a separate sheet, there is a list of products and their prices. This list is formatted as a named Excel table – tblProducts. The two columns in the table are also named – ProdCodeList and ProdPriceList.
On another sheet, there is a list of the promotions that have been offered. This table is named tblPromotions, and each column is also named. We’ll use those names in the formulas.
In this table, I added an ID column at the left, and entered a unique number in each row. The existing Promo Code column has text entries, and for my solution I needed a numeric ID in each row.
On the Orders sheet, a record is created for each new order, with the order date, product code and quantity entered. This table is named tblOrders.
NOTE: Because the formulas are being created in a table, you’ll see column references, like [@Qty], instead of cell references.
You can use an INDEX / MATCH formula to get the product price, based on the product code:
=INDEX(ProdPriceList,MATCH([@[Prod Code]],ProdCodeList,0))
For the subtotal, multiply the product price by the quantity
=[@Qty]*[@[Unit Price]]
The next step is to check the promotions table, to see if there was a promotion for the selected product, when the order was placed.
To do that, I used a SUMIFS formula (NOTE: only available in Excel 2007 and later versions):
=SUMIFS(PromoIDList, PromoStartList,"<=" & [@[Order Date]], PromoEndList,">=" & [@[Order Date]], PromoProdList,[@[Prod Code]])
This formula returns a number from the PromoID column, if a promotion is found that matches the criteria:
If no matching promotion is found, the sum will be zero. Otherwise, the Promo ID will be the sum.
NOTE: This solution depends on there not being any overlaps in dates for a product promotion. Each promotion ends before another one for the same product begins.
Once the Promo ID has been found, the Promo Code can be looked up, using INDEX and MATCH, based on the Promo ID.
=IFERROR(INDEX(PromoCodeList, MATCH([@[Promo ID]],PromoIDList,0)),"N/A")
This formula returns a promo code, if a promotion is found that matches the promo ID. If there is no match, the result is “N/A”.
The same type of INDEX and MATCH formula is used to find the discount rate, based on the Promo ID.
=IFERROR(INDEX(PromoDiscList, MATCH([@[Promo ID]],PromoIDList,0)),0)
This formula returns a discount, if a promotion is found that matches the promo ID. If there is no match, the result is 0.
Finally, to calculate the total amount, we multiply the subtotal by 100% minus the discount rate.
=[@Subtotal]*(1-[@Disc])
To download the sample file, please visit my Contextures website: Excel Sample Files – Functions Section.
Look for FN0023 – Product Code Lookup in Date Range
_____________________
