Professor Excel
Merge Excel files in Excel: 6 Methods

Merge Excel Files: How to Combine Workbooks into One File

You have several Excel workbooks and you want to merge them into one file? This could be a troublesome and long process. But there are 6 different methods of how to merge existing workbooks and worksheets into one file. Depending on the size and number of workbooks, at least one of these methods should be helpful for you. Let’s take a look at them.

[…]

Disable GETPIVOTDATA permanently in Excel.

GETPIVOTDATA: Disable GETPIVOTDATA permanently in Excel

Does this sound familiar to you? You want to refer to a cell within a PivotTable, let’s say cell C6. But instead of getting =B6, Excel does something like =GETPIVOTDATA(“Value”;$A$3;”Name”;”c”). This article shows you how to permanently disable GETPIVOTDATA in Excel.

[…]

Sort Worksheets in your Excel Workbook: 3 Simple Methods.

Sort Excel Sheets: 3 Simple Methods (+Download)

Especially for large Excel workbooks with many sheets it’s crucial to organize the content. You should—for example—sort worksheets in a logic sequence. One way to sort sheets would be in an alphabetical order. This article introduces three simple methods for sorting Excel worksheets.

[…]

Disable AutoSave permanently in Office

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

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.

  1. You simply want to know the number of unique records. There is no other condition to be considered.
  2. 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.

[…]

Named Ranges in Excel: See and Edit Hidden Names

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.

[…]

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. […]

wrong, sum, number, average, excel, status, bar

Status Bar Shows a Wrong Result (Sum, Average) – Here Is Why

Excel has a very handy feature: It displays some quick information about the selected cells in the status bar. This might be the sum, average, maximum or minimum value. Unfortunately, in some cases, the shown number seems to be wrong. Here is why!

[…]