Question: can you tell me how to color the maximum value in a cell range with conditional formatting? Yes I can and i will show you how it is done. Simple and effective. First open Excel and create a range with random numbers not sequentially but in random order. Follow the next steps:
– Select the range of cells you would like to apply the conditional formatting on
– On the Home tab click on the button Conditional Formatting (see pic. on the left)
Take a look at the various options to choose from. That are some fine options to choose from. We might be thinking about using the =MAX function for the question. However i think that i have a better solution to offer you. Click the second option from the menu Top/Bottom Rules. That little black arrow on the right is an indicator that another menu will appear. From that menu choose the first option (as shown below…)
See those three dots at the end of Top 10 Items… well that indicates that a dialog window will open. From that dialog change the default settings from 10 to 1 and choose your coloring. The image below shows the default setting an the setting after we have customized it.
Now all we have to do is click OK and your formatting will be applied on the selected range. The highest value should now be colored. The great advantage of Conditional Formatting is that it is dynamic. So if a number changes and that becomes the highest number the appropriate cell will be colored and the previous one will be white again.
Now this is one way to do the Conditional Formatting for this question. You can also choose to add a new rule yourself. In that case choose New Rule… from the menu. You will see the next dialog pop up. Choose the third option from that dialog and apply the same settings (top 1 – color) and click OK.
That is all there is to it! Simple, effective and colorful.
Homework: how to apply conditional formatting to the minimum value in a cell range?
Happy coloring 🙂
Copy – Paste?
How many times did you paste information from a website into an Excelsheet? It is a common scenario where people are surfing the web and want to keep the information they bump in to. A nice table presented by the website with all possible combinations, pricelists, adresses you name it. Well copy the table from the web and paste it into the Excelsheet right. But what if it is a very long table which exceeds the limits of your screen? Dragging your mouse very slowly and hoping that those annoying adds wont be in the selection as well. Enter PowerQuery!
||No more copy and pasting, no more annoying adds accidently being selected as well.
PowerQuery is an add in for Excel and you can download it here . When installed you will see an additional tab in your Excelribbon. For this example i want to use the data presented on the microsoft learning site about MOS certifications. There are various tracks to choose from. I want to have all the data. So let’s see if we can grab the info for Office 2013, 2010 and 2007.
Next time we will grab a more complicate table and edit the data before entering it into our worksheet. Stay tuned!
Happy Querying 🙂
This has been published a while ago but i do want to bring it to your attention once again. A great series of videos about Excel and Word 2010. The info is provided by MS Press. If you click one of the videos you will view them with Youtube.
Why should I watch these?
Well for learning basic simple steps, but also as preparing for your MOS exams!
This has been put together with great effort and it’s free why not view a video from time to time. You even have time to drink some coffee while you are at it 🙂
have a look here: http://blogs.msdn.com/b/microsoft_press/archive/2012/01/30/free-step-by-step-videos.aspx
Let me know what you think of them!
Happy Viewing 🙂
A new best friend…
During one of my courses i stumbled up on the following situation. One of my students was using nested functions and used the function wizard to accomplish his task. What caught my attention was the he was copying and pasting his nested function. In other words he was nesting functions by using the function wizard, closing the wizard, copying and pasting the rest of the functions in the formula bar. I asked him if he knew that he could accomplish the same thing using the function wizard. He didn’t and told me that this was the technique he used all of his Excel career. I showed him how he could do it and he and i are best friends for ever now 😉
Let me show you how you can nest functions in Excel without leaving the function wizard.
In this post i will show you two examples of nested functions in Excel using the function wizard. The first one is a simple example using the same function twice. The second example is more complex showing three different functions in one formula just to show the technique. Remember the example is simple and is just to show the technique!