Excel has some powerful functions to calculate working days between two dates in Excel. These are especially useful when we are creating Gantt charts or timelines for a proposal/project. In this tutorial, you’ll learn about Excel functions that can calculate: Working days between two dates. Date after the specified number of working days.
Another question to answer and very interesting one indeed. Again this question came from our vibrant facebook community. The requirement is to change number signs that is change negative numbers to positive and positive to negatives. Although this is hardly the case in many situations that you really want an additive inverse for each and every value as most of the time users want all the values to be positive or all the values to be zero but in real life all sorts of things happen.
Last year the same day we learnt how to make a birth date heat map in Excel. So lets another Excel trick exactly one year after. Lets say the date of birth is August 6, 1986. And here are few things you want to find out only by punching your birth date:
At Excel Efficiency, we spend a lot of time explaining how to speed up formatting in Microsoft Excel. For example, you can read this shortcut roundup to get up to speed on the best formatting shortcuts. And there are some formatting hints in our list of ways to Idiot-Proof Your Spreadsheets, too. These shortcuts and tips cover the pre-defined number formats in Excel – which are probably sufficient for around 95% of your work. But if the default formatting options in Excel don’t quite allow you to present your work in just the right way, there is a solution.
Hyperlinks can be used to link to other sheets, webpages or other files such as PDF’s from your Excel spreadsheet. If you are creating hyperlinks for many records though this will take a long time to set them up. Excel provides a HYPERLINK function for creating hyperlinks in our spreadsheets. The real power behind this function is that it can be used to create dynamic hyperlinks.
For all those who think working with Excel is boring, here is your answer to them – Excel Camera Tool. Now, don’t get all excited. It’s is not about a fancy DSLR and lens. It’s just another feature with a cool name. To begin with, Excel Camera Tool is not even available by default in Excel QAT or Ribbon. You need to take a few steps to dig down into excel features and pull it out.
One of our community member asked how to convert rows to columns or make columns as rows in Excel quickly without using pivot tables? Many already know how to do this. But for those who don’t this one minute Excel tutorial is for them. By the way, yes! you can use pivot tables to shift rows as columns. To learn how to make pivot tables read this readers’ favourite tutorial. And most probably you will figure out the way how rows are shifted as columns using pivot tables.
In this tutorial, we will see how to check if number is power of two. There are many approaches to check if number is power of two or not. It is very easy and straight forward approach. Run a while loop which checks for condition if n is even number (n%2==0).
Let’s look at using one of Excel’s features – Auto Correct and put it to another use to speed up Formula Writing. With the Auto Correct feature in Excel, we can specify our own Auto Corrects, essentially we can use shortcuts of text to get Excel to automaticially fill in the beginning of the formula we are wanting to type. For example, let’s say we want to type VL and get Excel to auto correct us and type VLOOKUP, like below?
If you want to display your pivot table values as a percentage of the total, there is a simple solution. You don’t need to create a new calculated field, you can just right-click the values, select Show Values As, % of Grand Total.
Microsoft Excel lets you filter data. It’s a useful feature that can help you make sense of large amounts of data. If you lack the technical know-how needed to create a database, this feature is a life saver. The filters are fairly basic and allow you to include or exclude data from a column. The filter extends to and applies to data values in the corresponding rows as well. What the filter doesn’t do is auto-update itself. If a value in a filtered column changes, either because you manually updated it or a formula is in use, you have to apply the filters again. A simple code snippet allows you to update filtered columns on the fly.
If you use Excel regularly, I'm sure you've come across dates and times in your cells. Data often has a record of when it was created or updated, so knowing how to work with this data is essential. Here are three key skills that you'll learn in this tutorial: How to format dates in Excel so that they appear in your preferred style Formulas to calculate the number of days, months, and years between two dates An Excel date formula to log today's date, and a keyboard shortcut to add the current time
This tutorial shows different ways to add leading zeros in Excel: how to keep zeros as you type, show leading zeros in cells, remove or hide zeros. If you use Excel not only to calculate numbers, but also maintain records such as zip codes, security numbers or employee ids, you may need to keep leading zeros in cells. However if you try typing a zip code like "00123" in a cell, Excel will immediately truncate it to "123".
In this tutorial we are going to look at how you can have multiple criteria as your lookup value with VLOOKUP. We’ll use the CHOOSE function with an array constant, and enter it as an array formula.
Excel built-in data sorting is amazing, but it isn’t dynamic. If you sort data and then add data to it, you would need to sort it again. In this post, I will show you various ways to sort data in alphabetical order using formulas. This means you can add data, and it will automatically sort it for you.
One of the most prosperous skills I have picked up over the years as a financial analyst has been the ability to create custom Excel add-ins specific to my department and company's needs. This one skill I have been able to provide to my company has saved numerous people time, frustration, and money. While it took me well over a year to teach myself how to create top-notch add-ins, I want to let you in on a little secret....it's really NOT THAT HARD! And today I want to share with you just how easy it can be to build an Excel add-in that looks amazing and will provide tremendous value to your company as well as to your professional career.
I am currently working on creating a sales pipeline management dashboard. I have all the data in place and now I am looking for some visualization to create the dashboard. One of the charts that I absolutely want in there is a sales funnel chart.
If you work with Excel worksheets that have a lot of cell comments in a worksheet, this tutorial could be useful. Sometimes you may insert cell comments to highlight or explain data, or your boss may insert comments in cells while reviewing your work. If you have a huge dataset and the comments are scattered all over the worksheet, it may help to have all comments in a single place as a list.
In a pivot table, subtotals are automatically added to the outer fields, when you add more fields below them. The innermost field doesn’t show subtotals, but you can force them to appear, by creating pivot table custom subtotals. Learn more about pivot table subtotals on my Contextures website.
SUMIFS function can be used when you want to add the values in a range if multiple specified criteria are met. It returns a number that represents the sum of all the numbers for which the specified criteria are met.