Hello,

I work in a Big 4 in Finance and accounting and I'm also programmer. This guide is originated from countless mistakes i've seen people make, from complete beginners and also from experienced people.

I've been using Excel, and also programming for 8 years in professional settings, so this should be relevant wether you're advanced or just a pure beginner. These advices will be guidances on good practices. This will help you have a good approach of Excel. It won't be about hyperspecifics things, formula, but more about how to have a steady, and clean understanding and approach of Excel.

This guide is relevant to you if you regardless of your level if you :

• Work a lot on Excel
• Collaborate, using Excel.
• Deliver Excel sheet to clients.

So without further do, let's get stared.

First of all, what do we do on Excel, and it can be summarized in the following stuff :

Input > Transformation > Output.

As input we have : Cells, Table, Files
As transformation we have : Code (Formulas, VBA) , Built-in tools (Pivot table, Charts, Delimiter, PowerQuery), External Tools
As output we have : The Spreadsheet itself, Data (Text, Number, Date) or Objects (Chart, PivotTable).

And we'll focus on in this guide on :

• How to apply transfomations in a clean way
• How to take Inputs in a maintenable way.
• How to display Output in a relevant way

Part 1 : How to apply transfomations in a clean way

When you want to apply transformations, you should always consider the following points :

• Is my transformation understandable
• Is my transformation maintanable
• Am I using the best tool to apply my transformation

How to make proper transformations :

Most people use these two tools to do their transformations

Mistake people do : Formulas

We've all came accross very lenghty formula, which were a headache just to think of trying to understand like that one :

Bad practice =IF(IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)=5;INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;EQUIV("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6));-INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)))

Here are some ways to improve your formula writing, make it more clear and readable :

1) Use Alt + Enter and Spaces to make your formula readable.
Turn this :

