Open Excel’s Options menu to customize the ribbon. We can add in our custom tab or group. Now you will see an extra tab in your ribbon with all the new groups of actions you added.

In this example let’s concatenate column 1 and have the list separated by commas. Using the CONCATENATE function we could use the formula: Using the & we could use the formula: Both not very practical as the range gets bigger. A better solution is to create a user defined function in VBA. Something like this will do the trick. With this VBA code, the formula needed becomes this. Much more simple....

In this example, we will move the Data and Sort & Filter section. From the Excel Options menu, we can customize the location of all the tabs and sections within tabs. We can’t move individual items within a section though 🙁 Hurray, the Data tab and subsections have moved.

Like Excel, PowerPoint has the ability to draw various shapes into your slides (or sheets in Excel). We can draw lines, arrows, rectangles, circles and many other shapes. But PowerPoint has an extra feature with shapes that isn’t available in Excel. In PowerPoint, we have the ability to merge shapes together to create new shapes. We could combine a long skinny rectangle with a star shape to create a magic wand....

In this example we have table A and table B. Table A contains data for clothing purchases by customer and product along with the quantity purchased and the total amount paid. This table has two dimensions of customer and product, and two metrics of quantity purchased and amount paid.Table B contains data for the same clothing purchases by customer and product with an extra dimension of description and also includes quantity purchased and the total amount paid....

If you find you’re always doing the same transformation or calculation, why not turn it into a function? This can simplify the steps in your query and allow you to re-use a solution. The Problem A while ago, I wrote a post on 4 ways to remove unwanted space characters from text data. Two solutions I looked at were the TRIM function in Excel and using Power Query. Excel’s TRIM function is easy to use and does a great job....

Copy and paste into cell A1 First, we’re going to turn our data into a data table so we can reference it with named ranges. This will allow things to update automatically when we add data to our table. Creating a data table will automatically give the data a named range (something like Table1), so we’ll change the name to something more meaningful next. Now we will add an extra column into our data table....

Next time your company has a social or fundraising bingo event breakout this Excel Bingo template on the projector to call out the bingo game. This bingo template features a Next button, which randomly selects the next number from the remaining numbers that haven’t been drawn yet. Numbers that have been drawn already will appear in a darker shade on the bingo card for player to see. The Clear button will reset the card for the next game, but don’t worry about accidentally pressing this during a game as it will prompt you to confirm your choice before erasing the game in progress....

Change the format for a part of your text in a cell. It’s a neat trick that will allow you to make key parts of the text really stand out to the viewer. You can also add line breaks within a cell to add white space and make it easier for anyone reading. Just press Alt + Enter when editing the cell to add a line break anywhere in your text!...

Suppose we have some data for an employee list that will continually be updated and we want to make sure that any user adding to the list doesn’t add a duplicate entry. In this post we’ll learn how to use data validation to prevent a user from entering a value if it’s already been entered in the table. In our example we have a simple set of data that contains a field for employee ID, name and address and we will prevent any duplicated employee ID’s being entered....

Of course you do! If you want to create interactive dashboards in Excel and visually explore your data, then you need to start using slicers and timelines. They’ll really take your workbooks to the next level… and they’re easy! In this post, we’re going to learn everything there is to know about slicers and timelines in Microsoft Excel. There’s a lot to cover, so let’s get started. What Is A Slicer In Excel?...

In this example we’ll create a custom list for a few European countries. In the Options window. Now we can add our list. Now to use this list all you have to do is type in one of the items in the list then drag the cell cursor using the lower right corner. It’s a pretty neat feature that can save you some time.

First create a new sheet called “Comments” for the purpose of writing out all the comments. Then add this bit of code into the visual basic editor. Now all you have to do is run the code and all your comments will appear in the sheet you created called “Comments” and you will be able to click the hyperlink to go to the cell containing the comment.

Unfortunately the acquisition fell through but the development team had already finished coding the Easter egg into the core code. The Easter egg addition was long forgotten and was never removed from the core Excel code but can still be access during the Excel loading screen. While Excel is loading press Up, Up, Down, Down, Left, Right, Left, Right, B, A on your keyboard. If you entered the code correctly during start up, you will be able to play Contra from within Excel....

For each value which we want to replace, we have to create a Replace Values step in the query. If we have tens or even hundreds of values to replace, then it can be quite tedious. If we already have a list of values which we need to replace, then it would be easier if we could do it all in one step based on the list! In this post we’re going to learn how to do a bulk find and replace in power query based on a list of values....

Syntax VLOOKUP(Criteria, Range, Column, Type) Criteria (required) – This is the value you are going to try to find.Range (required) – This is the range of cells that you want to search.Column (required) – This is the column number of the range that contains the result you want to return.Type (optional) – This is the type of match you are looking for. FALSE if you want an exact match and TRUE if you want an approximate match....

Normally when you create a formula in Excel it will return a calculated value in the cell. But you might want to see the formula and not the value it generates. This can be very useful for inspecting your formulas for potential errors. Showing your formulas will also help you to familiarize yourself with a new spreadsheet and what items are calculations and which are static data. When the need to view your formulas arises, how do you show them in the grid instead of the values?...

Datasets with people’s names usually have the first and last name in one cell, designated by a space character. Although this isn’t necessarily bad, it can be more helpful to have the first and last names in different columns. This way you can more easily perform further analysis on your data such as sorting and filtering based on just the last name. This post will show you all the ways to extract the first and last names into different cells in Excel!...

Syntax COUNTIFS(Range 1, Criteria 1, Range 2, Criteria 2,…) Range 1 (required) – This is the range to which the first criteria is applied.Criteria 1 (required) – This is the criteria Range 1 has to satisfy to be included in the count.Range 2, Range 3,… (optional) – These are more criteria ranges that can be used.Criteria 2, Criteria 3,… (optional) – These are more criteria to which the associated range has to satisfy to be included in the count....

It’s going to save you time and effort if you put in the small amount of time to learn it. The best part is, it’s built right into Excel 2016 or later. It’s also the same technology that’s used in Power BI, so you’re learning two in demand data skills at the same time! If you haven’t heard of power query and the awesome things it can do, or you want to fully understand it better, then check out my Complete Guide to Power Query beforehand....