Don't Forget To add barbara@howtoexcelatexcel.com to your safe or contacts list to ensure you always receive your 3 FREE Excel Tips. |
| | | October 5 2016
Hello How
Welcome to the October 2016 edition of the How To Excel At Excel Newsletter. I hope you enjoy these next 3 tips.
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. Using Search Filters & Add To Selection.
2. Ranking Data With No Ties Allowed!
3. Unhide Rows Is Just not Working?
|
|
|
| | 1. Using Search Filters & Add To Selection.
| | From Excel 2010 onwards we have another methos for filterting data - Search Filters. Just search for what you are looking for in the search box and Excel will return any matches. This really speeds up data selection to just what you need.
Let's take a look at an example in my data below- (which is a summary of Fruit Sales In and SOld Out) and search for 'Apples' in the Fruit Type field then Hit Ok.
|
| | Now I have my Apples filtered, I want see the values of boxes in and out of of Kiwi also. This is where we can again search for what we want and then simply add it to our already saved filter criteria.
|
| | Just make sure to tick Add current selection to filter then hit Ok. This adds the new search data to the original. Carry on adding in data as you need to, and no need to make your selections all at once. It saves all of that scrolling up and down the list of boxes to tick.
|
| | | | | 2. Ranking Data With No Ties Allowed.
| | One function I use a lot of in Excel is Ranking. It's a simple one that compares a number to other numbers in the same list. For an example let's use the same data we have in our first tip, and rank the fruit sales (Boxes Out).
- Add an extra column to contain the ranking data named Boxes Out Rank.
- To find the rank of the Boxes Out (or sold) use the formula below in the top line of your data table.
=RANK([@[Boxes Out]],[Boxes Out],0) |
| | As the formula is within a table, Excel uses structured references such as [Boxes Out], and is the combination of Table and Column Names. Once we hit Enter, the formula will be automatically populated within the Excel table.
So just to recap. Ranking has 3 arguments-
RANK( number, array, [order] )
number The number to find the rank for.
array A range or array of numbers to use for ranking purposes.
order Optional. It specifies how to rank the numbers.
Note
- If order is 0, the numbers are ranked in descending order.
- If order is not 0, the numbers are ranked in ascending order.
- If the order parameter is omitted, it assumes order is 0 (descending order).
So we can see that Blueberries are the top seller in our Fruit Sales. But, what if we have a tie?, What if Strawberries were to sell as many boxes as Blueberries?. (I have changed mydata to refelct this in the screen shot below). So we need to find a way to break the tie of fruit sales.
Within my data I know I have how many boxes were sold at full price and how many were sold on offer. I can now make the decision to split the ranking tie dependant on how many boxes of fruit were sold at full price or offer price - Of the tied fruit sales the fruit type that sold most boxes at full price wins the tie or will be more favourably ranked.
|
| | We can easily do this by adding in another column- I have called it Tie Break. In this column I can use the following formula
=IF(COUNTIF([@[Boxes Out Rank]]:[Boxes Out Rank],[Boxes Out Rank])>1,RANK([@[Sold At Full Price]],[Sold At Full Price],1)/100,0)
This formula identifies any numbers in our Ranking Colum that appears more than once, if it does then those numbers are rankined based on the number of boxes sold at full price. I have then just turned this into a small decimal number to add to the number of boxes sold at full price to distinguish between the tied fruit types. Any ranking numbers that only appear once (not tied) are represented by zero.
|
| | By adding another column Called Ranking Plus Ties, which sums the Boxes out plus the decimal number indentified in Tie Break, I can the carry out a clean new ranking in the Overall Ranking Column, which I also added to the table.
You can see that Strawberries are in the lead, and Ranked 1 due to the higher number of boxes sold at full price. Hurray for Strawberries.
|
| | | |
|
|
|
|
|
| | | | 3. Unhide Rows Rows Is Just Not working.
| |
|
| | | A very confused friend of mine asked me to look at their Excel spreadsheet as they could not see some of the worksheet rows.
The sheet was not protected, so they had tried to unide rows in the usual way to see if the missing rows appeared, either right clicking with the mouse and selecting Unhide or the alternative -Home Tab - Format - Visibility - Hide and Unhide - Unhide Rows.
I tried the same myself, how strange. Rows 1 to 9 would just not appear.
|
| | | | | I had one last thing to try......row Height! And there it was, the row height for rows 1 to 9 had been changed to 0.5. To see the row height-
- Right click on the rows next to the hidden ones (it was tight at the top of the excel worksheet in my example)
- I changed the height from 0.5 to 15
- My rows are back!!
|
| | | 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
|
| |
No comments:
Post a Comment
Note: only a member of this blog may post a comment.