A lot of Excel users hide rows and columns when they have some data that they don’t need visible. It’s a way to only keep useful data visible, and at the same time not have to delete the data you don’t need to be visible. And, if you have lots of such hidden rows/columns, it could be a pain to find and delete these hidden rows and columns (in the case when you don’t need them)....

Let’s take a look. Near the bottom of the border menu on the home tab of the ribbon is a group of commands for drawing borders. Excel has two basic modes for drawing borders: Draw Border and Draw Border Grid, and another mode for erasing borders called Erase Border. To draw a border, first select a color and a line style. Notice that when we select either of these options, Excel shifts into draw border mode; the Draw Border menu item is selected, and the cursor changes into a pencil....

Let’s take a look. Here we have some customer data. To illustrate how to extract text using the LEFT and RIGHT functions, I’ll use them both to pull out just certain parts of this information. For the first example, I’ll extract the area code from the phone number. This is a perfect application of the LEFT function. LEFT takes two arguments: the text you’re working with, and the number of characters to extract, which is optional....

Example #1 - Number too big or small Excel has limits on the smallest and largest numbers you can use. If you try to work with numbers outside this range, you will receive the #NUM error. For example, raising 5 to the power of 500 is outside the allowed range: Example #2 - Impossible calculation The #NUM! error also can appear when a calculation can’t be performed. For example, the screen below shows how to use the SQRT function to calculate the square root of a number....

Let’s take a look. Here we have a table that contains 12 months of sales data for a team of salespeople. Let’s use Conditional Formatting to quickly highlight the highest and lowest figures. To do this, we need to first select all of the data. Then we’ll use presets in the Top and bottom rules category of the Conditional Formatting menu. The presets in the Top and bottom rules category apply formatting based on the current selection....

Let’s take a look. Here we have a simple budget worksheet. Let’s apply some indentation to make the categories a little more readable. The easiest way to indent is to use the Indent buttons in the Alignment group on the home tab of the ribbon. One button increases the indent by one step, and the other button decreases the indent by one step. Once you reach zero, clicking decrease indent has no further effect....

Let’s take a look. Here we have an Excel table that contains almost 3000 rows. This is sales data, and each row represents one order, for one kind of chocolate, to one customer. Using this data, let’s build a pivot table and see what happens if we remove the source data. The moment a blank pivot table is created, Excel generates the pivot cache. As we add fields to the pivot table, we are actually working with the pivot cache....

Let’s take a look. As you work in Excel, one of the first questions you need to answer is “where am I now?” You can always tell what workbook you’re in by looking at the name in the window title bar. In this case, we can see that we’re in a workbook called Forecast 2013. All workbooks have at least one worksheet, and worksheets are listed as tabs at the bottom of the workbook....

Let’s take a look. When you use Find, there are three options for the “look in” setting on the Find tab: formulas, values, and comments. Comments are self-explanatory, so let’s focus on formulas and values. You can think of formulas as whatever is stored in the cell—the underlying formula—and values as whatever is displayed in the cell. The naming is a little confusing, so let’s take a look at some examples....

When working with data in Excel and creating reports/dashboards, sometimes you would have a need to split a cell diagonally. This is helpful when you want to show two separate headings within the same cell – one for the row and one for the column (as shown below in cell A1). And being the super awesome spreadsheet tool that Excel is, there is an inbuilt way to easily insert a diagonal line within a cell in Excel....

Pivot Tables are great when you want to analyze a huge amount of data in seconds. It also allows you to quickly create different views of data by simply dragging and dropping. And to create a Pivot Table, you need to have the data in a specific Pivot Table ready format. In many cases, you’re likely to get the data in formats that are not Pivot Table ready. This often is the case when someone manually collects data and creates a format that is more readable by humans (not Pivot Tables)....

When to use Excel SUBSTITUTE Function SUBSTITUTE function can be used when you want to substitute text with new specified text in a string. What it Returns It returns a text string where an old text has been substituted by the new one. Syntax =SUBSTITUTE(text, old_text, new_text, [instance_num]) Input Arguments text – the text you want to change. old_text – the text string that you want to substitute. new_text – the new text string with which you want to substitute old_text....

Note: this is an array formula, and must be entered with control + shift + enter. The formulas in F7 and F8 return the same result, but have different approaches. In cell F7, we have the following formula, using a nested IF approach: This is how Excel evaluates the IFs inside SUM: In essence, each IF “filters” values into the next IF, and only quantities where all three logical tests return TRUE “survive” the operation....

He has some amazing articles on his website, and I am going to copy this tip shamelessly here [with his permission of course] If you have a workbook where the tab’s names are numbers (for example 1, 2, 3.. or 2020, 2021, 2022..) then you are going to love this tip. Here it is – Give the desired number to your tab and put it in round brackets – (1)....

In the example shown, the formula in cell D6 is: When this formula is copied down column D, it multiplies the value in B6 by a number that starts with 1 and increments by one at each step. We want to start with 1, however, so we need to subtract 5, which yields 1. As the formula is copied down column D, ROW() keeps returning the current row number, and we keep subtracting 5 to “normalize” the result back to a 1-based scale: If you are copying a formula across columns, you can use COLUMN() function the same way....

Which returns the sum of the named range “data” on Sheet1. Once the string is assembled using values in B6 and C6, INDIRECT evaluates and transforms the string into a proper reference. Note you can refer to a named range in a formula without using INDIRECT. For example, the formula in D6 could be written: However, if you want to assemble the reference as text, and have Excel treat the text as a reference, you need to use INDIRECT....

where project (B5:B16) and data (C5:F16) are named ranges. With a lookup value of “Neptune” in cell H5, the result is all four quarterly values for the Neptune project, which spill into the range I5:L5. Note: this problem can also be solved with INDEX and MATCH, as described below. Although this example shows off the simplicity of the XLOOKUP function, it can also be solved with a straightforward INDEX and MATCH formula, as described below....

Note: this is an array formula and must be entered with control + shift + enter. In this case the we simply compare one range with another with a single logical expression: The two ranges, B5:B12 and F5:H12 are the same dimensions, 5 rows x 3 columns, each containing 15 cells. The result of this operation is an array of 15 TRUE FALSE values of the same dimensions: {TRUE,TRUE,TRUE; TRUE,TRUE,TRUE; TRUE,TRUE,TRUE; TRUE,TRUE,TRUE; TRUE,TRUE,TRUE; TRUE,TRUE,TRUE; TRUE,TRUE,TRUE; TRUE,TRUE,TRUE} Each TRUE FALSE value is the result of comparing corresponding cells in the two arrays....

Pivot Table Fields In the pivot table shown, there are four fields in use: Date, Area, Sales, and Day. Three of these fields are used to create the pivot table shown: Area is a Row field, Day is a Column field, and Sales is a Value field, as seen below. When the Sales field is first added as a Value field, it is automatically named “Sum of Sales”. In the example, it has been renamed “Sales “, with a trailing space to prevent Excel from complaining that the name is already in use....

The Quick Access Toolbar is a set of icons that allows you to easily access the commands you use the most. By default the quick access toolbar only contains 4 commands but the commands it contains can be customized to suit the user. Changing The Quick Access Toolbar Location If you don’t like the default location above the ribbon then you can move the Quick Access Toolbar below the ribbon....