Example Generic Formula What It Does This formula will take the average of a numerical range then return the count of the items in the range that are less than the average. How It Works AVERAGE(Numbers) will return the arithmetic mean or average of the Numbers. In our example this is (13+5+7+9+20+3+4+6)/8 = 8.4. “<"&8.4 then creates the text string “<8.4" which is used as a condition in our COUNTIF function....
Sometimes you may have a need to embed a PDF file within the Excel workbook. This could be the case when you are creating a catalog and want to embed product specific PDFs. In this tutorial, you’ll learn how to insert a PDF file in an Excel Worksheet. How to Embed a PDF File in an Excel Worksheet Here are the steps to embed a PDF File in Excel:...
In many situations, you may want to insert a watermark in Excel (such as your company’s logo or an image) before sharing it with others. In case you are sharing a WIP (work-in-progress) version of the report, you may want to place a WIP/Draft as the watermark in it. While Excel does not have a dedicated feature to insert watermark in Excel, it can be easily achieved by using the Header & Footer sections in Excel....
How do you use VLOOKUP with multiple lookup criteria? In its basic form, VLOOKUP can only look up data based on one criteria. To get around this, you could add in another helper column that concatenates the different criteria together and then looks up data based on that. You formula might look something like this. Using array formula plus the CHOOSE function we can avoid this workaround and keep our data tables less cluttered....
I find the functionality to zoom in on a specific section of the data set in Excel quite useful when I used to present my work in calls and meetings. Once you know how to zoom in or zoom out in Excel, it only takes a simple keyboard shortcut or a couple of clicks to quickly get the right zoom level of your data. In this tutorial, I’ll show you simple methods to zoom-in and zoom-out in Excel....
What is Pivot Cache? Pivot Cache is something that automatically gets generated when you create a Pivot Table. It is an object that holds a replica of the data source. While you can’t see it, it is a part of the workbook and is connected to the Pivot Table. When you make any changes in the Pivot Table, it does not use the data source, rather it uses the Pivot Cache....
A serial number is a unique identifier for a row or record of data and they will usually start at 1 and increase incrementally with each row. This way you can refer to each record in your data set by the serial number. In this post, I’ll show you 15 interesting ways which you can add row numbers to your data. Use the Row Headings Good news! Excel comes with serial numbers out of the box....
Often you will need to rearrange your data when designing your spreadsheet solutions. You might need to move old data out of the way to make way for a new entry. Shifting cells down allows you to create space in your workbook and move any existing cells downwards so you don’t end up overwriting them. This post will show you all the ways to shift cells down in Excel....
Suppose you have the financial data for a company ABC Ltd, and you want to see how the revenue has grown over the quarters. At the same time, you also want to compare the growth with previous years. Dynamic excel chart can give you something as shown below: A single click instantly shows you the performance of 2013 as compared to 2011 or 2012. Try it Yourself.. Download the file...
When to Use Excel HOUR Function Excel HOUR function can be used when you want to get the HOUR integer value from a specified time value. What it Returns It returns a value between 0 (12:00 A.M.) and 23 (11:00 P.M.) depending on the time value used as the input. Syntax =HOUR(serial_number) Input Arguments serial_number: the time value that contains the hour that needs to be determined. Additional Notes The argument can be provided in different formats: As a serial number: The integer part of the serial number represents the date and the decimal portion represents time....
What it Returns It returns a value between 0 and 59 depending on the time value used as the input. Syntax =SECOND(serial_number) Input Arguments serial_number: the value that contains the time for which the seconds value needs to be determined. Additional Notes The argument can be provided in different formats: As a serial number: The integer part of the serial number represents the date, while the decimal portion of it represents time (remember excel stores date and time as serial numbers)....
Range A range is simply any collection of cells. Workbook A workbook is simply another name for your Excel file. Worksheet A worksheet is where Excel stores all your text, numbers and formulas. Each workbook contains at least one worksheets but can contain as many as you need subject to your computers memory restraints. Ribbon The ribbon is Excels graphical menu interface for commands you can perform. Commands are organised into different tabs and groups within tabs....
I often get queries on how to calculate regular hours and overtime hours of employees based on the ‘In time’ and ‘Out time’. I also couldn’t find a good Excel timesheet template online, so I decided to make one. Click here to download the Excel Timesheet Calculator This is a dynamic timesheet template in Excel where you can change the start date and the weekends. You can also specify hourly rates (regular/overtime)....
A few days ago, I was working on a project where I had to extract some tables/data from PDF to Excel. Now, this is not the first time I have been stuck in such a situation. In the past, I have somehow trudged through it with some or the other workaround. I remember a few years ago, I had to go through a set of PDF files that were in Spanish....
This tutorial is a part of a three-part Excel KPI Dashboard tutorial series: KPI Dashboard in Excel – Part 1: Dynamic Scatter Chart. KPI Dashboard in Excel – Part 2: Dynamic Interpretation. KPI Dashboard in Excel – Part 3: Dynamic Data Extraction + Bullet Chart. In the previous article of this 3 part ‘KPI Dashboard in Excel’ series, we learned how to create a dynamic scatter chart. Something as shown below:...
Now there could be various situations where you need to insert rows in Excel. For example, you may want to insert a single row, or multiple rows that are all together, or multiple rows that are not together. How to Insert Multiple Rows in Excel In this tutorial, you’ll learn four different ways to do insert multiple rows in Excel. Using Keyboard Shortcuts. Using Insert Options. Using Mouse + Keyboard (super trick)....
When you work with Excel charts, it rarely happens that you use the same default chart that Excel creates for you. In most of the cases, you have to change its formatting based on your brand, project, or audience. Now, if there is a fixed format that you have to use for many charts, it pays off to do all the formatting only once and then simply copy-paste the formatting....
And since there are so many formats, there are also ways to edit and modify these. One common thing many people need to do often in Excel is to remove time from dates (i.e., remove the time from the time stamp so that you only have the date). Below is an example where column A has the timestamps (with date and time both) and Column B only has the date value....
When to use Excel SMALL Function Excel SMALL function can be used to get the Kth smallest value from a range of cells or array. For example, you can get the 3rd smallest value from a range of cells. What it Returns It returns the value which is the Kth smallest value from a range of cells. Syntax =SMALL(array, k) Input Arguments array – the array or range of cells from which you want to fetch the kth smallest value....
