Handy Tips for TVmap in Excel users ~ Part one

Tip 1. Easily export your workbook to Excel so that it can be opened outside of TVmap.
Here’s the scenario: You have a TVmap workbook and want to share it with your client. The problem is they don’t have access to TVmap so if they open the workbook it won’t link to the audience and rate data and will display ‘#NAME?’ instead. The client needs a static view of the workbook.

What to do: Select ‘Export’ from the TVmap tab in the TVmap ribbon to create a static version of the workbook. A duplicate of the workbook will display as ‘Book 1’ and allow you to save it with all the values rather than formulas.

Export to Excel screenshot

Tip 2. Using the ‘$’ shortcut when copying formulas.

Here’s the scenario: You’re creating a TVmap workbook using TVmap functions. Rather than re-enter the function into each cell you can update the formula to allow you to copy it down and across and populate with the correct information – saves a heap of time!

What to do: When creating your own TVmap workbook, use the ‘$’ symbol in the formula to create a fixed cell reference. This will enable you to copy formulas into other cells.

Another tip: Rather than typing the ‘$’ symbol into the formula, use the F4 key as a shortcut. One hit of F4 = ‘$’ on both column and row, two hits of F4 = ‘$ on the row and three hits of F4 = ‘$’ on the column.

Shortcut to copy formula screenshot

Tip 3. Save functional workbooks with the file extension .xlsm
TVmap workbooks should be saved as ‘macro enabled’ with the file extension .xlsm - this will ensure that the workbook will fully function when reopened within TVmap in Excel.
Here’s the scenario: You have run a TVmap workbook, e.g. Reach Curves, and would like run it again next week to refresh audience data. Rather than starting a new Reach Curve from the template list and re-entering all the requirements, you can save it as an .xlsm file type. This ensures the workbook will retain functionality when re-opened, using TVmap Excel/Open existing.

Save as .xlsm extension screenshot

Tip 4. Automatic station update when market is selected.

Here’s the scenario: You are creating your own workbook and require the station fields to update when you change market from Sydney to Melbourne to allow you to quickly return results with minimal work. For example the broadcaster is Seven, the market is Sydney = station ATN7. When you change the broadcaster to Nine the station automatically updates to TCN9, if you then change the Market to Melbourne the station will update to GTV9.

What to do: Within a TVmap workbook, you can link the station field to the Broadcaster and Market so that if either are changed the station changes automatically. This applies to both primary and secondary stations. To do this, use the pre-defined TVmap function called 'StationName()'  rather than the ‘Station’ drop down selection.

Station update screenshot

I hope these are really useful for you. Please check out my next post for three more time-saving tips, and do get in touch if you have any comments or questions.

Regards, Leanne
Phone: +61 2 9657 1388
Email me