Skip to main content

Compare 2 Lists in Excel [Fast method]

This video is also available on my YouTube channel:

I am going to explain you how to compare two lists in excel. This is in my opinion the quickest and easiest way to do it.

 

So as you see here we have grocery list 1 and grocery list 2. We will start by selecting the first list then we hold the ctr key while selecting the second list.

Now go to the home tab. Click on conditional formatting. Choose highlight cell rules. Then select duplicate values.

 

A new window will appear. Now we have to make a decision. We can either choose to highlight the duplicate values or to highlight the unique values. For the purpose of this tutorial we will select the option duplicate values. Now press Ok.

So that’s all you have to do. As you will see, excel has highlighted all duplicate values with a red color.

 

formatting result

How to highlight all blank Cells in Excel [Conditional Formatting]

In this blog post I will show you multiple methods on how to Highlight blank cells. We will use conditional formatting to give these cells a background color.

Method 1: Dynamic

Step 1: Select the whole table. The easiest method is selecting the top left cell, then select the bottom right cell while holding down the shift button. Other methods might not work because the table is missing certain values.

Step 2: Go to Home tab –> Highlight Cells Rules –> More Rules… –>  a new window will open.

condition formatting more rules

Step 3: Format only cells with: Blanks –> format.

new formatting rule

Step 4: Select a background color.

Format empty cells

Step 5: press OK –> OK.

Video example from my youtube channel:

Method 2: Isblank()

Step 1: Select the whole table. The easiest method is selecting the top left cell, then select the bottom right cell while holding down the shift button. Other methods might not work because the table is missing certain values.

Step 2: Go to Home tab –> Conditional Formatting –> new rule…

Step 3: Select Use a formula to determine which cells to format.

New formatting rule for blank cells in excel

Step 4: Now we will use the formula =ISBLANK(A2). This is the first cell (top left) of your table.

Step 5: Click on Format. A new window will appear.

Format empty cells

Step 6: Click on OK and check the result.

formatting result

Method 3: LEN( )=0

Use following formula:  =LEN(A2)=0

Previous methods have 1 little downside. empty strings “” will not be highlighted by those methods. Using LEN might be a better option because it highlights both.

Download my files

Feel free to download my workbook so you can try this example yourself:

Download Workbook

select partition 1 with diskpart

How to restore a USB Drive or SSD back to full capacity. Without extra tools

I will explain you how to format your USB or SSD drive back to full capacity. Sometimes you will not be able to format your USB back to full capacity. you did a right mouse click -> format but you are unable to format the whole disk. The solution is actually really simple.

Step 1 : Go to start menu and click RUN

open search box windows

Step 2 : Click on RUN to open the application. A small windows will appear. Fill “diskpart” into the text box and press enter or “OK“.

Windows Run with diskpart

Step 3 : This will open the command prompt. Now enter “List disk” command. This will give you following list:

Yours might look different.

diskpart list disk comamnd

The first one is my hard drive. The second one is my USB stick. Please be careful, don’t delete the wrong disk.

Step 4: Now choose disk. I want the second disk so I type: “select disk 1“. Now press enter.
Console output: Disk 1 is now the selected disk.

Step 5: Now type: “clean“.

Console output: Diskpark succeeded in cleaning the disk.

Step 6: Now type: “create partition primary“.

Console output: Diskpark succeeded in creating the specified partition.

Step 7: Now go back to file explorer. Tip: Press ctrl+E to open this window quickly.

Step 8: Right mouse click on the USB and format the drive a usual.

Extra tips:

  • If u get Error message: “Cannot delete a protected partition without the force protected parameter set.” Then use the command delete partition override.

 

weighted average explained

Excel formulas: how to calculate weighted average

Definition

An average can be defined as the result obtained by adding several quantities together and then dividing this total by the number of quantities.

The difference with a weighted average is that we will give each number a weight. This number will represesent the importace of the number within our data set.

Click here for the wikipedia explonation.

Why would we use this?

Another good example to explain this concept is the following joke from Tim Ferris:

“The extremes inform the mean, but not vice versa.

That average can be deceptive or even meaningless.. Here’s a statistician joke for your next hot date:

Person A: What happens when Bill Gates walks into a bar of 55 people?
Person B: I don’t know. What?
Person A: The “average” net worth jumps to more than a billion dollars! (Bill Gates’s estimated net worth as of March 2012 was $61 billion.)

The joke makes an important point: sometimes it pays to model the outliers, not flatten them into averages.” – Tim Ferris, Tim.Blog

Calculating normal average

All scores in de table are between 0 and 20.

