Excel tips and tutorials
Excel doesn’t have a MAXIF function, but we’re able to create our own version, by combining the MAX and IF functions. But how about a bigger dream – MAX IF with multiple criteria? Could we create our own MAXIFS function too, with multiple IFs?
There’s always something new to learn about Excel, and it’s good to be reminded of a few old tricks.
For example, I’m sure you know that you can’t make a holiday meal without a spreadsheet, as this video shows. The spreadsheet section starts at the 1:57 mark, and continues a little later.
Here’s what I posted last week:
Here are a few of the Excel articles that I read last week, that you might find useful:
If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.
Please include a brief description, and a link to the article.
_________________
In a previous article, we combined the MAX and IF functions, to find the highest price for a specific product. Today we’ll use that technique to find the latest date that a product’s price was changed. Then, using the product name and that date, we can find the latest price for specific product – even if it isn’t the highest price.
Last week, I opened an Excel file, and was surprised to see a Security Warning. Apparently there were external links in the file, and I didn’t want to keep them. Here’s how you can find external links in an Excel file.
Here are a few of the Excel articles that I read last week, that you might find useful:
Microsoft has improved the Office Web Apps, and you can read the details on the Excel Team’s blog. The feature that looks most interesting to me is the real-time collaboration in Excel files — you can’t do that in the desktop version.
If you use the MOD operator in Excel VBA, you could run into problems with large numbers. Chip Pearson suggests some alternative formulas and code that you can use.
Dick Kusleika ran into some problems while running code on a pivot table that has calculated items. Lots of problems are caused by pivot table calculated items, in my experience!
If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.
Please include a brief description, and a link to the article.
________________________
This week, I was experimenting with sending email from Excel via Outlook. The goal was to send an email to each name in a list, and attach a couple of PDF files. Here’s how I managed to send email with PDF attachment from Excel.
Continue reading “Send Email With PDF Attachment from Excel”
With an Excel line chart, you can show the sales results from a date range, to see how things have gone. For example, in the chart shown below, you can see the sales quantities for the first six months of the year. To add context to those numbers, you can create an Excel line graph with target range.
Continue reading “Create an Excel Line Chart with Target Range”
Happy Halloween! It’s the day for tricks and treats, so here is a useful trick that you can use with your drop down lists.
We’ll block changes in Excel drop down list cells, and that could prevent your worksheet’s data from turning into a nightmare!
Today, we’ll look at a time-saving Table trick that you might not know about. See how to print Excel table only, without print area setup.
Continue reading “Print Excel Table Only Without Print Area”
If you have a list on your Excel worksheet, and a date in each row, you can use a formula to count items in a date range. For example, how many Pencil shipments went out in September, based on the Ship Date column?
