Advanced XLOOKUP: All the great, fancy stuff possible with XLOOKUP!
In our previous article of this big XLOOKUP series, we have explored the basic usage of XLOOKUP. Now it is time to up our game: All the great, fancy stuff you can do with XLOOKUP. We look at error catching, wildcard lookups and grouping / classification problems. These advanced XLOOKUP functions will make you the lookup master!
Screen Sharing Excel files with Zoom & Co Like A Pro: Easy Tricks!
In times with an increasing amount of remote work, presenting Excel files through Zoom, Microsoft Teams, Skype, etc. seems to be the new normal. I often present day-in day-out my Excel files through screen sharing sessions. Depending on the audience, the task and the status of the file, the presentation should probably look as professional as possible. In this article you’ll find the most important tips and tricks!
XLOOKUP in Excel: Easily Solve all VLOOKUP Problems!
It seems as if Microsoft has listened to many complaints of Excel users and introduced a new formula: XLOOKUP. It’s supposed to improve all the disadvantages of the “traditional” lookup functions VLOOKUP, INDEX/MATCH and SUMIFS. This article describes in what case and how to use it. Please feel also free to download all example in an Excel workbook at the end of this article.
The XLOOKUP function is special in a way that it is very simple in it’s basic form. Just three arguments that are quite straight-forward. At the same time, it has very advanced capabilities. That’s why I’ve decided to split this article: The basic application, just using the first three arguments and advanced functions.
Convert XLOOKUP to VLOOKUP With These 4 Easy Methods!
The new XLOOKUP formula in Excel is great. It’s very powerful and solves many problems the iconic VLOOKUP (or HLOOKUP) formula cannot do. But there is one problem: It’s only available in the newest Excel versions. Older versions – which are still around a lot – can’t use them. In this article we explore 4 ways to convert XLOOKUP to VLOOKUP.
Excel Opens Blank Window When Double-Clicking On File? Easy Solution!
You try to open an Excel file, but Excel doesn’t open it? Instead, a blank or empty Excel window is shown? This can be very annoying. Especially because it’s often not clear, what has happened. Google searches (to my experience so far) show many results, but nothing seems to be working. Or the steps in other articles are very complex and long. Often there is just a simple reason.
I receive a lot of Excel questions. This one is one of the rather annoying types… but luckily you can check out this easy solution!
Comments to Cells – 3 Methods for Comments and Notes (+Download)
Many Excel users like to use the comment function in Excel. Unfortunately, they come with some disadvantages (displaced, wrong size, hiding content, bad for printing, often outdated, e.g.) so that I usually recommend not to use them. Instead, I recommend using a comment column. This article describes how to transform Excel comments to cells. Extract comment text or note text of an Excel cell with these 3 methods.
Return Number Format Codes in Excel – 4 Ways to Get the Formatting Code from a Cell
Excel is a great software. It’s easy to use (at least the basic functions…) and very flexible. Unfortunately, coming with the flexibility, users tend to misuse the options and disobey certain basic rules. One thing I’ve seen multiple times is to transport important information in the formatting of a cell. It might be the background color, font color or strike-through. In this article, we’ll talk about something related: Return number format codes.
Problem: Information is stored in the number format code of the Excel cell, for example the currency “USD” or “EUR”.
Let’s assume the following example. You receive a table containing prices with two columns. The first column contains the name of the item, for example “Product A”. The second column contains the respective prices. The problem is that instead of having one common currency, the currency information is only given in the number format code. Learn four methods in this article of how to return number format codes in Excel.
Please note: If you want to know how to use custom number format codes, please refer to this article. On this page we only explore how to return number format codes.
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.
Metadata in Excel: 6 Methods of How to See and Remove All Meta-Data
Every Excel file has metadata. According to Wikipedia, “Metadata is data [information] that provides information about other data”. That means, metadata is some data which is not your content of your file but rather information like the author name, data saved or even the file name. Or in other words: If your Excel file is a letter or parcel, the address written on it is the metadata.
In many cases, the metadata is very useful: You can easily see who edited the file the last time or when was it saved the last time. But sometimes, you want to remove this information. For example, when you send your file to a client and it still contains somehow data of your previous client.
This article introduces 5 methods to see, change and remove the metadata of your Excel file.
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.
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.
If the sign-up form doesn't work, please use this page.
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:
- Advanced XLOOKUP: All the great, fancy stuff possible with XLOOKUP!
- Screen Sharing Excel files with Zoom & Co Like A Pro: Easy Tricks!
- XLOOKUP in Excel: Easily Solve all VLOOKUP Problems!
- Convert XLOOKUP to VLOOKUP With These 4 Easy Methods!
- Excel Opens Blank Window When Double-Clicking On File? Easy Solution!