Skip to main content

2. Excel VBA: Creating our first macro

  1. Open the Visual Basic editor. Go to the developers tab. Click on Visual Basic. A new window will open.
    Opening visual basic editor
  2. Go to the top menu and select Insert –> Module.
    Visual basic editor windows
  3. On the left of your screen you can see that a new window is created.
    A new module is created
  4. Enlarge the window so we can start making our first macro. We will name our macro “HellowProgram”. msgBox will create a message box with the text “Have a nice day”. “End sub” indicates  the end of our program. Close the window.

    Our first macro

  5. Now we will link the macro to a button. So it will be activated when we click on it. Your cursor will turn into a cross. By holding the alt key you can make sure that excel uses the existing grid lines. You can select multiple cells if you want.
    inser button to excel workbook
  6. Select the name of the macro we’ve just made. I named my macro ” HelloProgram” . Click on “Ok”.

    assign macro in excel

  7. Now you can use the button in your workbook. But first we want to edit the text. Edit the text of the button by right clicking on it. And clicking “Edit Text”.
    edit text of button
  8. Edit the text. You can name it any way you like.
  9. Click on the button. the “Have a nice day” message box will appear. Our first macro works!
    pop up message 2
  10.  Click on “Ok” to close the window.

 

 

Excel Options, customize ribbon

1. Excel VBA: Activating The Developers Tab

In this video is will explain you how to activate the developers tab in excel.

  1. Open up a new blank excel file.
  2. Go to the tab File in the top left of the excel menu. Then choose Options.
    Excel file menu
  3. Go to customize ribbon. Check “Developer” in the menu on the right.
    Excel Options, customize ribbon
  4. Click on Ok. The windows will close.
  5. The developer tab will now be visible in the top menu.
    Developper options tab visible in excel
  6. Done!

How To Use Calculate Compound Interest With Excel

So maybe you have an investment, and you want to know what it will be worth in a few years? I will show you a method how you can easily find out.

“Compound interest is the eighth wonder of the world. He who understands it, earns it … he who doesn’t … pays it.” – A. Einstein

compound interest in excel

The formula (by year)

Result = initial investment * ( 1 + interest rate) ^ number of periods

The formula (by Month)

Result = initial investment * ( 1 + interest rate/ periods per year) ^ (number of periods)

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.

 

Weekly graph of website visitors in microsoft excel

Excel: How to make a chart that contains big differences in data.

I got an e-mail this weekend asking following question:
How do I represent many very large and very small data values together in a MS Excel graph?

There are multiple ways to do this depending on which data you want to show your users.

Split the axis in two parts

Let’s start of making a table with some data that we want to visualize.

Most of the values in the graph are almost invisible. This makes it hard to read our data. How can we fix this?

Start by decreasing the chart’s plot area. So we can make room for a second graph.

Click on the area around the graph and press CTRL+D . This will make a duplicate on top of the current excel chart.

Resize and align both charts. The second chart comes above the first plot area. Don’t forget to delete the column names from second chart.

Select y-axis. Go to “axis options” Under the section “bound” you lower the maximum range to that the small values on the graph become visible. Then take the second y-axis and increase the minimum. Delete the x-axis from the second graph.

Now go to Insert -> shapes and pick a rectangular shape.

Now use this triangle to cover up the line between the graph. Then go to settings and give the rectangle a white filling.

 

Plot with a logarithmic scale

This is a more scientific approach to the same problem. But this makes it less easy to comprehend for the general public.

First select the y-axis -> right mouse button -> format axis.

This will give the following result.

Do you have a different approach to this problem. Let me know in the comments.