How To Install Power Query For Microsofft Excel Add-In

How To Install Power Query

Power query is a great tool built by Microsoft that will help you work with data in Excel. This tool is great for connecting to various external data sources, querying and transforming data, or cleaning and parsing data.

• Web pages, Facebook
• Excel, CSV, XML, Text or Hadoop (HDFS) Files
• A Folder
• Various databases like MS Access, SQL Server, MySQL, Microsoft Azure SQL, Oracle, IBM DB2, PostgreSQL, Sybase, Teradata, OData etc…

This is available as an add-in for excel 2010 professional plus or 2013 and comes already built in for Excel 2016.

You can download Excel Power Query here from Microsoft.

Unfortunately, if you’re not running Excel 2010 professional plus or 2013, then you will need to upgrade to Excel 2016 in order to use this feature as it’s not available for previous versions of Excel. Mac user are also out of luck.

There are both a 32-bit and 64-bit versions and which one you choose will depend on the version of Excel which you have installed.

Table of Contents

Excel 2010

To check what version you have:

1. Go to the “File” tab.
2. Go to the “Help” section.
3. Here you will see the product version, if it says professional plus 2010, then you’re in luck.
4. Here you will either see 32-bit or 64-bit. Take note and download the correct Power Query add-in version accordingly.

Excel 2013

To check what version you have:

1. Go to the “File” tab.
2. Go to the “Account” section.
3. Here you will see the product version.
4. Click on the “About Excel” button.
5. In the screen that pops up, at the top you will either see 32-bit or 64-bit. Take note and download the correct Power Query add-in version accordingly.

Excel 2016

Power query comes pre-installed in Excel 2016 but has been renamed to “Get & Transform” and is under the Data tab in the ribbon. If you have Excel 2016, then you don’t need to do anything to use it.

Download The Add-In

Go to the Microsoft website:

1. Select your preferred language.
2. Click the download button.

Select The Correct Version

1. Select either the 32-bit or 64-bit version depending on your version of Excel.
2. Click the next button.

Run The Setup Wizard

Make sure Excel is closed and then click the download from the download bar from your browser.

Follow The Setup

Follow the steps in the Setup Wizard.

Power Query Is Now Ready To Use

Now the next time you open up Excel, Power Query will be available to use under its own tab.

Microsoft Excel Conditional Formatting

Conditional Formatting

 

Highlight Cells Rules | Clear Rules | Top/Bottom Rules | Conditional Formatting with Formulas

Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

Highlight Cells Rules

To highlight cells that are greater than a value, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click Highlight Cells Rules, Greater Than.

4. Enter the value 80 and select a formatting style.

5. Click OK.

Result. Excel highlights the cells that are greater than 80.

6. Change the value of cell A1 to 81.

Result. Excel changes the format of cell A1 automatically.

Note: you can also use this category (see step 3) to highlight cells that are less than a value, between two values, equal to a value, cells that contain specific text, dates (today, last week, next month, etc.), duplicates or unique values.

Clear Rules

To clear a conditional formatting rule, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click Clear Rules, Clear Rules from Selected Cells.

Top/Bottom Rules

To highlight cells that are above average, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click Top/Bottom Rules, Above Average.

4. Select a formatting style.

5. Click OK.

Result. Excel calculates the average (42.5) and formats the cells that are above this average.

Note: you can also use this category (see step 3) to highlight the top n items, the top n percent, the bottom n items, the bottom n percent or cells that are below average.

Conditional Formatting with Formulas

Take your Excel skills to the next level and use a formula to determine which cells to format. Formulas that apply conditional formatting must evaluate to TRUE or FALSE.

1. Select the range A1:E5.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click New Rule.

4. Select 'Use a formula to determine which cells to format'.

5. Enter the formula =ISODD(A1)

6. Select a formatting style and click OK.

Result. Excel highlights all odd numbers.

Explanation: always write the formula for the upper-left cell in the selected range. Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =ISODD(A2), cell A3 contains the formula =ISODD(A3), etc.

Here's another example.

7. Select the range A2:D7.

8. Repeat steps 2-4 above.

9. Enter the formula =$C2="USA"

10. Select a formatting style and click OK.

Result. Excel highlights all USA orders.

Explanation: we fixed the reference to column C by placing a $ symbol in front of the column letter ($C2). As a result, cell B2, C2 and cell D2 also contain the formula =$C2="USA", cell A3, B3, C3 and D3 contain the formula =$C3="USA", etc.