This page provides sample Excel macros, this is a growing list. I place macros that in my opinion makes the life of the person who has to work with Excel and need to find some code solutions to make more useful and powerful spreadsheet applications.
On this page I simply added some personal stuff where I used Excel to support my hobbies or interests while having a little fun with Excel.
XLOOKUP FunctionSamples provided on each tab showing different ways of using the new XLOOKUP function. This function became available during the summer of 2019 and provides a whole lot more versatility than the VLOOKUP.
Active Cell ColoringMacro changes the active cell color to yellow and when left it reverts back to its original color.
Discount FunctionUser defined discount functions applied as a regular built-in spreadsheet function to calculate the discounted amount.
TVM - Time Value of Money CalcsSome basic financial calculations are provided on this example.
INDEX MATCH ExampleINDEX MATCH is a lot more powerful function than VLOOKUP.
Excel Tips and TricksMany practical and useful Excel tips and tricks can be found on this document.
Absolute to Relative Value Conversion - From OzGridThis macro was created by OzGrid for business applications and could be highly useful on a huge page of data that needs to be converted between absolute to relative addresses depending on the types of operations that need to be applied on the same range of data.
Array Formula ExampleArray functions are entered as [Ctrl+Shift+Enter] key combination into a cell not only by hitting the Enter key alone. Their power is looking into a range defined in the formula for the given criteria to perform the desired operation.
Display Respective ValuesVBA on this file looks at the values in the range and show their corresponding values on the target or destination tab.
Indirect FunctionChange the value in the yellow cell and see how the column values respond accordingly with the use of INDIRECT function.
Extract and Display File NamesMacro on this file gets and displays he file names in the selected drive and folder.
Dynamic Range Pivot Table UpdateMacro updates the pivot table range for dynamically changing ranges.
Totals by Cell ColorVBA to calculate totals by cell color.
Refresh Multiple PivotsVBA code on this file refreshes multiple pivot tales with dynamically changing data ranges.
Compare Two Ranges for ChangesVBA in this file compares two ranges and if they are not identical creates another file indicating the cell in red where change occurred.
Email File SenderVBA on this file emails the named files on the list from their original location to their corresponding email addresses on the list.
Create Access Table from within ExcelClick on the button on the tab and it will create an Access file and append the data on the tab to a table in this Access file.
Extract Year from TextOn this file GetYear function which is a user defined function acting like another built-in Excel function extracts the year from a text in a selected cell where ever that year is mentioned within the text. Using string functions in this case don't help unless you know where that year is located in the cell.
Combo Box Auto Feed for SelectionsVBA code in this file feeds the associated items form the raw data set per the selection in the first column. On the properties of the Combo Box object, the linked range properties is left blank and it is populated dynamically after the selection is made in the first column. To make sure that code works after opening up the file make a selection in the first column in any of those boxes then check the items that get displayed on the associated drop down box positioned next to that selection. On file open when you get a prompt click yes to continue.
Delete Rows Per CiteriaVBA in this file deletes all the rows designated as Delete. This is done without manually filtering for the criteria and carefully deleting only those rows from the range and gives flexibility if this operation is to be done repeatedly after each load of new data set. 2nd tab is the original data set. On file open when you get a prompt click yes on it.
To create a personal ribbon with only the desired menu items available.
Excel XLOOKUP ExampleThis file has various working examples to illustrate how to use XLOOKUP function instead of VLOOKUP which offers a whole lot more power and versatility for spreadsheet based analysis.
This Excel file helps with mortgage decision in the process of buying a house by running various combinations of scenarios
This Excel data set compiles the ultimate movie statistics database.
This Excel file compiles a dedicated laptop information by the manufacturer, model, RAM, CPU and GPU type, storage capacity, memory and price in Euro.
This file is used to track a weight lifting and strength workout routines based on high volume training principle on a weekly basis. Macros run weekly updates.