Let’s start of by calculating the normal averages. An average is the sum of a list of numbers divided by the quantity of numbers in the list. Use following formula: =AVERAGE(B14:D14)
We will get the following result:

Tip: A ‘$’ will lock down the reference to an absolute one versus a relative one. You can use this for a column, row or both.

But not all the results are equally important. As an example: an exam is more important than regular homework. So let me show you a better method.

Calculating weighted average

Weighted Average with SUM

We calculate this with the following formula:

=(result 1 x weight +result 2 x weight + result 3 x weight) / (weight 1 + weight 2 + weight 3).

For our first student the result is 14,8/20 .

=SUM(B14*$B$29,C14*$C$29,D14*$D$29)/SUM($B$29:$D$29)

weighted average explained

This is difficult to do when you have a lot of data. In that case, SUMPRODUCT is a better alternative.

Weighted Average with SUMPRODUCT

Use the following formula:

=SUMPRODUCT(list with grades, range of list with weights)/ SUM(range of list with weights)

=SUMPRODUCT(B15:D15, $B$29:$D$29) / SUM($B$29:$D$29)

weighted average witg sumproduct

 

Download workbook

Feel free to download my excel workbook and try this example yourself. Click on the link to start the download.
Weighted_average

 

select insert and pivot table

How do you create a pivot table in Excel? 7 easy steps!

What is a pivot table?

A pivot table is used to summarize and organize a very large data set. This creates a second table with the result without changing the original data. They are used to sort, count, and total the data. When used the right way the will save you lot’s of time.

Warning: you can’t use the pivot table function when there are empty cell’s. You won’t be able to select all the data.

I will show you a simple example on how to do this. The spreadsheet below tracks the profit that is generated by Coffee shops. Feel free to download the xlsx-file to try the exercises yourself:
Download Pivot table spreadsheet

excel table that will be pivoted

How to create the table

Step 1 : Select any cell in the table that u want to pivot. Then click on Insert -> Pivot table.

select insert and pivot table

Step 2: The following pop up box will appear. Check if the table range is selected. This should already be filled automatically. Next, choose to show the pivot table in a new worksheet. Click on the ok button to proceed.

select range and new worksheet

Step 3: Choose “PivotTable Fields” on the right side of your screen. This is done by dragging and dropping fields into the areas below. Start off by selecting the appropriate the values for Rows.

Tip: Select the check boxes and let the excel algorithm guess how you want to display your data.

excel pivot table fields

As a result we will see a table with al regions and their total profit. It’s quite amazing how fast we can analyse data using this tool.

excel pivot table fields filled in

Using Filters

Step 4: We can easily create a drop-down list to filter data in our table. You can do this by dragging a field to the Filter area. There will appear a drop-down list on top of our current table.

 

excel pivot table fields with filter

Step 5: Click on the drop-down box and select “Brussels“.

excel pivot table fields with filter result

Step 6: Now we will only see data from cities within “Brussels“.

excel pivot after filtering on region "brussel"

 

7. Additional tips

  • Experiment with the recommended Pivot Tables option. What are the results?
  • You can refresh data from your pivot table by right clicking on any cell from the pivot table on the right side of your screen. Next, select “refresh” from te option menu.

 

me selecting custom sort in microsoft excel

Excel : how to sort tabel on custom criterea [custom sort]

This will allow us to sort multiple collums of data. If we use the normal sorting function we will mix our data.
In this example we use a list of the largest companies in the world by revenue.

Step 1: Select the tabel you want to sort

Step 2: Select “Sort & filter” in the top right of your screen. Then select “Custom Sort…”.

step 3: Now in the dialog box select by wich value you want to sort the table. In this example I want to find out which company has grown the most.

step 4: Then we can choose to sort “largest to smallest” or “smallest to largest”.

 

 

 

 

 

Excel Data validation window with validation criterea

Excel: How to make a dropdown list

Step 1: Create a list of items in the same or in another worksheet.

Step 2: Select all the cells and give them a name. In this example we use the name “levels”. Choose a name that contains only one word and press enter. Do this by clicking the “Name Box”.

Step 3: Select the cells where u want the list to be.

Step 4: Press “Data” in the menu >> choose Data Validation >> List >> put an ” = ” sign in the source field followed by the list name. It’s also possible to select a range of cells.

Tip: You can also set the values without creating a list. Put the values between comma’s and do not use “=”.

Step 5: Your drop down menu is ready. We can select one of the values from our list.

Additional tip: Users will get an error when the try to type within these cells. You can turn this of in the data validation menu.

Error message: “This value doesn’t match the data validation restrictions defined for this cell.”

This will allow users type any value if they want to.