How to Get The Worksheet Name, File Name, and Its Path

How to Get The Worksheet Name, File Name, and Its Path

by | Apr 6, 2018

Managing multiple workbooks with various sheets in Excel can be a challenging task, especially when dealing with dynamic file names that can be altered by users. However, there's a simple trick you can use to efficiently organize your data and label workbook details....
How to shorten a number and get the integer value

How to shorten a number and get the integer value

by | Apr 5, 2018

Do you need to shorten number, but not round them? Need to find the nearest INTeger or TRUNCate numbers in Excel? Yep, Excel has two functions that do exactly that! You can shorten numbers using the TRUNC and INT functions while preserving the original value, without...
How to rank data in Excel tables

How to rank data in Excel tables

by | Apr 4, 2018

Sure, it’s easy to find the highest or smallest values in a data set. But what if you’re looking for the 3rd, 10th, 20th rank in a large table, or need to do this in ascending and descending order? Luckily, Excel features a formula that does just that....
How to calculate weighted average with SUMPRODUCT

How to calculate weighted average with SUMPRODUCT

by | Apr 2, 2018

A final exam is worth more in terms of graduation score and the term weighed average becomes relevant. In such cases, SUMPRODUCT formula can help calculate the weighed totals of a data set. In our example, test scores of 3 candidates are being compared. Although...
How to COUNT values between two dates

How to COUNT values between two dates

by | Apr 2, 2018

A quick and easy way to count between dates is by using the COUNTIFS formula. Create formula-based filters, find your sales figures, or organize your tables in no time. Syntax =COUNTIFS(date range, >=minimum date, date range, <=minimum range) Steps Type...
How to SUM values if date is less than

How to SUM values if date is less than

by | Mar 29, 2018

How much did you spend until last year, or what is the total of X until Y? SUMIF and SUMIFS functions can help you to sum values in your data tables. Syntax =SUMIFS(values to sum range, date range, <maximum date) =SUMIF(date range, <maximum date, values to sum...
How to SUM values if date is greater than

How to SUM values if date is greater than

by | Mar 29, 2018

Using the versatile SUMIF function, see how you can create cell totals from a certain date. Creating a YTD (year-to-date) reports has never been easier. Syntax =SUMIFS(values to sum range, date range, >minimum date) =SUMIF(date range, >minimum date, values to...
How to SUM values between two dates using SUMIFS formula

How to SUM values between two dates using SUMIFS formula

by | Mar 29, 2018

Be it for budgeting, reporting, or analysis, you can easily sum data within a date range using the SUMIFS formula. SUMIFS is an extended form of SUMIF formula where you can enter multiple criteria. Syntax =SUMIFS(values to sum range, date range, >=minimum date,...
How to calculate running totals with SUM

How to calculate running totals with SUM

by | Mar 27, 2018

Using the SUM formula this way will allow your data tables to grow and include any new items by running totals. Syntax =SUM(Absolute reference, Relative reference) Steps Type SUM function with a range that contains only single cell =SUM(C3:C3) Make range’s first cell...