View Source code Microsoft Excel

Question

Is there an equivalent to view source code in microsoft excel for either the entire excel workbook or possibly for individual cells?

Ideally I would like a way to view a list of every possible option for Microsoft Excel. It appears a lot of the options are available under "Format Cell".

Solution 1

I'm not totally sure what you require, despite my question in the comments but, it would appear you want to know about a cell (it's properties).

Since your comments mention inspect element and firebug I will assume you have javascript knowledge (and therefore programming) and want to understand what properties the cell has (like the concept of a class and properties).

One way to acheive this would be to use VBa

Sub dothis()
Dim rng As range
Set rng = range("A1")

Dim s As String
s = "myBreakPoint"

End Sub

In this example, in cell A1, I added some formatting to the cell. I then put a break point in the VBa, where it shows myBreakPoint. I then highlighted the code range("A1"), right click, add watch. I can now see the properties of this cell.

In this screen shot, it's showing the font of the cell:

Solution 2

Excel does not have a built-in command to display all settings for a cell's content/formula/formatting.

This is a complex topic and there are several ways to provide information about a cell or sheet or workbook.

Formulas - From Excel 2013 and up there is the FormulaText() function that can be used to display the formula a particular cell uses.

Ctrl-` (that is the key on the left of the 1 key on a US keyboard) - This will switch the sheet display to formula view, so instead of the RESULT of formulas, you see the actual formulas inside the cells.

There are a number of spreadsheet auditing tools available. Some of them are commercial, some of them are free.

Excel MVP Dave Brett is the author of Mappit, which is free

Excel 2013 and up has the add-in "Inquire" built-in, which needs to be activated via the Excel Options Com Add-ins before it can run and will provide more info about your spreadsheet than you will ever want to know. If you want to know "every possible option for Microsoft Excel" then that's your starting point.

Comments

[Dave]

You don't explain why you want this, so I'm guessing it's to give yourself a better understanding of what it is doing. If you want to see what is going on, you can record macros to see the code which instructs Excel. If it's for layout purposes then you can migrate the tables to other programs /sources (HTML, MS Word etc). Or you can learn to use VBa. But, like 'view source' it will only show you the rendered HTML, not the actual nitty gritty of the server side or how the browser interprets the CSS etc

[William]

@Dave Ideally I want something similar to inpsect element or firebug. There are a decent number of options for a cell it would be nice to have a way to at minimum list all the ones affecting a current cell.

[William]

This is quit close for what I'm looking for. I haven't gotten this to work just yet. I'm new to visual basic but I thought Stop was the way to trigger a breakpoint. Do you possible have developer mode enabled? I'm on excel 2016. This is what I get i.stack.imgur.com/LQRM9.png

[Dave]

Can you see where I have the red cirlce in the image. Click in that position to add a break point

[Dave]

Also, add the code to ThisWorkbook, not sheet 1. To run, simply press the run (green arrow)

[William]

It looks pretty much identical except expressions is blank i.stack.imgur.com/xjYl2.png I added a Cyan color to the workbook to insure the code is running

[Dave]

You need to add the watch, as Per my post

[William]

Is there a way to generate essential an xml/html version? I think you could loop through the values therotically and create something like "<cell formula='test' DirectDepedents>"

[William]

So it appears Inquire is only available in the pro version prior to 2016. Having 2016 this is not an option. Have you used Mappit I haven't gotten it to work just yet.

[Dave]

You could write your own parser and iterate through all the properties children

[William]

Yes I'm terrible at Visual Basic Applications but I think it could work.

[brettdj]

@william you will find mappit in my profile. :)