How many Excel functions are in Excel 2016?

According to this Office article, there are 477 functions in Excel 2016. They are distributed this way: Category Amount Statistical 112 Math and trigonometry 80 Financial 55 Engineering 54 Compatibility 38 Text 33 Date and time 25 Lookup and reference 24 Information 20 Database 12 Logical 11 Cube 7 Web 3 Add-in and Automation 3 Total [...]

Pivot Table Excel

This short story shows how I gained the respect and trust of 3 departments in the big company I worked for. Before I knew how Pivot Tables in Excel worked, I found a big opportunity to become valued at my office… …I noticed that one single Excel table contained all the data that the Planning, Cost, and [...]

How to do a Pivot Table in Excel

The reason why many Excel users never learn how to do a Pivot Table in Excel is because they don’t understand why they should learn Pivot Tables neither what is a Pivot Table. If you are still afraid of Pivot Tables or you want to establish a solid foundation to your data analysis journey, in [...]

How to reference cells dynamically using VBA

The best way to go through cells dynamically is using the Cells object inside a loop. This way you can refer to a different cell on each pass of the loop. Use a simple counter variable (you can name it whatever you want), which is increased or decreased on each loop pass. Here are some [...]

How to create a Pivot Table using VBA

You can generate a Pivot Table report with a mouse click. You can do it using Excel VBA and macros! Let’s suppose you want to generate a Pivot Table report based on a given Excel table created; see below… Figure 1: Source data for Pivot Table report This would be the resulting PT report (a [...]

How to run a macro from a button

You can run an Excel macro by clicking any Excel object you can imagine: text box, shape, picture, chart, etc. See below the main Excel objects you can use… After storing your Excel macro for future use, you can attach your macro to any button inside any workbook. Here’s the 3-step process to do it… 1) [...]

How to store the Excel macros you create or record for future use

If you acquire a brand new car and ignore the location of the keys, you couldn’t open the doors to get in, nor drive the car. In a sense, when you don’t know the location of your recorded Excel macros or written Excel VBA macros you won’t be able to get in the VBA editor [...]

How to find the code of a VBA macro in a workbook

Are you asking where the macros in a workbook are? Well, you should know that Excel stores VBA stuff: macros, modules, user forms, etc. as well as spreadsheet data inside workbooks. How does this work? Here’s a quick explanation… To access your macros VBA code you need to open the workbook compartment that contains VBA data, this is [...]

How to record a macro in Excel that works dynamically on different array sizes

You want to record dynamic Excel Macros that work on variable-size arrays, right? Maybe you recorded/created an Excel VBA Macro and worked awesome this time, but you are wondering if it will work the same way the next time. In just a moment you are about to learn how to record a dynamic Excel Macro [...]

Quick tip to make your recorded Excel macros super fast

Here’s the tip. Turn off screen updating. Doing this is like jumping to the desired movie scene instead of watching a chain of quick-motion sequences before you watch it. When you do this, your macro will not spend computing resources to update the screen at every action it performs. Shifting sheets (this does not occur if you [...]