May
6
All Google Docs & Google Sheets limits you need to know
This blog post is a collection of the most important existing Google Docs and Google Sheets limits you need to know so everything loads and works like clockwork. Continue reading
Tutorials for Excel, Outlook and Google Sheets, page 5
Apr
27
How to find sum of largest N numbers in Excel
In some situations, you may need to sum specific numbers in a range, say top 3, 5, 10 or n. That might be a challenge because Excel has no inbuilt function for this. But as always, there is nothing that would prevent you from constructing your own formulas :) Continue reading
Apr
22
Version history in Google Sheets: see edit history & restore any version
Google Sheets has many beneficial features. Saving your spreadsheets automatically while keeping records of all changes made in the file is one of them. You can access those records, look through them and restore any version anytime. Continue reading
Apr
14
Merge Google sheets: update cells, add related columns & non-matching rows
Did you know that when you merge Google sheets you can not only match & update records but also pull other related columns and even non-matching rows? Today I will show you how it's done with VLOOKUP, INDEX/MATCH, QUERY functions and the Merge Sheets add-on. Continue reading
Apr
12
SUMIF multiple columns with one or several criteria in Excel
Doing a conditional sum in Excel is a piece of cake as long as all the values to be totaled are in one column. Summing multiple columns is a problem because both the SUMIF and SUMIFS functions require the sum range and criteria ranges to be equally sized. Continue reading
Apr
6
How to make a heat map in Excel with or without numbers
Microsoft Excel has a number of inbuilt features to create graphs. Regrettably, a heat map is not on board. Luckily, there is a quick and simple way to create a heat map in Excel with conditional formatting. Continue reading
Mar
18
How to merge Google sheets: lookup, match and update records
Today's blog post features all ways to merge 2 Google Sheets. You will use VLOOKUP, INDEX/MATCH, QUERY and Merge Sheets add-on to update cells in one sheet from records from another one based on matches in common columns. Continue reading
Mar
9
How to convert column number to letter in Excel
When building complex formulas in Excel, you may sometimes need to get a column letter of a specific cell or from a given number. This can be done in two ways: by using inbuilt functions or your own one. Continue reading
Mar
2
Using ISERROR with VLOOKUP in Excel
VLOOKUP is one of the most confusing Excel functions plagued with many issues. Using VLOOKUP with ISERROR can help you catch all possible errors and handle them in a way most appropriate to your situation. Continue reading
Feb
25
How to highlight duplicates in Google Sheets
There are different ways to deal with duplicates in Google spreadsheets. But highlighting them is the best way to spot them instantly. Here you'll learn how to color duplicate cells, columns, and rows while ignoring or taking their first occurrences into account. Continue reading
Feb
25
Mail Merge formatting issues in Word: causes and fixes
Using Word's Mail Merge to automate the creation of a document from an Excel worksheet may present lots of challenges. Our troubleshooting tips will help you fix typical formatting problems. Continue reading
Feb
23
Mail Merge in Outlook - send personalized bulk email
Whenever you need to send personalized emails to multiple recipients, mail merge is a real time-saver. It works great for sending out business updates, season's greetings, and the like, so that every recipient gets a personal email with their own information, without knowing who else this message has been sent to. Continue reading
Feb
17
How to find and remove duplicates in Google Sheets
Looking for a simple way to find duplicates in Google Sheets? How about 7 ways? :) That's all you need for numerous use cases :) I will show you how to use formula-free tools (no coding — promise!), conditional formatting and a few easy functions for avid formula fans. Continue reading
Feb
16
How to use ISERROR function in Excel
When you write a formula that Excel does not understand or cannot calculate, it draws your attention to the problem by showing an error message. The ISERROR function can help you catch errors and provide an alternative when an error is found. Continue reading
Feb
10
Excel split screen - view different parts of a sheet at once
When working with large datasets, it can be helpful to see a few areas of the same worksheet at a time to compare different subsets of data. This can be done by using Excel's Split Screen feature. Continue reading
Feb
2
How to open Excel files in separate windows and multiple instances
Having spreadsheets in two different windows makes many Excel tasks easier. One of the possible solutions is viewing workbooks side by side, but this eats a lot of space and isn't always the best option. Continue reading
Jan
19
Excel IFNA function to handle #N/A errors
Getting a lot of #N/A errors in your worksheets and are curious to know if there is a way to replace them with a user-friendly message? IFNA formula is the solution you need. Continue reading
Jan
16
How to number columns in Excel and convert column letter to number
The tutorial talks about how to return a column number in Excel using formulas and how to number columns automatically. Continue reading
Jan
12
ISNA function in Excel with formula examples
When Excel cannot find what it is asked for, a #N/A! error appears in a cell. To intercept and handle such errors, you can use the ISNA function. Continue reading
Jan
6
How to change Excel CSV delimiter to comma or semicolon
Imagine this: you want to export your Excel data to another application. Whatever CSV option you use, the result is a semicolon-separated file instead of comma-separated you really wanted. The setting is default, and you have no idea how to change it. Continue reading
Dec
22
How to highlight active row and column in Excel
When viewing a large worksheet for a long time, you may eventually lose track of where your cursor is and which data you are looking at. To know exactly where you are at any moment, get Excel to automatically highlight the active row and column for you! Continue reading
Dec
16
How to create dynamic Outlook email templates for the current user
Let's say, you have a really nice template, and your teammates would be happy to use it in their correspondence, but you're stuck on making this template dynamic. The problem is that each of your colleagues would like to get a different image, an individual attachment, and some specific text added to the template every time they use it. Continue reading
Dec
15
VLOOKUP to compare two columns in Excel for common values and missing data
When you have data in two different lists, you may often need to compare them to see what information is missing in one of the lists or what data is present in both. Comparison can be done in many different ways, which method to use depends on exactly what you want from it. Continue reading
Dec
9
How to use and store custom functions in Excel
Today we’ll continue exploring custom Excel functions. Now that you know how to create UDFs, let’s dig a bit deeper and learn how to use and store user-defined functions in Excel. Continue reading
Dec
8
RegEx in Excel: using regular expressions in formulas
At first sight, Excel has everything you could ever need for text string manipulations. Hmm… what about regular expressions? Oops, there are no built-in Regex functions in Excel. But no one says we cannot create our own ones :) Continue reading
Dec
2
Debug user defined functions
As you already know how to create UDFs (and, I hope, you’ve also tried applying them in your Excel), let’s dig a bit deeper and see what can be done in case your user defined function is not working. Continue reading
Dec
1
How to remove whitespace and empty lines in Excel with Regex
Whichever input data you are using, you'll hardly encounter a dataset without spaces. In most cases, whitespace is good. In some situations, however, it may become evil - extra spaces can mess up your formulas and make your worksheets almost unmanageable. Continue reading
Nov
25
Custom UDF function vs VBA macros: advantages and drawbacks
This article will help you understand the differences between UDF and Macro. Also, custom functions have many advantages, but there are also many limitations when using them. Continue reading
Nov
24
How to extract substrings in Excel using regular expressions (Regex)
Microsoft Excel provides a number of functions to extract text from cells. Those functions can cope with most of string extraction challenges in your worksheets. Most, but not all. When the Text functions stumble, regular expressions come to rescue. Continue reading
Nov
16
How to split Google Sheets into multiple sheets or files
When you work with a large Google spreadsheet, you may find yourself constantly filtering the table to get only particular datasets. Wouldn't it be best to split all info into multiple separate sheets or even files in Drive? If so, today I invite you to split your sheets and spreadsheets together. Pick the way you want to get your data and follow the steps described there. Continue reading
