Array Formulas in Excel: All You Need to Know
Array formulas are an advanced topic in Excel. Usually Excel users discover them when reaching the limits of – let’s call them – normal formulas, e.g. SUM, VLOOKUP, COUNT and so on. This article provides an introduction of array formulas in Excel.
Insert Author Name, Date Last Saved, File Size into Excel Cell: 3 Methods (+Free Download)
You want to display the name of the author or who has saved the Excel file the last time? Or you want to insert the date last saved, the date created or the file size into an Excel cell? Unfortunately, there is no way without some disadvantages. Let’s take a look at three different methods.
Disable AutoSave in Office Permanently
The Office suite recently introduced a new AutoSave feature for OneDrive and SharePoint users. Probably with good intentions, Microsoft – like so often – did this exactly the wrong way: There is no option to disable it permanently. You can only do it for each file individually. What is so bad about that, you might asked. The problem is that every time you open a file that is located in your OneDrive or SharePoint folder, it saves every change you do. So what if you just want to look something up in an old file? Or you want to use an old file, do some changes and save it under a new name?
[…]
Count Number of Unique Records in Excel: 5 Methods (+Download)
A common task in Excel is to find out the number of different entries in a list. For example, you have a list of names and want to know, how many different people are listed as some people might be multiple times on the list. This article introduces 5 different methods of counting the number of unique records in a list, regarding two major differences.
- You simply want to know the number of unique records. There is no other condition to be considered.
- You want to know the number of different entries under one or more conditions.
Return Blank Cells Instead of Zeroes in Excel Formulas
If the return cell in an Excel formula is empty, Excel by default returns 0 instead. For example cell A1 is blank and linked to by another cell. But what if you want to show the exact return value – for empty cells as well as 0 as return values? This article introduces three different options for dealing with empty return values.
Convert Table to One Column in Excel: 4 Easy Methods to Copy All Columns underneath Each Other
Copy table to one column
Say you have an Excel table and want to copy all column underneath each other so that you only have one column. For example, you have a table 2 rows by 4 columns like in the screenshot on the right-hand side. You want to copy and paste this table to one column. You often need such transformation for inserting PivotTables or to create database formats. This article provides 4 simple methods to transform a 2-dimensional table into one column in Excel.
Named Ranges in Excel: See All Defined Names (Incl. Hidden Names)
Excel has a useful feature: Named Ranges. You can name single cells or ranges of cells in Excel. Instead of just using the cell link, e.g. =A1, you can refer to the cell (or range of cell) by using the name (e.g. =TaxRate). Excel also provides the “Name Manager” which gives you a list of defined names in your current workbook. The problem: It doesn’t show all names. Why that is a problem and how you can solve it is summarized in this article.
Equal-Plus in Excel: Why You Should Not Start a Formula With “=+”
You’ve probably seen it already: Starting a formula in Excel with “=+” (equal-plus) instead of just equal. So why do people use the “=+” instead of just “=”? Is there any meaning? This article describes the background of the equal-plus signs, the advantages and disadvantages.
Merge Sheets: Copy All Worksheets Underneath Each Other (+Download)
Say you have many worksheets, all in the same structure. You want to combine them into one worksheet. For example copying them underneath each other so that you can conduct lookups or insert PivotTables. In this article, you learn four methods to merge sheets in Excel. […]
Professor Excel Newsletter
- The best Excel tips, tricks and tutorials.
- 1x per month.
- No spam. Promised.
Your welcome gift:Save a lot of time with our 45-pages keyboard shortcut package.
Book: Speeding Up Microsoft Excel
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Overview | Features & details
Excel Shirts and Accessories
Check this out:
Try our free apps:
Professor's Daily Tips for Excel: Get one free Excel tip every day.
Professor's Error Helper for Excel: Solve any error in Excel.
Also Interesting:
- Array Formulas in Excel: All You Need to Know
- Insert Author Name, Date Last Saved, File Size into Excel Cell: 3 Methods (+Free Download)
- Disable AutoSave in Office Permanently
- Count Number of Unique Records in Excel: 5 Methods (+Download)
- FREQUENCY Formula in Excel: Everything You Should Know (+Download)
Top articles:
Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
