Excel Roundup 20151019

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.

Excel Roundup http://blog.contextures.com/

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.

twitter20151016b

________________

Excel Roundup http://blog.contextures.com/

You may also like...

3 Responses

  1. Doug Glancy says:

    Debra, thanks as always for the pointers to interesting reads. I especially like John Patrick Pullen’s folksy tips. And thanks for including my humble contribution.

  2. NARESH says:

    in vba i want to copy the data as per week wise and paste in text file and also trigger the time. can you help me out to complete the task to make this senario in VBA

Leave a Reply

Your email address will not be published. Required fields are marked *