Posts

And as more advanced tools such as Powe Query and Power Pivot now rely on Excel tables to be the data source, Excel tables are going to become even more important. But this tutorial is not about the awesomeness of Excel Tables, rather it’s about something that a lot of people find frustrating about it – the formatting. When you convert a data range into an Excel table, Excel automatically applies some formatting to it (you can, of course, customize or remove the table formatting)....

MOD(Range,2)=0 will create an array of Boolean values where TRUE will mean the number is even and FALSE will mean the number is not even. We then multiply this Boolean array by the Range to get an array where each value is either an even value from the Range or a 0. This is because NTRUE equals N and NFALSE equals 0 for any number N. In our example MOD({1....

Syntax IF(Expression, Value if true, Value if false) Expression (required) – This is the expression you want to test.Value if true (required) – This is the result the function will return if the expression is true.Value if false (optional) – This is the result the function will return if the expression is false. If nothing is entered here the function will return FALSE when the expression is false. Example In this example we use the formula IF(A2=”Donald Trump”,”Lame”,”Not Lame”)....

And one of the common tasks most Excel users have to do is to go to the end of the data in the column (i.e., the last filled cell). While you can quickly go to the end of the column to the last filled cell, or select the entire column till the last filled cell with an easy keyboard shortcut, things can get a bit complicated if you have blank cells in the column....

When you click the format painter you will be able to use it once, but if you double click it then you will be able to use it an unlimited number of times until you click the button again to turn it off. Easily Rename A Sheet Place your mouse cursor over the sheet you want to rename and double left click. Now type the name you want to change it to....

And one of the things I had to do while cleaning the data was to delete any blank columns in the data set. While you can always manually select columns and delete them one by one, doing so in a large data set, where you have tens or hundreds of columns in every data set, would be inefficient and error-prone. While there is no inbuilt functionality in Excel to date blank columns in one go, this can be achieved by using a combination of different functionalities....

For example, if you have an address, you can use the VBA Split function to get different parts of the address that are separated by a comma (which would be the delimiter in this case). SPLIT is an inbuilt string function in Excel VBA that you can use to split a text string based on the delimiter. Excel VBA SPLIT Function – Syntax Expression: This is the string that you want to split based on the delimiter....

Usually, the first column (or the first few columns) has the headers for each row. And when you scroll to the right of your worksheet, it would be helpful if the headers in the column were visible. When they are visible all the time, you do not have to keep scrolling back to see what the values further across the worksheet mean. This saves effort and time. This is where the ability to freeze columns comes in handy....

But don’t worry. A cool bunch of excel functions (or a little bit of VBA if you’re feeling fancy) can easily do this for you. In this tutorial, I will show a couple of ways to count words in Excel using simple formulas. And at the end, will also cover a technique to create a custom formula using VBA that will quickly give you the word count of any text in any cell....

It’s also a useful tool when I’m sharing my files with other people, as I can restrict data entry in some cells and only allow the selection to be made from the drop-down. However, in some cases, you may have a need to remove the drop-down from your Excel file. This could be because you no longer need the drop-down, or you want to enter something else other than the options in the drop-down....

For example, if you have the sales data for a month, you may want to know the total sales that have happened between two given dates or the total sales made on weekends vs weekdays. One of the ways to quickly get these answers is by using the SUMIFS function. SUMIFS (as the name suggests), allows you to sum a range based on criteria. Within SUMIFs, you can specify multiple conditions and it will sum only those cells/values that meet all the conditions....

Not to be mistaken with Excel Tables, Data Tables are a tool that can be used to do “what if” type analysis and allows you to see the results of a calculation while varying up to two of the calculation inputs. In this example we’ll create a data table based on the accumulated value of a bank deposit that earns interest (i.e. how much money will we have after a given time at this interest rate)....

In Excel. there are text functions that allow you to quickly change the case of the text (to lower case, upper case, or proper case). Below is an example of each type of case: Excel UPPER Function – Overview UPPER function is one of the many text function in Excel. What does it do? It takes a string as the input and converts all the lower case characters in that text string to upper case....

Syntax INT(Number) Number (required) – This is the number you want to round down to the nearest integer. Example In this example find the round down a few numbers. Text values will result in a #VALUE! error.

If you wonder what was my first post, here it is – Trump Excel’s First Post. Now coming back to cool Excel stuff, in this post I am sharing an amazing Excel Template that can help you manage your daily/weekly tasks with ease (and its fun to use) – TASK MATRIX Before you jump away and download it, here is some background to this template. Have you heard of 7 Habits of Highly Effective People?...

This is usually the area where we summarize fields by various different aggregation methods like taking the sum, average, minimum, maximum or standard deviation. But the thing is, these aggregation methods require numeric data! Is there any way to summarize text based data that will return text as the result? The answer is yes, but we will need to use the data model and DAX formulas to do this. Traditional pivot tables do not have this functionality....

Tables are a great tool for storing all your tabular data, but when you want to add new data to the table you will need to extend the boundaries of the table. This will allow you to add one or more rows to the bottom of your table so it’s obvious that you can add new data to the table. This post will show you how to expand your Excel table and increase its range size....

As it’s name suggests, it is used to split the text into multiple columns. For example, if you have a first name and last name in the same cell, you can use this to quickly split these into two different cells. This can be really helpful when you get your data from databases or you import it from other file formats such as Text or CSV. In this tutorial, you’ll learn about many useful things that can be done using Text to Columns in Excel....

If yes, then you may find the AutoFormat option useful in speeding up your formatting work. It allows you to quickly apply a preset formatting on a data set that has one header row and one header column. It allows you to quickly apply a preset formatting on a data set that has one header row and one header column. So you quickly get a result as shown below:...

When to use Excel WORKDAY Function Excel WORKDAY function can be used when you want to get the date after a given number of working days. For example, if I start a project today and it will take 20 working days to complete it, then I can use the WORKDAY function to get the completion date. This function is best suited when you want to calculate the invoice due date, project due date, delivery date, etc....