There’s always something new and interesting to learn in Excel. Here are some of the articles that I’ve read recently, and links to new and updated Excel resources.
Excel Price Lookup Date Product Name
If there’s just one price per product in an Excel lookup table, you could use the INDEX and MATCH functions to to get that price. But what if the price changes occasionally, and your pricing list has multiple dates and prices for each product? How can you do a product price lookup based on invoice date and product name?
7 Ways to Round in Excel
It’s not usually as obvious as the error in the screen shot below, but have you ever seen Excel adding things up incorrectly? No wonder some people take out a calculator, to check the totals! Your trusty old calculator wouldn’t tell you that 2+2=5.
SUMIFS Formula With Empty Criteria Cells
I recently learned about The 100-Day Project (#The100DayProject), in which you work on making something every day, for 100 days. Would Excel formulas (#100DaysOfXL) be considered an artistic project? Maybe not, but I had fun working on this SUMIFS formula with empty criteria cells!
Pivot Table Fill Colour Disappears
Microsoft just announced the winner of their Excel World Champ data visualization contest. Congratulations to Ghazanfar Abidi, from Canada! I found his website today, and learned something new from his latest blog post – depending on how you apply it, pivot table fill colour might disappear!
Combine VLOOKUP and MATCH for Flexible Formula
With the Excel VLOOKUP function, you can pull data from a specific column in a lookup table. For example, if the lookup table has Product ID, Product Name and Price, you’d use column 3 to get the price. To make your formulas more flexible, and to prevent problems, you can combine VLOOKUP and MATCH.
Continue reading “Combine VLOOKUP and MATCH for Flexible Formula”
Take the Excel Name Fix Challenge
Last week, in my Contextures Newsletter, I posted an Excel Name Fix challenge. There was a short list of names, which needed to be fixed. Then we needed to count how many were fixed. Take the challenge yourself – download the workbook to see how you’d solve it.
Freeze All Worksheets Macro
If you’re working with a large worksheet in Excel, it usually helps if you freeze the cells at the top and/or the left side of the sheet. That way, your headings are always visible, along with other key information that you’ve put at the top of the sheet. You can freeze each sheet individually, or use this macro to freeze all worksheets at once.
Fix Excel Conditional Formatting Duplicate Rules
Conditional formatting is a great way to highlight specific data, but did you know that it can automatically create new rules on its own? I’ll show you how that happens, and an easy way to fix those conditional formatting duplicated rules.
Continue reading “Fix Excel Conditional Formatting Duplicate Rules”
Excel RANK IF Formula Example
There is a Microsoft Excel RANK function, so you can calculate where each number stands in a list of numbers. There isn’t a RANKIF function though, if you need to rank based on criteria. Someone asked me for help with ranking daily sales, so I used COUNTIFS to make an Excel Rank If formula workaround.