Excel tips and tutorials
Christmas is only 2-1/2 weeks away, so it’s time to start planning! Of course you’ll want to organize everything in a spreadsheet, so you can download the free Excel Holiday Planner, from my Contextures website.
It has shopping lists, budget sheets, task lists, and even a dinner planner, so get your copy, and get the holidays under control.
If you’ve got a long list of items, it can take a while to find what you’re looking for, in a data validation drop down list.
For example, in the screen shot below, you’d have to scroll down to the bottom of this long list, if you want to select the Teatime Chocolate Biscuits (and don’t pick the Tofu by mistake!)
Continue reading “Show Drop Down List With Specific Letters”
Happy Cyber Monday! I hope you didn’t spend all you money at the Black Friday sales, and have a little left over for today’s shopping spree.
To celebrate Cyber Monday, the busiest online shopping day of the year in the USA, you can buy a couple of my Excel products, at 50% off today only. The details, and links to the product pages, are below.
NOTE: Unfortunately, the shopping cart will only discount one item per order — if you want both items, please order them separately.
Wow! It’s December already – how did that happen? It’s Cyber Monday too – a big online shopping day. Did you find any Excel bargains?
To start the countdown to Christmas, Mynda Treacy created an Advent Calendar in Excel. You can download the file, if you’d like a bit of fun while you count the days until Christmas
You’ll have to answer a quiz each day, to open the calendar doors. To get a head start, you can start thinking about the answer to the December 5th question, shown in the screen shot below.
Happy Black Friday! If you celebrated Thanksgiving yesterday, I hope you enjoyed time with your friends and family, and are ready to do some shopping today.
To celebrate Black Friday, the busiest shopping day of the year in the USA, you can buy my popular ebook kit – Excel UserForms for Data Entry – at 50% off today. So, instead of the regular price of $30 US, you can get the kit for only $15 US. The price will be reduced automatically – you don’t need to enter a coupon code.
[Update: This product is no longer available]
The special price ends tonight (Nov. 28, 2014) at midnight, Toronto time (Eastern time zone).
Click here to see the details, and grab a copy of this awesome kit.
Happy Thanksgiving, if you’re celebrating today. Tomorrow is Black Friday – the biggest shopping day of the year, in the USA. Up here in Canada, we celebrate the day too, so I’ll put one of my Excel products on sale for the day. Remember to check this blog tomorrow, to get the coupon code.
If you’re doing your shopping around the world, you might end up with a worksheet filled with amounts in a different currency. Let’s see how Excel can help with that problem!
Continue reading “Convert Currency With Different Separators”
In a short video, Sara Silverstein shows 6 keyboard shortcuts for formatting numbers in Excel. Do you use any of these?
You can see the shortcuts in the video below (no longer available), or on the Business Inside website.
Last week, I was working on a client’s file, and we wanted to get any new information from an update file, and paste it into the current record.
Both files were set up with the same columns, but the update file only had a few of the fields filled in. We could have used a macro to loop through the fields, grab any new information, and paste it to the current record. However, that would be a slow way to update the records, especially in a large file.
In this Garage Series episode, Power BI and Excel go to Spain, to expose data patterns in past matches between Real Madrid and FC Barcelona.
“By combining historical El Clásico and player data, outside forces like temperature, humidity and moon phases, Jeremy tries to predict the outcomes and puts these predictions to the test among fans in Barcelona.”
They didn’t use good hair as one of the factors, but they predict 2-1 for Madrid, in the Oct. 25th match. (The final score was 3-1 for Madrid.)
You can watch the video below, or on YouTube: Power BI takes on El Clásico
If you’re a pivot table fan, like I am, you know how quick and easy it is to summarize a massive amount of data, with just a few clicks. You can show sums, counts, averages, and other totals, without using any fancy formulas.
In the screen shot below, the pivot table is summarizing income and expenses, and there is a Slicer at the top left, for quick filtering.
As wonderful as pivot tables are, they do have some limitations, and you might not be able to get the layout exactly the way you need it. In the screen shot below, you can see a P & L statement, based on the same data as the previous pivot table.
You’d never be able to get the pivot table in exactly this layout, with its blank rows, and formatting, and there are additional formulas at the right side too.
Roger Govier is a pivot table fan too, and he has created a solution for building his own custom reports, like the P & L statement shown above. Roger creates a pivot table first, and then he uses the GetPivotData function, to pull specific data into his custom layout.
In the formulas, Roger uses cell references to the row and column headings, so he just has to create one GetPivotData formula, then copy it into all the data cells of the custom layout.
Another smart trick is that Roger adds headings at the top of the sheet too, and refers to those cells, instead of hard coding the field names into the formulas.
If you don’t want to use the GetPivotData function, Roger also show how you can create named ranges, based on the pivot table. Then, use the INDEX and MATCH functions to extract the applicable data, and build the custom report.
He even has sample code that you can use, to automate building the named ranges.
To see the detailed instructions, and download Roger’s sample file (with or without the VBA code), please visit the Build Custom Reports With GetPivotData page, on my Contextures website.
________________
