Excel Tips and Tricks
How to merge cells in excel without losing data
Excel will give you a warning message telling that you will lose the data, when you try to merge cells using the Merge & Center feature. In this article, we're going to show you how to merge cells in Excel without losing your data. Steps Begin...
Connect Your Data Sources and Automate Updates with Excel Data Connection
Although Excel does a great job at saving data in a nice table structure, some projects require data to be stored or exported elsewhere. Text files, other excel files, databases, or even web pages can be used as a means to consume or store data. If you need to...
A Guide to Custom Sorting in Excel
Excel is widely recognized for its powerful data sorting features, seamlessly organizing data in ascending or descending order, whether alphabetically or numerically. However, there are scenarios where the standard A-to-Z or 0-to-9 sorting methods may not suffice....
How to copy a formula in Excel between workbooks without links using a text editor
You may have noticed that Excel generates links between workbooks, when you copy and paste a formula from one to the other. These links between worksheets call the original workbook for the formula you need in the present workbook. To get rid of them, you need to...
How to create a year-by-year average calculator
Although the AVERAGEIF and AVERAGEIFS function can sum values by a given condition, they won't work if you want to use the output in other functions. In this article, we're going to show you how to create a year-by-year average calculator with help of the...
How to count cells in Excel by year
Although the COUNTIF and COUNTIFS functions can count values by a given condition, they won't work if you want to use the output in other functions. In this article, we're going to show you how to count cells in Excel by year. Syntax =SUMPRODUCT( N( YEAR(...
What week of the year is it and how to get its start date
Although Excel has a function to get week number of a date, WEEKNUM; there isn't any to make reverse calculation. This method will help you answer the question - what week of the year is it, and give you the start date of each week. Syntax...
How to reduce file size of your Excel workbooks
In addition to transportation disadvantages, a large Excel file is typically the culprit for performance drops when you’re opening, calculating, or saving a spreadsheet. If your file suddenly increased from KBs to MBs, it might be a good idea to troubleshoot for...
Count unique values Excel
This article shows how to count unique values Excel formulas SUMPRODUCT and COUNTIF. The SUMPRODUCT function can handle arrays without actually using array formulas, and this allows evaluating the formulas in each cell of an array. Syntax =SUMPRODUCT( 1 /...