This is because Excel has a specific way of storing date and time values in Excel. While a full day is stored as a whole number in Excel, time values are stored as fractions/decimals. For example, 44927 represents the date 01 January 2023, and 44927.25 represents 01 January 2023 06:00 AM. Here, 0.25 is the fraction that represents the time. Since a day has 24 hours, 0.25 part of the day would mean 6 hours (24*0....
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....
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....
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)....
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....
Excel VLOOKUP function, in its basic form, can look for one lookup value and return the corresponding value from the specified row. But often there is a need to use the Excel VLOOKUP with multiple criteria. How to Use VLOOKUP with Multiple Criteria Suppose you have a data with students name, exam type, and the Math score (as shown below): Using the VLOOKUP function to get the Math score for each student for respective exam levels could be a challenge....
Today there is no excel tip in this message. Just a plain and simple THANK YOU!! It is you who makes this initiative special, and I hope what I do makes you better and more successful in your professional and personal work. I want to take this opportunity to share what we have achieved so far. Here are some key stats, and given that Trump Excel is only one year old, these look pretty staggering to me (especially as I never thought that the response would be so awesome)...
And if you want to automate your work in Excel using VBA, you need to know how to work with cells and ranges using VBA. There are a lot of different things you can do with ranges in VBA (such as select, copy, move, edit, etc.). So to cover this topic, I will break this tutorial into sections and show you how to work with cells and ranges in Excel VBA using examples....
If you work with data and create good looking client-ready reports, it will pay off to learn some Excel formatting shortcuts that you can use regularly and save time. In this tutorial, I am sharing my top five Excel formatting shortcuts that I almost every day. Watch Video – Excel Formatting Shortcuts If you prefer reading instead of the video, here you go! 5 Powerful Excel Formatting Shortcuts I often get a lot of data download from colleagues or files from databases that have no formatting to it look raw and ugly....
Excel Data Filter feature gives you many ways to filter the data – based on text, value or date. Suppose you have the data as shown below: To activate Excel data filter for this data, select the entire data and click on the Filter button in the Data tab in Excel ribbon (keyboard shortcut – Control + Shift + L) Once you apply filter to the data, you will see a filter icon in each of the header cells of your data When you click on this filter icon, you can access all the filter and sorting options available for that column of the data....
