Comparing Dynamic Arrays in Excel Comparing items is one of the main tenets of Excel. Recently, Excel released Dynamic Array and Spill Range functionality to most Excel 365 users. Dynamic Arrays are incredibly powerful and could fundamentally change how we use Excel. However, array functionality has been around for a long time. This...

Continue reading

Are the Spreadsheet Wars Back? A breakdown of Google Sheets and how it compares to Microsoft Excel It is hard to imagine, but waaaaay back in the 1980’s several spreadsheet programs were competing for market dominance in the field of finance. First was VisiCalc, followed by Lotus 1-2-3, and then a number of...

Continue reading

Building Dynamic Charts with Named Ranges This article dives deeper into two methods of creating a dynamic chart. The methods include: (i) Named Ranges with OFFSET and (ii) Excel 365’s new FILTER function. This new FILTER function makes chart automation much easier, allowing you to create powerful automation with much less...

Continue reading

Creating Absolute References and Locking Cells in Excel Creating Excel formulas can become a tedious task if the user doesn’t understand how cell references work. Each cell reference in a formula has two parts – the letter represents the column and the number represents the row. For example, the formula =B3 will show...

Continue reading

Using the ALT key in Excel If you want to become an efficient Excel user (or better yet a lightning-fast user), the ALT key must become your close friend (not literally though). To perform basic, common ALT Key actions in Excel (like adding a border to a cell or removing a...

Continue reading

How to Use Excel’s Paste Special Feature We often want to borrow some, but not all, of the attributes of a cell and paste them elsewhere. While regular pasting will transfer all of the attributes of the copied cell(s), using Excel Paste Special will allow the user to paste some of...

Continue reading

Copy and Pasting in Excel Text, values and formulas often need to be copied to other locations and/or adjacent cells to avoid duplicating work. While many Excel users rely on the mouse to copy and paste, a far more efficient way is to use built in Excel keyboard shortcuts. Here are...

Continue reading

Navigating Between Excel Worksheets and Files Here is one area of Excel (like many others) where it’s far more efficient to use the keyboard than the mouse. Here are the basic shortcuts for Navigating Between Excel worksheets in a file, or between files. These shortcuts will also work when creating a...

Continue reading

Editing Formulas in Excel with the F2 Key Formulas in Excel frequently need to be checked and modified. To modify an existing formula, first move to the cell in question. To “re-enter” the formula, there are a number of options: Click with the mouse on the formula bar above the column headings...

Continue reading

Creating a Basic Formula in Excel Formulas are the backbone of Excel analysis and financial models. At Training The Street, we are big believers in keeping formulas as clean and simple as possible, but sometimes formulas can become longer if there are cell references to other sheets / files or if...

Continue reading