=IFERROR(MAX(CHOOSECOLS(FILTER(Ventes[#Tout];(Ventes[[#Tout];[Vendeur]]=Tableau4[Vendeur])*(Ventes[[#Tout];[Livreur]]=Tableau4[Livreur]));MATCH(Tableau3[Champ];Ventes[#En-têtes];0)));0)

Into this :

=IFERROR(
          MAX(
               CHOOSECOLS(
                           FILTER(Sales[#All];
                                                 (Sales[[#All];[Retailer]]=Criterias[Retailer]) *
                                                 (Sales[[#All];[Deliverer]]=Criterias[Deliverer])
                                );
                                MATCH(Parameters[SumField];Ventes[#Headers];0)
                          )
              );
0)

Use Alt + Enter to return to the next line, and spaces to indent the formulas.
Sadly we can't use Tab into Excel formulas.
If you have to do it several time, consider using a Excel Formula formatter :
https://www.excelformulabeautifier.com/

2) Use named range and table objects

Let's take for instance this nicely formatted formula i've written,

=IFERROR(
          MAX(
               CHOOSECOLS(
                           FILTER(Sales[#All];
                                                 (Sales[[#All];[Retailer]]=Criterias[Retailer]) *
                                                 (Sales[[#All];[Deliverer]]=Criterias[Deliverer])
                                );
                                MATCH(Parameters[Field];Sales[#Headers];0)
                          )
              );
0)

Explanation : It filters the Sales tables, with the Criterias values, and then retrieve the MAX value of the column Parameters[Field].

=IFERROR(
              MAX(
               CHOISIRCOLS(
                           FILTRE(Formulas!$H$1:$L$30;
                                                 (Formulas!$K$1:$K$30=Formulas!$E$8) *
                                                 (Formulas!$J$1:$J$30=Formulas!$F$8)
                                );
                                EQUIV(Formulas!$C$8;Formulas!$H$1:$L$1;0)
                          )
              );
0)

Explanation : It filters some stuff with some other stuff within the sheet 'Formulas', and get the max value of that thing*.*

As a rule of thumb, you should be able to understand your formulas, without ever looking at the Excel sheet. /!\ If you need the Excel sheet to understand the formula, then it's a badly written formula /!\ .

3) When Formula gets too complex, create custom function in Vba or use Lambda functions.

When you want to use complex formulas with a lot of parameters, for instance if you want to do complicated maths for finance, physics on Excel, consider using VBA as a way to make it more. Based on the function in example, we could implement in VBA a function that takes in the following argument :
=CriteriaSum(Data, Value, CriteriaRange, GetMethod)

=CriteriaSum(Ventes[#Tout], MATCH(Tableau3[Champ];Ventes[#En-têtes];0), Tableau6[#Tout], "Max")

You can also use lambda functions in order to name your function into something understandable

=RotateVectorAlongNormal(Rotator, Normal)

We can understand what this function does just from its name and you don't have to spend 15 minute reading :

=IF(IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)=5;INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;EQUIV("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6));-INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)))

To figure out what result you're supposed to have.

4) Your formula probably already exists.

That's probably what you've been thinking if you know about the DMAX formula. But it was on purpose to bring this point to your knowledge.

=BDMAX(Vente[#Tout];Champs[@Champ];Criteres[#Tout])

This does the job, and it's applicable to many cases. in 90% cases, there's inside Excel a function that will do exactly what you're looking for in a clear and concize manner. So everytime you encounter a hurdle, always take the time to look for it on internet, or ask directly ChatGPT, and he'll give you an optimal solution.

5) ALWAYS variabilize your parmaters and showcase them on the Same Sheet.

Both for maintenance and readability, ALWAYS showcase your parameters inside your sheet, that way the user understand what's being calculated just from a glance.

If you follow all these advices, you should be able to clear, understable and maintenable formulas. Usually behind formulas, we want to take some input, apply some transformation and provide some output. With this first

Mistake people do : VBA

The most common mistake people do when using VBA, is using it in wrong scenarios.
Here's a table of when and when not to use VBA :

And here's a table of when to use VBA :

If you write VBA code, you should rely on the same rules as formulas for formatting given that you can be cleaner on VBA.

Part 2 : How to reference input.

When you reference input, you should always consider the following points :

• Is my reference durable
• Is my reference understandable
• Am I using the best tool to reference my input ?

Here the rule are simple :

How to properly reference your input :

Outside of a spreadsheet

Always use PowerQuery. When using PowerQuery, you'll be able to reference Data from other file which will then be preprocessed using the transformation step you've set up.

Using PowerQuery is better because :

• PowerQuery is safer and faster than manually copy pasting
• PowerQuery automates entirely all the prepocessing
• PowerQuery tremendously faster than Excel for all its task
• PowerQuery is easier to maintain and understand even from someone who never used it
• PowerQuery is Built-in in Excel

Outside of a spreadsheet input referencing use cases

Part 3 : How to display output in a relevant and safe way :

As an output

When you display an output, you should always consider the following points :

• Is my output necessary to be displayed ?
• Is it displayed in a understable way ?

Then about how to communicate, and display it will depend on the target. However less is more, and most of the time, your spreadsheet can do the job only using 5 Sheets in most cases.

TL;DR : To have clean Excel Spreadsheets :

• Use PowerQuery for Large Data input and preprocessing
• Format your formulas, and use named range
• Use VBA to write custom functions when Formulas are getting too lenghty
• Keep your Sheet count to a minimum

As it turns out, you can lock down a machine so far you no longer can execute windows media player. The only browser was Internet Explorer (Version 7, so no HTML5 support either) with disabled Plugins.

Invoking Windows API commands summons tasks in the calling process, so I did the only thing I found reasonable

There was an Application that monitored my process usage. With 98% in excel the job went quite well and everybody was happy.

If anybody is interested you can download it here. I am still trying to add a volume control and a save feature that also saves the position of the active item. File has playlist support. Available media formats depend on the system, but mpeg codecs and some basic AVI codecs are built in by default. I don't know why mkv support was available on this machine

EDIT: Added Download link

I accidentally found a stupidly simple way to unlock protected worksheets (Office 365). Searching the internet you've got your brute force method, your Google sheets method, your .zip method, and more. But I've discovered one that exploits an incredibly basic oversight in VBA. If you find someone who found this before me, please let me know so I can credit them!

Obviously you should use this information responsibly. Sheet protections should never be considered secure but people tend to put them on for a reason. I've only used this on workbooks that I own and manage - I suggest you do the same. Lastly, this method loses the original password so if you need to know what it was you'd be better with another method.

Anyway the code is literally just:

ActiveSheet.Protect "", AllowFiltering:=True

After running this single line, try to unprotect the sheet and you'll see it doesn't require a password anymore.

For some reason specifying true for the AllowFiltering parameter just allows you to overwrite the sheet password. That's the only important part to make this work, so set other parameters as you please. I did test a handful of other parameters to see if they also overwrite but they gave an error message.

Works in Office 365 for Windows. Haven't tested any other versions but let me know if it does work :)

Edit: apparently works in Office 2016 too

Over the past few months I’ve been collecting the Google Sheets formulas that save me the most time. Thought I’d share in case it helps anyone else:

1. =IMPORTRANGE("url","sheet!range") → Pull data from other files
   
2. =UNIQUE(A:A) → Remove duplicates fast
   
3. =FILTER(A:C, B:B="Done") → Auto-filter rows
   
4. =ARRAYFORMULA(A2:A*B2:B) → Apply to whole column
   
5. =SPLIT(A1,"-") → Break text into parts
   
6. =QUERY(A:D,"select B,sum(C) where D='Done' group by B") → SQL-style reports
   
7. =IFERROR(A2/B2,"Check") → Replace errors with text
   
8. =VLOOKUP(key,range,col,0) → Find values instantly
   
9. =SUBSTITUTE(A1,"-","") → Quick text cleanup
   
10. =REGEXEXTRACT(A1,"[0-9]+") → Pull numbers only
    

Even just a couple of these can save hours per week.
Curious — what other “life-saver” formulas do you all use most in Sheets or Excel?

I watched a colleague spend 20 minutes manually coloring rows yesterday and it physically hurt me.

Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.

It takes 3 seconds. That’s it. That’s the post. Save your time for something better!

I've used Microsoft Excel for 20 years, and these 20 tips & functions will make you an expert and increase your productivity (with examples of each below):

(1) Wildcards

(2) Duplicate

(3) Remove Duplicates

(4)Transpose

(5) Filter

(6) Conditional Formatting

(7) Sparklines

(8) Pivot Tables

(9) Auto-fill

(10) TRIM

(11) XLOOKUP

(12) IF

(13) SUMIF

(14) SUMIFS

(15) COUNTIF

(16) COUNTIFS

(17) UPPER, LOWER, PROPER

(18) CONVERT

(19) Stock Market data

(20) Geography / Maps

​

Let's discuss each in detail (with examples):

​

(1) Wildcards

A wildcard is a special character that allow you to perform partial matches on text in your Excel formulas.

Excel has three wildcards: an asterisk "*", question mark "?", and "~"

​

​

(2) Duplicate

Duplicate the data from the cell above.

Ctrl + D fills and overwrites a cell with the contents of the cell above it

​

​

(3) Remove Duplicates

Remove duplicates in a set of data in Excel Alt+A+M

​

​

(4) Transpose

This will transform items in rows, to instead be shown in columns, or vice versa.

To transpose a column to a row:

1. Select the data in the column,
2. Select the cell you want the row to start,
3. Right click, choose paste special, select transpose

​

​

(5) Filter

The FILTER function allows you to filter data based on a query.

For example, you can filter a column to show a specific product or date.

You can also sort in ascending or descending order.

The shortcut for this function is CTRL + SHFT + L

​

​

(6) Conditional Formatting

Conditional formatting helps to visualize data, and can show patterns and trends in your data

Go to: Home –> Conditional Formatting –> Highlighting Cell Rules

​

​

(7) Sparklines

Sparklines allow you to insert mini graphs inside a cell provides a visual representation of data. Use sparklines to show trends or patterns in data.

On the 'Insert tab', click 'Sparklines'

​

​

(8) Pivot Tables

A powerful tool to calculate, summarize & analyze data, which allows you to compare or find patterns & trends in data.

To access this function, go to "Insert" in the Menu bar, and then select "Pivot Table"

​

​

(9) Auto-fill

With large data sets, instead of typing a formula multiple times, use auto-fill.

There are 3 ways to do this:

(1) Double click mouse on the lower right corner of a 1st cell, or

(2) Highlight a Section and type Ctrl + D, or

(3) Drag the cell down the rows

​

​

(10) TRIM

TRIM helps to remove the extra spaces in data.

TRIM can be useful in removing irregular spacing from imported data =TRIM()

​

​

(11) XLOOKUP

XLookup is an upgrade compared to VLOOKUP or Index & Match.

Use the XLOOKUP function to find things in a table or range by row.

Formula: =XLOOKUP (lookup value, lookup array, return array)

​

​

(12) IF

The IF function makes logical comparisons & tells you when certain conditions are met.

For example, a logical comparison would be to return the word "Pass" if a score is >70, and if not, it will say "Fail" An example of this formula would be =IF(C5>70,"Pass","Fail")

​

​

(13) SUMIF

Use this to sum the values in a range, when they meet a certain criteria.

For example, use this if you want to figure out the amount of sales in a given region or by person.

​

​

(14) SUMIFS

SUMIFS sum the values in a range that meet multiple criteria.

For example, use it if you want the sum of two criteria, for example, Apples from Pete.

The formula is SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

​

​

(15) COUNTIF

Use COUNTIF to count the number of cells that satisfy a query.

For example, you can count the number of times a particular word has been listed in a row or column.

​

​

(16) COUNTIFS

CountIf counts the number of times a criteria is met.

For example, it counts the number of times that both (1) apples and (2)price > $10, are mentioned.

​

​

(17) UPPER, LOWER, PROPER

=UPPER, Converts text to all uppercase,

=LOWER, Converts text string to lowercase,

=PROPER, Converts text to proper case

​

​

(18) CONVERT

This converts one measurement to another.

There are multiple conversions that you can do.

An example is meters to feet, or Celsius to Fahrenheit.

​

​

(19) Stock Market data

You can get stock data in Excel

Enter a list of stock ticker symbols, then select the cells and go to the Data tab, then click the Stocks button within the Data Types group.

Excel will attempt to match each cell value to a company stock, and fill in data

​

​

(20) Geography/ Maps

Instead of researching geographical data or maps, use Excel

With the Geography data type, you can retrieve data like population, time zone, area leaders, gasoline prices, language, and more

Type the data you need, then go to Data Tab -> Geography

​

​

What would you add?

If you’re cleaning the same report every month (deleting columns, trimming spaces, filtering rows), Power Query can do it for you. Here’s how I’d get started, plus the habits that stop it getting messy.

Where to find it
Data tab > Get Data. You can connect to a workbook, CSV, folder, web page or database. When the preview appears, click Transform Data to open the Power Query editor.

A simple order that works

1. Remove any junk rows at the top and bottom (titles, totals).
   
2. Promote the first row to headers.
   
3. Set your data types (dates, numbers, text).
   
4. Remove columns you don’t need.
   
5. Filter out rows you don’t want (cancelled orders, blanks).
   
6. Clean text (Trim, then Capitalize Each Word).
   
7. Add any calculated columns.
   
8. Close & Load to a table, then use Refresh All whenever the data changes.

Habits worth building early

* Leave the raw data alone. Power Query reads your source file but never changes it. Do all your cleaning in the query, so you can always go back.
* Set headers and data types early. Later steps behave better when they’re in place, and dates stored as text cause all sorts of problems.
* One step, one purpose. Don’t cram two jobs into one step. It’s much easier to find and fix things later.
* Name your steps. “Filtered out cancelled orders” beats “Filtered Rows1” when you’re back in six months.
* Aim for a tidy table. One row per record, one column per field. Pivots and formulas get much easier.

If you get a new file every month
Use the folder connector. Put all the files in one folder and Power Query combines them. When you add next month’s file, you just hit refresh. The part that confused me initially was which steps go in the Transform Sample File query and which in the main query. My rule of thumb: steps that make one file look like a proper table (top rows, headers) go in the sample file, and anything that works on all the rows combined (filters, trimming, calculated columns) goes in the main query.

I made a video that walks through all of this on a simple example, in case it’s useful: https://youtu.be/23vd7vAEH2s

Hope people find this useful!

Just learned that Excel has a "Very Hidden" sheet state.
Unlike normal hidden sheets, these don't show up in the “Unhide” menu at all.

To create one:

1. Press Alt + F11 to open VBA.
2. In the Project Explorer, right-click a sheet → Properties.
3. Change Visible from -1 (Visible) or 0 (Hidden) to 2 (Very Hidden).

Now, only VBA (or the Developer tab) can bring it back. Perfect to keep things tidy or prevent accidental edits.

Did anyone else know about this ninja-level Excel feature?

I've worked 15+ years in Finance and use Microsoft Excel daily, here are 12 Excel tips & functions that will increase your productivity and make you feel like an expert:

(1) XLOOKUP

(2) Filter

(3) Pivot Tables

(4) Auto-fill

(5) IF

(6) SUMIF

(7) SUMIFS

(8) COUNTIF

(9) COUNTIFS

(10) UPPER, LOWER, PROPER

(11) CONVERT

(12) Transpose

​

Let's discuss each in detail (with examples):

​

(1) XLOOKUP

XLookup is an upgrade compared to VLOOKUP or Index & Match. Use the XLOOKUP function to find things in a table or range by row.

Formula: =XLOOKUP (lookup value, lookup array, return array)

(2) Filter

The FILTER function allows you to filter a range of data based on a query. For example, you can filter a column to show a specific product or date. You can also sort in ascending or descending order.

The shortcut for this function is CTRL + SHFT + L

(3) Pivot Tables

A powerful tool to calculate, summarize & analyze data, which allows you to compare or find patterns & trends in data.

To access this function, go to "Insert" in the Menu bar, and then select "Pivot Table"

(4) Auto-fill

With large data sets, instead of typing a formula multiple times, use auto-fill. There are 3 ways to do this:

(1) Double click mouse on the lower right corner of a 1st cell, or

(2) Highlight a Section and type Ctrl + D, or

(3) Drag the cell down the rows.

(5) IF.

The IF function makes logical comparisons & tells you when certain conditions are met.

For example, a logical comparison would be to return the word "Pass" if a score is >70, and if not, it will say "Fail"

An example of this formula would be =IF(C5>70,"Pass","Fail")

(6) SUMIF

Use this to sum the values in a range, which meet a criteria.

For example, use this if you want to figure out the number of sales for a given region.

(7) SUMIFS

SUMIFS sum the values in a range that meet multiple criteria.

For example, use it if you want the sum of two criteria, for example, Apples from Pete.

The formula is SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

(8) COUNTIF

Use COUNTIF to count the number of cells that satisfy a query.

For example, you can count the number of times a particular word has been listed in a row or column.

(9) COUNTIFS

CountIf counts the number of times a criteria is met.

For example, it counts the number of times that both, a (1) apples and (2) A price > $10, are mentioned.

(10) UPPER, LOWER, PROPER

=UPPER, Converts text to all uppercase,

=LOWER, Converts text string to lowercase,

=PROPER, Converts text to proper case

(11) CONVERT

This converts a number from one measurement to another.

There are multiple conversions that you can do.

An example is meters to feet, or Celsius to Fahrenheit.

(12) Transpose

This will transform items in rows, to instead be in columns, or vice versa. To transpose a column to a row:

1. Select the data in the column,

2. Select the cell you want the row to start,

3. Right click, choose paste special, select transpose

​

​

Which functions, formulas or shortcuts would you add?

Okay, so this is probably old news for most, but I just realized XLOOKUP can look backwards for the column to match to. I have used vlookup for so long, it took me a while to finally convert, but this has me sold 100%! I have had so many instances in the past with vlookup and needed it to look back, so I would either move/copy the column or set up an index/match, but xlookup is just so darn easy! Anyway, just wanted to share just in case anyone else is a late comer and didn't know.

TL;DR; can't post company-sensitive information? We understand. Take five minutes to mock it up with bogus data and attach it in table format

This message is aimed at people posting in this sub. It's 50% PSA / 50% rant.

Often I see in this sub "I can't share the data because it's company sensitive". So? Of course, we're not expecting you to breach your company's data privacy policy, but you're asking for help, so you should make every effort so that helping you is as effortless as possible. Your data is sensitive? Fine. Take five minutes to mock it up with Joe, Jill, Jack as names, oranges, apples, and lemons as products, etc. And then, go to https://xl2reddit.github.io, paste the table, and attach it to your post.

Important note: when you paste a table from Excel directly, it shows up nicely at first, but when the message is posted, it ends up all screwy, hence the tool.

I'm not saying screenshots are not useful to show the big picture, but data in table form is the fastest way for people on this sub to reproduce your problem and to get working on it, instead of wasting time rekeying in the data from a screenshot.

Full disclosure: I am the author of ExcelToReddit. I don't make a cent off it and I'm more than happy to see people using other tools (such as tableit.net which works for MarkDown), as long as I can copy-paste the data directly (or almost directly) into Excel.

Edit: added TL;DR;

Edit2: special shoutout to u/perihelixn for the beautiful hand-drawn chart mockup: https://redd.it/1iwxk3h

EDIT: The macro can filter by several items in a column.

Here is how these two macros work:

• Filter_By_Active_Cell (Ctrl + F): Filters the dataset based on the value in the active cell. If AutoFilter isn't active yet, it turns it on automatically.
• Clear_Filter (Ctrl + W): Clears all active filters to show all data, leaving the AutoFilter arrows intact.

I 've found more practical to use it as an excel complement (add-in).
Step-by-Step Guide

1. Create a standard macro-enabled workbook (.xlsm).
2. Press Alt + F8 to open the Macro dialog box.
3. Select the first macro and click Options...
4. Assign the shortcut keys for each macro (Ctrl + W andCtrl + F)
5. Go to File > Save As and change the file type to Excel Add-In (*.xlam).
6. To install it as an add-in, go to Developer > Excel Add-ins.

This is the code:

```
'
' Filtro instantáneo por una o varias celdas seleccionadas
' Acceso directo: CTRL+F
'
    Dim cel As Range
    Dim valores() As String
    Dim i As Long
    Dim dict As Object
    Dim FilterColAbs As Long, FilterCol As Long
    Dim primeraCelda As Range
    Dim currentReg As Range

    ' Validar que haya una selección activa
    If Selection Is Nothing Then Exit Sub

    Set primeraCelda = Selection.Cells(1)
    FilterColAbs = primeraCelda.Column
    Set currentReg = primeraCelda.CurrentRegion

    ' Calcular el número de columna relativo dentro del rango filtrado
    FilterCol = FilterColAbs - currentReg.Column + 1

    ' Caso 1: Si solo hay una celda seleccionada
    If Selection.Cells.Count = 1 Then
        currentReg.AutoFilter Field:=FilterCol, Criteria1:=Selection.Text

    ' Caso 2: Si hay varias celdas seleccionadas
    Else
        Set dict = CreateObject("Scripting.Dictionary")

        ' Recolectar valores únicos de la selección
        For Each cel In Selection
            If Not IsEmpty(cel.Value) Then
                dict(CStr(cel.Text)) = 1
            End If
        Next cel

        ' Si se encontraron valores válidos, aplicar el filtro múltiple
        If dict.Count > 0 Then
            ReDim valores(0 To dict.Count - 1)
            i = 0
            For Each Key In dict.Keys
                valores(i) = Key
                i = i + 1
            Next Key

            currentReg.AutoFilter Field:=FilterCol, Criteria1:=valores, Operator:=xlFilterValues
        End If
    End If

    ' Vuelve a dejar la selección original activa
    Selection.Select

End Sub


Sub Clear_Filter()
' Clears the filter to show all data
' Shortcut: CTRL+W

    On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

Enjoy!

Hey Everyone,

I just published my own VBA automation library and I wanted to share it with anyone interested. This library have been build during my career in Supply Chain management and have been with me for over 7 years now, helping me every day.

I thought that it might be useful to publish it for others.

Feel free to use and comment :)

Enjoy

https://github.com/GrzegorzDokowicz/VBA_Automation_Engine/tree/main

Tiny Excel nerd-tip:

You can bind a name directly to a built‑in function and then call it as if it were that function.

The trick

=LET(
  s, SUM,
  s(2,3)
)

This returns 5, because s is effectively another name for SUM, and Excel happily evaluates s(2,3) as a function call.

Same idea with TEXTJOIN:

=LET(
  j, TEXTJOIN,
  j(",", TRUE, H17:H18)
)

Is this actually useful?

Practically, not much. just shows that Excel functions are first‑class values under the hood.

• CTRL + A - Select All
  
• CTRL + B - Toggle BOLD (font)
  
• CTRL + C - Copy
  
• CTRL + D - Fill Down
  
• CTRL + E - Flash Fill
  
• CTRL + F - Find
  
• CTRL + G - Go To
  
• CTRL + H - Find and Replace
• CTRL + I - Toggle Italic (font)
  
• CTRL + J - Input line break (in Find and Replace)
  
• CTRL + K - Insert Hyperlink
  
• CTRL + L - Insert Excel Table
  
• CTRL + M - Not assigned
  
• CTRL + N - New Workbook
• CTRL + O - Open
  
• CTRL + P - Print
  
• CTRL + Q - Quick Analysis
  
• CTRL + R - Fill Right
  
• CTRL + S - Save
  
• CTRL + T - Insert Excel Table
  
• CTRL + U - Toggle underline (font)
  
• CTRL + V - Paste (when something is cut/copied)
• CTRL + W - Close current workbook
  
• CTRL + X - Cut
  
• CTRL + Y - Redo (Repeat last action)
  
• CTRL + Z - Undo

I've worked 15+ years in Finance and use Microsoft Excel daily and here are 10 shortcuts to make you feel like an expert and save you time:

​

1) To insert AutoSum formula: Alt + =

2) To create pivot table: Alt + N, V

3) To insert todays date: Ctrl + ;

4) To apply the currency format: Ctrl + Shift + $

5) To apply the percent format: Ctrl + Shift + %

6) To activate the filter: Ctrl + Shift + L

7a) To delete a row: Ctrl + -

7b) To add a row: Ctrl + +

8) To open spelling & grammar check: F7

9) To find: Shift + F5

10) To save workbook: Ctrl + S

​

Which shortcuts would you add?

I see so many posts on here about automating formulas. Automation is difficult and time consuming. Most of the time you can probably accomplish what you want by creating a template and pasting the formulas alongside a new data set, then auto filling. Unless you’re spending entire days extracting and reassembling data sets the automation squeeze is probably not worth the juice.

2) make things easy on yourself by using intermediate steps, columns, and formulas rather than massive multiple step IF, AND, COUNTIF, SUMIF…. Trouble shooting becomes much easier.

Hope this helps and happy spreadsheeting!

A common scenario is you want to share a report, but you have some tabs with raw data, notes, etc, that you want to remove.

The problem is, hiding them, they can simply be unhidden, and deleting them can give you the dreaded #REF error for anything linked.

Here’s the hack I found: create a new empty Excel file. Drag the unwanted tabs to that file. Then go to the data tab, edit links, and break all links.

This process will replace all linked values with raw values.

I am pretty surprised that there isn’t an easier way to delete tabs without going through this process. As far as I know, deleting a tab just creates a bunch of #REF errors by default, if the data is linked to another tab. Seems like the default behavior should be to convert the values to raw values like it does when you break links.

Someone told me about this here a long while back, but for whatever reason I slept on it and my jaw dropped when I finally tested it out today.

If you use Excel Tables, go to Table Design > right click on Table Name and Add to your Quick Access Toolbar. I assumed that it'd just add an icon to the quick access bar but it straight up just adds the field to the bar!

Now you can use Alt + [numeric position of the quick access item], (e.g. Alt + 1 in screenshot after I moved it to the front) to access it, but more importantly you can always see the table name without ever having to do Alt + JTA!

I'm sorry I don't remember the user who brought this to my attention and they're too far back in my notification history to find, but thank you so much for sharing this!

We so often see as matrix selection solutions the common INDEX MATCH MATCH , but a much tidier solution is XLOOKUP XLOOKUP

Example;

For data in a Table select the intersecting value of Harry for Tuesday.

With INDEX MATCH MATCH we use the two MATCH functions to return the index of vertical and horizontal header values to the table of data reference index values in INDEX

With nested XLOOKUP we return the column of data in the inner XLOOKUP to the outer XLOOKUP to return the data from the lookup row.

This is because the inner XLOOKUP returns the whole column of data to the outer XLOOKUP to return the row value.

Example;

=INDEX(B2:E4,MATCH(A7,A2:A4,0),MATCH(B7,B1:E1,0))

or

=XLOOKUP(A6,A2:A4,XLOOKUP(B6,B1:E1,B2:E4))

Bear in mind also that XLOOKUP does not return values, it returns ranges and range values.

For example you can sum between XLOOKUP return ranges

=SUM(XLOOKUP(A7,B1:E1,B2:E4):XLOOKUP(B7,B1:E1,B2:E4))

You could also include a username reference to limit the sum to Harry if so desired, a little test question for you to figure out ;)

Hello Excel Fans (And Begrudging Users)!

Simple Post. You can Use 1+ extra variable(s) in LET to add Comments.

LET allows for improved ease of complex formula creation as well as drastically improved formula debugging. You can also use LET, especially with more complex formulas, to insert extra variables and use them only as comments.

CommentN, "Comment Text",

The above is the simple structure.

When you have intricate or complex terms, using comments really helps other folks' understanding of the formula.

Just a fun Improvement Idea. Happy Monday!

To use the date picker, you can click on an existing date or format blank cells as dates and then double-click a cell to add a date. This feature is supposed to be coming to the desktop soon.

Couldn’t get a clear answer for this so came up with my own solution. If all you have is a year, but you want excel to recognize it as a date without recalculating based on the number of days past 1/1/1900 (who the hell came up with that one?) you can use this function.

=DATE(VALUE(A1),1,1)

This will plug your year into a recognized date format. Others have pointed out that simply treating the years as numbers typically works, but being recognized as dates also allows you to use some of the axis options, such as when modifying a graph in power point and wanting to edit your axis to display in bounds. If you’re doing so you’ll want to change the format to custom in the axis format and set the type to yyyy to display just the years, also adding a blank date at the end and beginning of the data set that fits with your display range (ie every five years) will make the axis display much cleaner while retaining your data points for specific years.

Microsoft shortcuts make no sense--probably why I missed this all these years. Maybe people will know of it, but to me this was new, and very useful knowledge! I work a lot with people who don't understand tables are meant to be longer than they are wide so not having to deal with clicking the little scroll bar all the time is really helpful. (and I know there are also fancy mouses that can do left/right scrolling but I don't have one of those)

Most people are familiar with the fact that no range in Excel can have more than 2^20 = 1,048,576 rows or 2^14 = 16,384 columns.

Less familiar is that fact that a dynamic array can have 2^20 rows or columns. Excel generates an error if you try to spill a dynamic array wider than 16,384 columns, but dynamic arrays used only as intermediate results are just fine up to 1,048,576 columns.

(N.B. a range [aka reference] is an array of one or more cells actually in a spreadsheet. A dynamic array only exists inside a formula, although it can be spilled into an empty range. ISREF returns TRUE for ranges/references and FALSE for dynamic arrays.)

There is also a limit of the number of elements a single dynamic array may hold. That limit is usually said to be "about fifty megabytes" but I've never seen anyone work it out exactly.

My testing shows that that limit is exactly 53,687,091 elements, which is 333 3333 hexadecimal. To confirm this, try the following:

=COUNT(SEQUENCE(3741,14351))

This returns 53,687,091. (It takes a couple of seconds, but not terribly long.)

Now try this one:

=COUNT(SEQUENCE(33284,1613)

This should return 53,687,092, but it actually returns an “Excel ran out of resources” error. That pins the exact maximum-elements limit exactly.

So if you create a dynamic array with 1,048,576 rows, it can have, at most, 51 columns, and vice versa. If it has 16,384 columns, it can have no more than 3,276 rows. The largest square dynamic array is 7327 on a side.

Note that this is not the limit of cells in a range. If you put =SEQUENCE(3741,14351) in cell A1 and put the same thing in cell A3743, Excel spills both arrays with no problem. Further, =COUNT(1:.7483), returns 107,374,182, so Excel can definitely operate on larger ranges. But if you try anything that would make this dynamic, e.g. =1:.7483+0, you get an out-of-resources error. Fifty megabytes is such a small amount of memory these days, it's surprising that Excel imposes such an arbitrary limit.

And why is this 333 3333 hex? That is, the binary pattern is 0011 0011 0011 . . . That's both a very clean and very strange number. It tells us something about Excel's internals, but I can't think what.

Anyway, I thought I'd share this, since I hadn't seen it before and wasn't able to find it online. And I'd appreciate it if others would try to replicate it. It's possible it only works on my machine--although I rather doubt that.

Edit: 1/5 in hex is 0.3333 . . . so the limit seems to be based on 2^28/5, which u/SolverMax noticed. The limit is the maximum number of 5-byte quantities that will fit in 256 megabytes (with one byte left over). Which is still mysterious, but not quite as mysterious.