How do you use Excel formulas and functions to analyze software inventory data?

Using formulas effectively can transform raw data into powerful insights. For instance, by combining SUM and IF, you can calculate the total cost of only active licenses, offering a clear view of current cost/usage. Similarly, using AVERAGE with conditional statements helps in understanding average usage trends, crucial for optimizing software allocation.

Excel for me is a middle stage between raw data and powerBI reports and analysis. At this stage, I can collect transform, and analyze data by the vendor, application, region, and application field of use. Data conversion functions allow me to consolidate data from different sources. Logical and math functions help to understand the core indicators of license usage.

Basic functions and formulas can help to clean and bring the data into the proper form for analysis. IF- In simple terms, you use an IF formula to ask Excel to test a certain condition and return one value or perform one calculation if the condition is met, and another value or calculation if the condition is not met TRIM- There are several ways to remove unwanted spaces in Excel, with the TRIM function being the easiest one AND&OR - These are the two most popular logical functions to check multiple criteria. The difference is how they do this: AND returns TRUE if all conditions r met, FALSE otherwise. OR returns TRUE if any condition is met, FALSE otherwise. Before proceeding, utilize these few functions to assess the quality of your data.

Excel is a powerful tool for analyzing software inventory data. By employing formulas like SUM, COUNTIF, and AVERAGE, you can quickly calculate software quantities, identify license compliance issues, and determine software usage patterns. PivotTables offer a dynamic way to summarize and analyze data, revealing trends and insights. Additionally, functions like VLOOKUP and INDEX-MATCH can be used to match software information with other datasets, providing comprehensive analysis and reporting capabilities.

Knowledge of basic and advanced Excel formulas is essential for SAM analysts who prepare and analyse compliance reports on a daily basis. However, with the emergence of AI-based code generators that can create scripts or macros for data manipulation (depending on how effectively we can communicate the detailed step-by-step requirements), the importance of learning Excel formulas may be overshadowed by the ability to use AI. While Excel formulas were once a must-have skill, now be more of a good to have skill for a SAM consultant.

Lookup functions allow me to compare data by different parameters. I can also create formulas that allow me to filter and compare information at the same time. This is very helpful when you need to prepare information for the pivot tables.

VLOOKUP allows you to search for specific software items in your inventory, compare data across different sheets, and retrieve details like vendor, version, license type, etc. For example, VLOOKUP can find the vendor of a software based on its ID.

VLOOKUP (Fuzzy lookup) function is a best friend for a SAM analyst , especially when they don't have access to a SAM tool with a robust raw signature normalization repository.

With the introduction of XLOOKUP, it has become even more convenient for us to look through data. Specifically, I love to compare inventory data between multiple data sources e.g., last inventory data for hardware assets can be different within multiple data sources like Antivirus, Active Directory, other inventory tools like SCCM etc. With the introduction of ability to look through data from top to bottom as well as bottom to top is a game changer when dealing with data duplicates.

The text functions allow to consolidation of information about different versions of the same software. It is very helpful if you need to understand how many licenses of applications by the major version you have. In this case, information about software builds isn`t important.

A significant portion of the analytical process involves preparing and sanitizing data to ensure its reliability and accuracy. Text functions like TRIM, CLEAN, and SUBSTITUTE are essential in this phase. They help in removing inconsistencies and formatting issues, which are common in data collected from various sources. For example, TRIM removes extra spaces that could cause issues in sorting or searching, while CLEAN gets rid of non-printable characters that might have come from system exports or user input errors.

One good example here is when backdating Red Hat subscriptions in Excel, you can use the DATE and EDATE functions. If a subscription starts on January 1, 2023, and you need to calculate a one-year backdated start, use =EDATE(install_date, -12). This subtracts 12 months, giving the backdated start date. To find the duration between the original and backdated dates, use =DATEDIF(date_1, date_2, "d") for the total days.

These functions I use when I need to process the information that is dependent on EOL end EOS information. It helps to forecast license needs in the future. These functions also can be helpful for some accountant operations.

When working with data around metering period of applications, formulas like DATE=(year,month,date) helps set the dates to desired formats. In my experience, while comparing data I have experienced that different applications/tools have different format for dates and while comparing between multiple dates, it becomes important to standardize.

I've come to love Pivot tables when we had to do the data analysis for an SAP audit mainly manually. Pivot tables are without a doubt one of the most potent functions when it comes to Excel data analysis. You can easily identify irregulaties and once you do, you can click the dataset and voilá - Excel creates a new spreadsheet with all data relevant for this dataset. You can also use it to group, filter and aggregate your data and then create charts and graphs directly from the Pivot table. Once your input data changes, the Pivot table can change automatically with it, so do the charts you created on the Pivot table. I'll admit, that at first using the Pivot table is not intuitive. However, once you use it, you'll learn to love it.