u/IsEneff

Let’s say you have a list of 100 employees and email addresses as an export from your HR department, and you have to email all 100 of them with some important message like “You have not completed your required compliance course.” The upgraded version of concatenation is using textjoin(). The arguments are textjoin(delimiter, ignore blanks, values). So you could do something like =textjoin(“; “, true, b1:b100) to turn them all into employee1@company.com; employee2@company.com, and so on.

I will also use text join to get a quick list of unique values from a list to copy and paste into an email too. Let’s say I need a list of the supervisors from that same employee list. Since each supervisor can have multiple employees, and I only want to get the a list of supervisors to paste into the body of the email (or in the we email format if I have their email addresses) I could use =textjoin(“, “, true, unique(c1:c100)) to create a unique list of coma separated names.

One of the biggest struggles I face when working with data is finding alternative sources to gather detailed data that is already available in someone else’s Power BI report. Using Power Query, you can connect directly to the data model used in that report.

There are three ways to do this. The easiest is directly from the Power BI report itself. Using the Export option, select Analyze in Excel.

The next method is to use Data > Get Data > Power Platform > Power BI. From there, you can search for the report and either insert it as a table or as a PivotTable.

The final method is Insert > PivotTable > From Power BI.

Pulling the data into a PivotTable is both a blessing and a curse, depending on what you want to do. The measures from the data model carry over into Excel, keeping your calculations consistent with how the semantic model is set up. The drawback is that you cannot create your own calculated fields or measures. What you see is what you get. However, all the relationships remain intact, and you have access to all the tables and columns in the model.
Inserting the data as a table allows you to import it into Power Query or the Data Model. You can also choose to filter the data before it makes its way into your spreadsheet. Furthermore, you can adjust those filters and even add functions by editing the connection properties with DAX. If you need to transform the data or create a measure or calculated field, inserting the data as a table is the way to go.

But why would you need to do this? There are two big reasons for me:

1. I can customize my own personal dashboard in Excel without messing with someone else’s Power BI report.
2. Finding, downloading, and transforming data can be a real pain. Having the ability to pull in reformatted, ready to pivot data from an agreed upon data source is incredibly fast and much easier than it might seem.

One final drawback: You must have permission to access the report or semantic model for this to work. Contributor access will allow you to connect to the data, and the connection uses whatever authentication your Microsoft 365 account is configured with. That also means that if you send the file to someone who doesn’t have access, they may not be able to refresh the connection (oh no!!).
Do with this what you will.

Got a worksheet filled with data, parameters, lookup tables, or helper calculations that you want to keep out of sight? Simply hiding the worksheet is not always the best solution because many users know how to unhide tabs with a right click and a few clicks in Excel.

Excel VBA includes a worksheet property called Visible that supports a setting named Very Hidden. A Very Hidden worksheet does not appear in Excel's Unhide Sheet dialog, making it much harder for users to discover or access without opening the Visual Basic Editor.

To set a worksheet to Very Hidden manually:
Press Alt + F11 to open the Visual Basic Editor.

In the Project Explorer, select the worksheet you want to hide.

Press F4 to open the Properties window if it is not already visible.

Locate the Visible property.

Change it from -1 - xlSheetVisible to 2 - xlSheetVeryHidden.

The Visible property has three possible values:
xlSheetVisible: The worksheet is visible.

xlSheetHidden: The worksheet is hidden but can be unhidden through Excel's Unhide Sheet dialog.

xlSheetVeryHidden: The worksheet is hidden and does not appear in the Unhide Sheet dialog.

You can also set a worksheet to Very Hidden through VBA code:

Worksheets("Config").Visible = xlSheetVeryHidden

This approach is commonly used for configuration worksheets, lookup tables, parameter sheets, Power Query staging areas, and other supporting data that users do not need to interact with directly. It is not a security feature, but it does provide an additional layer of protection against accidental changes.

Recently, my WFM team has increased productivity by leveraging the .Rules function in Microsoft Copilot for Excel. In the Copilot chat window, click the + button and select Create workbook rules. This adds a .Rules tab that stores custom instructions specific to your workbook. It’s a powerful way to create repeatable, code-free automations. Even better, Copilot can write the instructions for you.

When working with a spreadsheet that involves repetitive tasks, such as maintaining a team calendar, performing data analysis, or preparing summary emails, start by using Copilot to produce the output you want. Break the task into smaller steps, just as you would when training a new employee. You don’t need to provide formulas, VBA, or Power Query instructions. Simply describe what you want, review the results, and continue refining them until you get the desired outcome.

Once you’re satisfied with the results, ask Copilot to document the process as instructions and add them to your .Rules tab. The next time you need to perform the task, prompt Copilot to follow those instructions. As your spreadsheet evolves, you can also ask Copilot to update the rules to reflect any changes.

Here’s an example of what those instructions might look like in a WFM scheduling workbook:
Example: Update the workbook for the next scheduling month.
Use this process:
On the ‘Schedules’ sheet, extend the employee schedule blocks from the current last completed week through the end of the next month. Copy the existing weekly block format, formulas, merged cells, and date progression.
Apply the latest recurring schedule rules already reflected in previous months.
On the ‘PTO Calendar’ sheet, use the next month’s columns to create a PTO list like the current month’s list, with Date, Day, Employee, and PTO Note columns. Split combined names into separate rows and keep half-day notes.
Mark the PTO entries on ‘Schedules’ in the matching employee/date cells as merged PTO blocks, matching the PTO formatting used in previous months.
Verify that dates run through the end of the month, PTO entries match the PTO Calendar list, no required schedule cells are missing, and no formula errors appear.