In my line of work, I handled a lot of lines of data extracted from a database, and sometimes, I just wanted to filter the extraction criteria based on a smaller list.
While I used to save this criteria into a text list, and load the text into the filter, for some reason, that method stopped working because the macro used cannot open and recognize txt file anymore.
But filtering based on string is still working. I am not going to type in a few hundred characters each time I want to filter. Instead, I used TEXTJOIN, a new formula that came with the Excel 365, joining together everything in an area into a continuous string.
Here, I am using a “,” to separate each different data, ignoring black space, and joining all the data within column A.
I pasted the resulting string back into my extraction query. Simple as that.
Custom View is a function of Excel that I sadly found that many didn’t know about, and even less used it. You can find it under View > Custom Views.
What it essentially do was to save a snapshot of your file outlook at that moment. Imagine, your files could have multiple hidden data not meant to be shown in a presentation. And instead of hiding it one by one, you select custom views and had it return to the preset you had save for this. Do this multiple times, and it will save you hours of getting the view right.
You might have different business unit that view the same data but in different format, or even focus on specific data only. Create multiple custom views for it.
Eg, Exec A Presentation, Exec B Presentation, Exec C Presentation
Save the time from formatting the file each time there a different presentation with your preset.
I coupled it together with macro to export the pictures to PPT to make my presentation life easier.
Someone in the past asked me to help reduce the size of her excel file. It’s over 100mb and killing her storage space.
I did a few adjustments and it went down to 1mb. 100-fold decrease in size.
1. Remove Unused Spaces
Go to your Excel file, press Ctrl + End. How far did it reach? Do you need all these cell space?
Sometimes, you might selected a cell at the far end of the excel sheet. You realized the mistake and return to the original location. Easy right? No issue here?
Wrong. You selected a cell at the other end, and effectively turned the space between this cell and A1 as active sheet. Even if you didn’t do anything in these cells, the fact that they are active takes up memory size.
What you can do to remove this dead space is highlight the column after the last cell that you wanted to use, and click Ctrl + Shift + Right to reach the end of the active column. Right click and delete all these columns.
Do the same for the the rows. Highlight the row after the last cell that you wanted to use and click Ctrl + Shift + Down to reach the end of active row. Right click and delete all these rows.
Do the same for every sheet in the file and you removed the dead space inside.
2. Too Many Pivot Tables
No, I can’t remove my pivot tables. They are essential to my reporting. Sure, but that depends on how it’s created.
When you create a pivot table from raw data, it also creates a copy of the raw data in memory. So, each time you create a new pivot table from the raw data, it creates another copy of the raw data. Even if the raw data in question are both the same. See where I’m going with this?
With each pivot table you are creating, you making multiple copies of the raw data. However, if you copied the first pivot table and paste it, the new pivot table will refer to the source file of the first pivot table. You are not creating another copy of the raw data this way.
So, if you have multiple pivot tables that referred to the same raw data. Delete the rest and start over copy and pasting the first pivot table. You are preventing the raw data from being created over and over again.
3. Changing Excel format
The smallest excel format I used is a xlsb ending. The default excel format nowadays is xlsx and that compressed the size a lot over the previous xls format. So, if you are still using an xls format, or inherited a file from years ago, consider saving it in xlsx or xlsb for even more compression.
Because the logic is simple here, most defaulted to using Nested IF. I do too, but at some point, enough is enough.
Things become cumbersome if the Nested IF became longer. Over time, more conditions could pop up and a remedy would be to add another enclosed IF to specify that condition.
Logic wise, there’s nothing wrong with nested if but it’s a headache to view at organization level. Imagined having to read a formula with a longer nested if than maybe 7? I encountered a 23 level Nested IF and that was a nightmare.
So, please. Use Lookup instead if there’s a long nested if. It’s simple and much easier to adjust than a long string of formula.
One of the ways to truly maximize the power of the MS Office Suite is through macros.
A macro is basically a set of instructions to perform specific task. I set macros in Word, Powerpoint, but where it truly shines is in Excel. I saved hundred of hours of work just by automating a number of repeating task with macros.
Here’s how I would proceed with creating macros if I’m a
The Record Macro button. It’s found from View > Macros > Record Macros.
Give the macro a name and OK to start. It’ll immediately record all the actions done until the Stop Recording square button is clicked at the bottom left to stop recording.
Attached the macro to a button by going to Insert > Shapes to put in a button. Right click the button and select Assign Macro to link the macro created to this button.
Now, each time the button is clicked, the macro is activated and the same steps will be repeated without any interventions.
The pro about this method is that it’s easy and simple. You just have to perform what you want to do, and the macro will repeat it each time.
The con on the other hand is that it is crude. I’m not kidding when I said that it will record everything action including superfluous ones like scrolling the screen etc. The code created are not the most efficient and sometimes, more complex sequences couldn’t be performed through this method.
This is the level is where most of my projects are at. It’s at a level where you are comfortable to look into the source code and modify it. There are tutorials for writing macros available in the internet and the goldmine of source codes.
To access the source code, right click on the tab and select View Code to enter the VBA sheet.
On the left hand side, search for the Modules or wherever you want the macro to be at and write the codes there.
At this level, there’s no need to recreate the wheel. A lot of suggestions and sample codes are available online. I usually start from there. Far easier to work from something existing than to restart from scratch. If I do need to start from nothing, I would actually use the Record Macro function in previous method to record the actions I wanted to do and continue modifying from there. It’s also here where you can realize why there’s a lot of redundant codes created if you used Record Macro function.
Don’t let the mind be limited by what a macro can do. Since I’m doing a finance focused job, I automated my data extraction, parameter refresh, chart refresh, and even copy paste the charts to ppt. Yup, preparing and transferring charts as pictures to specific ppt were all done through macros.
To get better at this stage, I put in the lessons I learned from computer programming in the past. I envisioned what I want the macro to achieve at the end. It might appear utterly complicated, but I break them down into the simplest part on what each function should achieve. At this point, it might be easier to create a flow chart of actions. You can see the relationship on how to create the macro and the flow of data.
I inserted many comments to provide clarify if I revisit the source code in the future to remind myself, or anyone else who inherited the file on what each function should do. In the VBA screen, creating Toggle Point between two line of codes allowed to see how the codes interacted and where did it break down. It’s often how I diagnosed the codes if they don’t work as needed. This is where breaking down the macro to the simplest form allow easy checking on each functions.
At this level, it involved more in depth knowledge of macro than I’m used to. I’m talking about changing the GUI and creating addins etc.
Something like above. Almost new environment created inside Excel itself. I don’t think most work with Excel will ever need to reach this level but it’s there. And that file above is something I used on a frequent basis.