On this page one can find randomly selected Excel macros to automate spreadsheet tasks, file operations, calculation logic and data flow control etc.  I pick these files from my work projects, tasks and assignments where I had to use them when I was wrestling with challenging data problems. 


I place either directly from my work files as a scaled down version to illustrate the function or formula or place here a re-created version of the topic to explain the concept on a working file.


If the sample file is created originally by another site, person or company on title section this situation is clearly indicated and referenced.


XLOOKUP Function
Samples 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.
Microsoft Excel sheet [382.9 KB]
Active Cell Coloring
Macro changes the active cell color to yellow and when left it reverts back to its original color.
Microsoft Excel sheet [27.0 KB]
Discount Function
User defined discount functions applied as a regular built-in spreadsheet function to calculate the discounted amount.
Microsoft Excel sheet [20.5 KB]
TVM - Time Value of Money Calcs
Some basic financial calculations are provided on this example.
Sample Financial Calcs.xls
Microsoft Excel sheet [58.0 KB]
INDEX MATCH is a lot more powerful function than VLOOKUP.
Microsoft Excel sheet [14.6 KB]
Excel Tips and Tricks
Many practical and useful Excel tips and tricks can be found on this document.
Adobe Acrobat document [794.0 KB]
Absolute to Relative Value Conversion - From OzGrid
This 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.
Absolute_Relaltive Conversion Macro.xls
Microsoft Excel sheet [38.5 KB]
Array Formula Example
Array 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.
Microsoft Excel sheet [10.6 KB]
Display Respective Values
VBA on this file looks at the values in the range and show their corresponding values on the target or destination tab.
Microsoft Excel sheet [38.0 KB]
Indirect Function
Change the value in the yellow cell and see how the column values respond accordingly with the use of INDIRECT function.
Microsoft Excel sheet [129.3 KB]
Extract and Display File Names
Macro on this file gets and displays he file names in the selected drive and folder.
Microsoft Excel sheet [19.0 KB]
Dynamic Range Pivot Table Update
Macro updates the pivot table range for dynamically changing ranges.
DynamicRange Pivot Sample.xls
Microsoft Excel sheet [755.5 KB]
Totals by Cell Color
VBA to calculate totals by cell color.
Microsoft Excel sheet [27.4 KB]
Refresh Multiple Pivots
VBA code on this file refreshes multiple pivot tales with dynamically changing data ranges.
Microsoft Excel sheet [7.8 MB]
Compare Two Ranges for Changes
VBA in this file compares two ranges and if they are not identical creates another file indicating the cell in red where change occurred.
Microsoft Excel sheet [63.0 KB]
Email File Sender
VBA on this file emails the named files on the list from their original location to their corresponding email addresses on the list.
Microsoft Excel sheet [22.9 KB]
Create Access Table from within Excel
Click 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.
Microsoft Excel sheet [47.0 KB]
Extract Year from Text
On 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.
Microsoft Excel sheet [31.0 KB]
Combo Box Auto Feed for Selections
VBA 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.
Combo Box Selections - Copy.xls
Microsoft Excel sheet [56.4 KB]
Delete Rows Per Citeria
VBA 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.
DeleteRows - Copy.xls
Microsoft Excel sheet [885.3 KB]
To create a personal ribbon with only the desired menu items available.
Custom Excel Menus.txt
Text document [2.9 KB]
Excel XLOOKUP Example
This 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.
Microsoft Excel sheet [382.9 KB]

 ADVANCEd   Excel                                     Access                 databases                                       VBA macros                                       SQL scripts                                       BI Reports                                         data   integrity                                          big data                                             data science                                   data mining                                     data mapping                                   data profiling                                 data   visualization                                   data modeling                                 financial     analysis                                           SCORE CARDS                                     BENCHMARKS                                     PERFORMANCE   KPI METRICS

Print Print | Sitemap Recommend this page Recommend this page
© Ray Erden

This website was created using 1&1 IONOS MyWebsite.