Posts

And we used to get it wrong every time. Today I am here with a Shared Expense Calculator Template. It can be used when you are incurring expenses that are shared by many people (for example, sharing apartment expenses or vacation/trip expenses). All you need to do is enter the details, and it will automatically tell you who owes whom. I wish I had this template back then. Download the Expense Calculator Template Now before you jump in and download the template, here is a little bit about how it works:...

Intersect Operator in Excel can be used to find the intersecting value(s) of two lists/ranges. This an unusual operator as it is represented by a space character (yes that’s right). If you use a space character in between two ranges, then it becomes the Intersect operator in Excel. Intersect Operator in Excel You can use Intersect Operator in Excel to find: The intersection of a single row and column. The intersection of multiple rows and columns....

Most of the people who work with Excel spreadsheets deal with numbers in large/small datasets. And when you work with numbers, you will have all types of it (positive, negative, decimals, date/time). One of the common tasks a lot of us need to do often is to convert these numbers from one format to another. And probably the most common one is when you have to change negative numbers to positive numbers (remove the negative sign) for some calculations....

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....

When to use Excel CONCATENATE Function CONCATENATE function can be used when you want to join 2 or more characters or strings. What it Returns It returns the joined text. Syntax =CONCATENATE(text1, [text2], …) Input Arguments text1 – the first item to join. The item can be a text value, number, or cell reference. [text2], .. – Additional text items to join. It can handle up to 255 items, up to a total of 8,192 characters....

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....

MVP is an award that is given by Microsoft to individuals that share the passion for its technology (Excel in my case), and who actively share their knowledge with the broader community. This could be via blogs, forums, social media, etc.. This is awarded for 12 months and is assessed every year and re-awarded if the guidelines are met. Read more about Microsoft’s MVP program. It has been 13 months since I started Trump Excel, and this is a great encouragement to keep on learning and sharing excel tips....

Calculating the percentage as a proportion of a specified value (for example, if you eat 4 out of 5 mangoes, what percentage of mangoes have you eaten). Calculating the percentage change (YoY or MoM). This is usually used in sales reporting where the manager would want to know what’s the sales growth Year on Year, or Quarter on Quarter. In this tutorial, I will show you the formula to calculate percentages in Excel as well as to format the cell so that the numbers show up as percentages (and not decimals)....

You may need to convert formulas to Values in Excel if you work with a lot of dynamic or volatile formulas. For example, if you use Excel TODAY function to insert the current date in a cell, and you leave it as a formula, it will update itself if you open the workbook the next day. If this is not what you want, then you need to convert the formula into a static value....

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....

For example, suppose you want to highlight all the cells that have an error in it, you can easily create an Excel add-in that will highlight errors with a click of a button. Something as shown below (the macro has been added to the Quick Access Toolbar to run it with a single click): Similarly, you may want to create a custom Excel function and use it in all the Excel workbooks, instead of copy pasting the code again and again....

In this short tutorial, I will show you how to quickly remove the division error in Excel (using some easy formulas). I will also show you how you can find all the cells in a worksheet or workbook that contain the DIV errors. What is a #DIV/0! Error in Excel? In Excel, you will get a DIV error when you have a formula where there is a division and the divisor is 0....

However, when working with large complex formulas, typing or editing the formula in the cell becomes a bit hard. In such cases are usually do this in the formula bar. A formula bar is a bar below the ribbon and above the working area of the worksheet (as seen in the image below). Whenever you select a cell that has a formula, that formula is shown in the formula bar....

Here is an example of a waffle chart (shown below): In the above example, there are three waffle charts for the three KPIs. Each waffle chart is a grid of 100 boxes (10X10) where each box represents 1%. The colored boxes indicate the extent to which the goal was achieved with 100% being the overall goal. What do I like in a Waffle Chart? A waffle chart looks cool and can jazz up your dashboard....

For example, you may want to track activities such as when was a particular expense incurred, what time did the sale invoice was created, when was the data entry done in a cell, when was the report last updated, etc. Let’s get started. Keyboard Shortcut to Insert Date and Timestamp in Excel If you have to insert the date and timestamp in a few cells in Excel, doing it manually could be faster and more efficient....

For example, as shown below, there are two groups (A and B) with 5 items in each group. Each item has a score in column C. Now there are two rankings done with this data set. Column D has the overall rankings and Column E has the group-wise rankings. Now there are two rankings done with this data set. Column D has the overall rankings and Column E has the group-wise rankings....

And it could be a nightmare if, at that point in time, Microsoft Excel decides not to open (arghhhhh!). Unfortunately, this unexpected behavior is quite common where Excel refuses to open one of your important Excel files, or fails to open the Excel application altogether. So, if you are suffering from the ‘Microsoft-Excel-won’t-open’ syndrome, this article is just what you need. In this tutorial, I will cover the reasons why Excel refuses to open and the possible solutions that can help you in this situation....

Although stacked column charts can show the change over time of a part-to-whole relationship, it’s generally difficult to compare the relative size of the components that make up each column. Pros Multiple categories and data series in compact space Can show part-to-whole changes over time Cons Difficult to compare all but first series Become visually complex as categories or series are added Tips Limit data series and categories Avoid all 3d variants...

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....