Tag Archives: Excel 2013

Excel: Conditional Formatting – Color Max Value in Range

CF_IQuestion: 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…)

CF_II

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.

CF_III

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.

CF_IVNow 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.

CF_V

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 🙂

Advertisements

PowerQuery – Using data from the Web

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!

PQ_ExcelAddIn 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.
1. Open Excel and click on the PowerQuery tab in the ribbon
2. In the Get External Data group click on the From Web button
PQ_I
3. Copy the URL from your addressbar from your browser
 PQ_II
4. In the dialog window in Excel paste the URL you’ve just copied and click OK
PQ_III
5. Wait for it…. A Navigation taskpane will show up on the right side of your screen
PQ_IV

6. The nav. pane will show you the available data from the URL you’ve copied. In this case we are presented with three tables and a document. The document does not contain the data we are looking for. The tables however….

7. Hover over the first table (in the screenshot table 0). Excel will show you a preview of the data the table contains.
 PQ_V
8. If you are satsisfied with the result click on the button Load on the bottom of the taskpane. The table will be inserted into your worksheet!
PQ_VI
That’s it.. no more hassle. If the table exceeds your screen don’t bother, PowerQuery will grab the whole table. No more copy and pasting. Yeah!What’s next?

Well it might be that you are almost satisfied but not quit yet. When hovering over the table you will see an option to edit the table before you load it into your worksheet. When you click edit you will see a new PowerQuery layout pop up with a lot of tweaking options for your datasource. Pick the ones you like and then click load to worksheet.

PQ_VII

Next time we will grab a more complicate table and edit the data before entering it into our worksheet. Stay tuned!
Happy Querying 🙂