June 2016 Edition Of The How To Excel At Excel Newsletter

View this email online if it doesn't display correctly
Don't Forget To add barbara@howtoexcelatexcel.com to your safe or contacts list to ensure you always receive your 3 FREE Excel Tips. June 1 2016 Hello How Welcome to the June 2016 edition of the How To Excel At Excel Newsletter.  I hope you like the new monthly scheduling.  I am excited to share three more Excel tips with you.  If you have any tips or Excel questions then email me and I can include them in upcoming newsletters. You can download the examples used in this newsletter here and follow along with the tips. Below are other places you can find and follow me for more Excel Tips on Social Media or download the FREE How To Excel At Excel App (IOS only) from the App Store for updates directly to your iPad or iPhone. Your 3 Excel Tips Are- 1. How To Remove All Cells Containing  Specified Words/s Or Values. 2. Using the Distinct Count With Pivot Table In Excel  2013 onwards. 3. Create A Dynamic Table Of Contents My Recent Blog Posts You Might Like- Formula Friday - Splitting Text And Numbers Formula Friday - Extract A Name From An Email Address Shhhhh…Want To Hide Your Excel Formulas?????…. 1. How To Remove All Cells Containing Specified Word/s Or Values Do you ever need to quickly do a bit of data cleansing by identifying cells that contain a specific value then delete those rows? It's really quick and easy to do.  Let's say we want to find all of the cells that contain the words FUNNY FISH, and delete those rows of data. Hit CTRL+F to bring up the Find and Replace Dialog box Type FUNNY FISH in the Find What box Hit Find All All cells that contain FUNNY FISH will de displayed Scroll down the results holding the SHIFT key which will select all of your results Hit CTRL+ - the shortcut to bring up the Delete dialog box Select to delete the entire row then hit  Ok That's it all of the FUNNY FISH data rows have been deleted! See how easy it is below- 2. Using The Distinct Count With Pivot Table In Excel 2013 onwards If you using Excel versions prior to 2013 then you may want to look away now...only kidding!!!! But this tip relly does only apply to Excel 2013 onwards, that is the ability to count unique items in your Pivot Table. First let's use a data table in Excel as our data source. Select your data set (CTRL+A) Hit CTRL+T Confirm your data table has headers Create your Pivot Table in the usual way by selecting the data table (or the shortcut keys ALT+N+V) Insert Tab - Tables Group - Pivot Table To ensure you can use the Distinct Count function this is where where MUST tick Add This Data To The Data Model in the Create Pivot Table Dialog Box (the data model is part of Power Pivot and works even if you dont have it installed) Simply drop the field you want to make a unique count of into the Values area in this case it is Product Names Then navigate to the Value Field Settings of the value you added At the bottom of the list you will now see Distinct Count (this does not appear if you do not click Add To Data Model) In this example I have chosen to count the number of unique products in the product list.  The result is 4 unique items.  Easy as that.  This is a handy feature that has at last been included in the Pivot Table functionality, saving a lot of time and effort removing the need for extra helper columns and formulas in your data to accommodate a unique or distinct count feature. Note this is not available in Excel versions prior to Excel 2013. 3. Create A Dynamic Table Of Contents In Excel. This Excel tip is in response to a comment on one of my blog posts from 'Harry'.  He had created a Table Of Contents (TOC) using the method below. SAVE YOUR WORKBOOK Select the cell you want to LINK TO Point to the cell border, and right click with the mouse. Press the ALT key and and drag the cells to the TOC area Once the destination sheet has been activated you can then drag the cells to position you want the hyperlink to be on the TOC area Release the ALT key and when the pop up menu appears, select "Create Hyperlink Here" So that is straightforward enough, but what happened when Harry has added new columns or rows, the references then became invalid and the links are broken!! One easy solution to this is to name the cell or cells your are hyperlinking to as a Named Range.  The benefit of using a named range is when the contents of a cell are moved for example when adding columns or rows, the named range does not change.  Handy. In my example below (dont forget to download the example workbook to take a closer look at the examples), I have 3 Report areas, 1, 2 and 3.  Both 1 and 2 have named ranges for their hyperlinks and 3 does not. The Names can be views in Name Manager. All three links work if I click on them and the target cells are the Report 1,2 and 3 Headers, in cells C2, C9 and H2 respectively. If I decide to insert an extra Column, then Hyperlinks to Report 1 and Report 2 work just as expected, but Report 3 points to an incorrect cell, as it does not have named range as a target.  Report 3 is hard coded into the Hyperlinks reference and must manually be changed anytime cells or rows are added.  H2 is now a blank cell and not the reference we wanted. Grrrrr See why I love named ranges. :-)... Hope you like that tip and thanks to Harry for the question!! Let me know what you think of the Excel tips. If you want a tip mentioned in the Excel at Excel Newsletter- at the email address below. I really hope you enjoyed this edition of the How To Excel At Excel Newsletter.  If you have any tips you want to share with other Excellers just email me- barbara [at] howtoexcelatexcel [dot] com and I will share with newsletter readers.   Keep Excelling!! BJohnston MSc,Bsc(Hons),MCP,MOS Don't forget you can follow me on Twitter for even more Excel Tips and Updates. The How To Excel At Excel Newsletter Is Available To Subscribers Only.  My records show that star.basak.howtodoit@blogger.com subscribed to this newsletter. If you have been forwarded this newsletter or you would like your own copy then send a blank email to me at the address above to subscribe or visit www.howtoexcelatexcel.com and hit Sign Up. Copyright B.Johnston All Rights Reserved
Barbara (at) howtoexcelatexcel (dot) com Co.Kildare, 00447510677 418 Ireland You may unsubscribe or change your contact details at any time. Powered by: