When connecting to a datablock in a S7-1200 or S7-1500 CPU, optimized block access must be disabled. By turning off optimized block access, the data elements in the datablock got their own unique address.
In TIA portal, right click on the datablock you want to access from S7 Excel Connect and select “Properties”.

Go to the “Attributes” tab and untick “Optimized block access”. Click “Ok”.

Now open the datablock and add data elements. The “offset” column shows the data element’s unique address and must be used in S7 Excel Connect to get a datablock value.

Download the changes to the CPU.