What does it do?
Counts the number of cells that matches your specified condition
What it means:
=COUNTIF(range of cells to check, condition to check against)
Do you have a scenario where you want to count the number of cells that match a specific condition?
I’m sure you do! There is a simple way to count this with Excel’s COUNTIF formula!
The COUNTIF formula is very flexible indeed, so let us try to count the following from our Excel worksheet:
I explain how you can do this below:
DOWNLOAD EXCEL WORKBOOK
STEP 1: We need to enter the COUNTIF function in a blank cell:
STEP 2: The COUNTIF arguments:
What is the range of values that you want to check against your condition?
What is the condition that you want to check against?
For our 1st example, we want to count the number of values greater than 2.
You now have your count of numbers greater than 2!
STEP 3: Now let us try for counting the number of Yellow values:
You now have your count of values that have the Yellow text!
STEP 4: Now let us try for counting the number of names starting with the Letter J:
Let us use the wildcard expression J*
* signifies a wildcard character i.e. Return any value that begins with a J
You now have your count of values that have a starting letter of J!
CountIf Formula in Excel
The post CountIf Formula in Excel appeared first on Free Microsoft Excel Tutorials.
Have you ever come across a scenario where your dates in Excel are in the wrong format?
Say you type in 01/05/2018 which actually means January 5, 2018 in the US but it shows as May 1, 2018! It drives me nuts because the entire spreadsheet of dates is interpreted incorrectly by Excel!
That happens because your computer’s region settings are in UK format and need to be changed to a US format.
Thankfully, there is an easy way to change your Windows region & date settings so that the dates will be interpreted correctly by Excel!
Here I show you how you can do this.
STEP 1: For Windows 10, go to your Search Bar and type Date & Time Settings.
STEP 2: Scroll to the very bottom, and select Change date and time formats.
STEP 3: You can see the current Short date setting is Day-Month-Year.
Change it to M/dd/yy which is the US Date Format.
STEP 4: Now go to Excel and type in 01/05/2018.
It is now correctly interpreted as January 5, 2018!
How to Change & Convert UK Dates to US in Excel
The post How to Change & Convert UK Dates to US in Excel appeared first on Free Microsoft Excel Tutorials.
Imagine you have a lot of shapes in your Excel file and the shapes are all over the place!
You want to organize the shapes but it seems a pain to move them one by one!
What would you do?
Thankfully, Excel allows you to distribute and align shapes!
This is our initial layout of shapes:
STEP 1: Hold the CTRL key and select all of the shapes you want to move:
STEP 2: Go to Format > Arrange > Align > Align Bottom
You can Align the shapes to the direction that you want (i.e. Left, Center, Right, Top, Middle, Bottom)
STEP 3: Go to Format > Arrange > Align > Distribute Horizontally
You can Distribute the shapes either Horizontally or Vertically.
This will ensure the distance between the shapes are equally distributed.
Your shapes are now in good shape! (Pun intended)
Distribute and Align Shapes
The post Distribute and Align Shapes in Excel appeared first on Free Microsoft Excel Tutorials.
Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data. One of the steps it allows you to take is to keep duplicate records.
We usually remove duplicate lines but if we need to keep and check what the duplicates are, Excel allows us to do that too!
Let’s suppose you have this set of data. You can see that the marked ones are duplicate values, let us keep them!
STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table
STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)
Excel 2013 & 2010:
STEP 3: This will open up the Power Query Editor.
Go to Home > Keep Rows > Keep Duplicates
STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated table.
You now have your new table with the duplicate rows kept!
How to Keep Duplicates in Excel
The post Keep Duplicate Records Using Power Query or Get & Transform appeared first on Free Microsoft Excel Tutorials.
You have your Pivot Table ready, all sorted nicely both from a row and column perspective. However you just need that one minor sorting tweak or two.
Well, Excel seemingly has a lot of tricks and you can even sort an Excel Pivot Table manually!
For our example, let’s see this Pivot Table below. It is sorted by years (2012-2014), and months (Jan-Dec).
But what if we want to move July to the top, and the year 2014 as the first column year?
STEP 1: To manually sort a row, click on the cell you want to move. Hover over the border of that cell until you see the four arrows:
Left mouse click, hold and drag it to the position you want (i.e. upwards to the first row)
We dragged it to the top so it’s now the first row!
STEP 2: To manually sort a column, click on the cell you want to move. Hover over the border of that cell until you see the four arrows.
Left mouse click, hold and drag it to the position you want (i.e. all the way to the left)
Voila! You have successfully manually sorted your Pivot Table!
EXTRA TIP: You can click inside a cell e.g. January, and start typing in another month, like August. This will also manually sort your Pivot Table items.
The post Sort an Excel Pivot Table Manually appeared first on Free Microsoft Excel Tutorials.
Counts the number of cells that contain numbers
=COUNT(range of cells to check, [additional cells to include in the check]…)
Ever had a column of data and wanted to check if all of the values contain valid numbers?
It would be cumbersome to count and check them one by one, especially if you had hundreds of entries!
Imagine we have the following data, we see an error, a text and a couple of numbers:
Thankfully there is an easy way to count how many of these cells contain valid numbers using the Excel’s COUNT formula.
STEP 1: We need to enter the COUNT function in a blank cell:
STEP 2: The COUNT arguments:
What is the value / range of values that you want to check?
You now have your count of valid numbers!
Count Formula in Excel
The post Count Formula in Excel appeared first on Free Microsoft Excel Tutorials.
Calculates the compound interest
=FV(rate, nper, pmt, [pv])
=FV(interest rate, number of periods, periodic payment, initial amount)
Computing the compound interest of an initial investment is easy for a fixed number of years. But let’s add an additional challenge.
What if you are also putting in monthly contributions to your investment? Now that’s a lot more challenging to compute now!
How much would be available for you at the end of your investment?
Thankfully there is an easy way to calculate this with Excel’s FV formula! FV stands for Future Value.
In our example below, we have the table of values that we need to get the compound interest or Future Value from:
There are two important concepts we need to use since we are using monthly contributions:
STEP 1: We need to enter the FV function in a blank cell:
STEP 2: The FV arguments:
What is the rate of the interest?
Select the cell containing the interest rate and divide it by 12 to get the monthly interest rate (make sure that this is in a percentage):
How many periods?
Select the cell containing the number of years and multiply it by 12 to get the number of months:
What is the periodic payment?
Select the cell that contains your monthly contribution (this is your periodic payment):
What is the initial amount?
PV stands for present value, the initial amount. Multiply the entire result by -1.
Apply the same formula to the rest of the cells by dragging the lower right corner downwards.
You now have all of the compound interest results!
Calculate the Monthly Investment with Excel’s FV Formula
The post Calculate your Monthly Investment with Excel’s FV Formula appeared first on Free Microsoft Excel Tutorials.
Stock Data Analysis is no easy feat! Once you have a lot of historical stock data it’s hard to visualize the trend using technical analysis.
Thankfully Excel has a lot of stock charts to help you with that, and one of them is the Candlestick Chart!
A Candlestick Chart has a vertical line that indicates the range of low to high prices and a thicker column for the opening and closing prices:
Below is the data source that we are going to use in Excel:
You need a Date column which should be the first column.
Then this should be followed by a Open, High, Low, and Close column. This is the exact order that needs to be followed in order to create the Candlestick Chart.
In this example I show you how easy it is to insert this using Excel.
STEP 1: Highlight your data of stock prices:
STEP 2: Go to Insert > Stock Charts > Open-High-Low-Close
STEP 3: Right click on your Legend and choose Delete as we do not need this.
STEP 4: Go to Chart Tools > Design and select the preferred design to make your chart more presentable!
And there you have it! Your own Candlestick Chart!
Create a Candlestick Chart
The post Candlestick Chart Using Excel appeared first on Free Microsoft Excel Tutorials.
Power Query (in Excel 2010 & 2013) or Get & Transform (in Excel 2016) lets you perform a series of steps to transform your Excel data.
One of the steps it allows you to do is to split column by number of characters easily.
This is helpful when you have columns that you want to split by an equal number of characters, say ID numbers.
Let’s suppose you have the following source data below. We want to split it by 3 characters so we will have 4 parts for each ID.
Let us get to work!
Select the column you want to split.
Go to Home > Split Column > By Number of Characters
STEP 4: Select 3 for the Number of characters, Split Repeatedly and click OK.
STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated table.
You now have your new table with the separated columns!
How to Split Column by Number of Characters in Excel
The post Split Column By Number of Characters Using Power Query or Get & Transform appeared first on Free Microsoft Excel Tutorials.