In this week’s roundup, delete sheets, hide passwords, use Boolean logic, and many more tips. I hope you’ve recovered from your Spreadsheet Day celebrations, and are ready to start a new week.
1. Count Items Based on Another Column
Even though the SUMIF function lets you check one column, then sum in a different column, the COUNTIF function restricts you to one column. However, if you want to count items in one column, based on entries in another column, you can use COUNTIFS or SUMPRODUCT.
2. Excel Tips for Spreadsheet Day
It was Spreadsheet Day on Saturday (Oct. 17th), and even though the day “is all about family”, John Patrick Pullen posted 5 quick Excel tips, in honour of the occasion. You can continue to use them this week, even though the celebrations are over for this year.
3. Delete All Unselected Sheets
Doug Glancy shares his VBA code for quickly deleting all the unselected sheets in a workbook. He uses this trick before sending a file to someone else, and the macro has a confirmation message, asking if you really want to delete them. I’m even more of a chicken than Doug is, so I’d probably add a step that makes a backup copy first.
4. Group By Times
If you have a table with time-stamped items, Jon Acampora shows 3 ways that you can groups those items into time blocks. He uses a pivot table, the FLOOR function and VLOOKUP.
5. Hide a Password
If someone has to enter a password on an Excel worksheet, you can use a mask to hide the characters that they are entering. Chris Newman shows the simple steps for adding that feature.
6. Boolean Logic in Excel
On the PC World blog, JD Sartain takes an in-depth look at Excel’s logical functions — AND, OR, NOT, and a new function called XOR. She starts with simple definitions, and then shows several examples of how to use the functions.
7. Free Dashboard Webinars
Mynda Treacy is offering free one-hour dashboard webinars this week.
- How to Build Excel Dashboards
- Dashboards with Power Query and Power Pivot.
Get the details, and sign up for a date and time that is convenient for you.
8. Excel Humour
Finally, for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets. Here’s one of my favourite tweets from this week’s collection.
________________
