Thanks, Anaconda Developers for adding R functionality into Online Excel with Anaconda R Code!
I like the idea but haven’t got the hang of how to efficiently incorporate R code into an Excel workflow. It doesn’t seem like the user is able to choose cells at will and specify that those cells be designated cells to run R code in. Instead, it seems like each time the user wants to run new R code that the user needs to select the “New Code” button and then type in code.
It also seems faster to just select cell data outside the R code cell by:
REF("CELL#START:CELL#END) rather than clicking and dragging.
Not sure thus that I’m using Anaconda R Code as intended; there seemed to be limited examples of how to use it besides the 1 web page and a short video. Are there other videos/links to better understand how to incorporate R in an Excel workflow (or perhaps being planned)?
Mike,
I’m glad you are excited about R in Excel! As the developer, I admit the documentation and usage videos are lacking. Sorry for that.
The typical way to create a new R cell is with the “New Code” button. The other way to create a new code cell is to type =ANACONDA.CODE( in the cell and press Enter. That will trigger the same behavior as clicking the “New Code” button.
As for manually typing REF( instead of clicking the “REF” button, that is absolutely fine. In fact, if you manually type, code completion will pop up with any defined Tables in your workbook, which is usually the best way to refer to them instead of clicking and dragging.
I don’t think there is an “intended” way to use Anaconda R Code. You can mix and match R cells with Python cells and regular Excel formula cells. If you spill the result to the Excel grid, the results become normal Excel values. As inputs change, Excel will update dependent cells, so you never need to worry about stale values.
Thanks, @Jim_Kitchen, for such a prompt and thorough reply!
That all makes sense. I’ll try the =ANACONDA.CODE idea. Would it be possible in the future to add the “New Code” command to the right-click menu on cells you select? That would seem even easier and more intuitive and mimic other Excel functionality.
@Mike16 A right-click menu would be a very nice feature! Unfortunately, Excel only exposes a limited set of functionality for add-ins, and modifying the right-click context menu of cells is not one of them.
So we’re stuck with manually entering the custom function =ANACONDA.CODE() or using a button in the right panel to trigger an action.
If you have any other usability ideas, please share them! I want Anaconda Code to be as useful as possible given the constraints imposed on add-ins.
I have one additional question: how does one successfully create a R dataframe object from Anaconda Code in Excel? I can’t seem to create a dataframe object from which to apply additional R functions on.
For example, I imported the mtcars dataset as an excel file (mtcars.xlsx) into Excel, created an Anaconda code cell in the same excel workbook, and then attempted to apply a simple function such as:
str(REF(“B:L”)) #B:L are the columns in the mtcars .xlsx file that I loaded
But that did not run.
From reviewing the REF instructions it looked like perhaps instead the user should use the built in function “to_dataframe” so I next tried:
str(to_dataframe(REF(“B:L”)))
but that also didn’t seem to run.
I’d appreciate any clarification you could provide on how to create a R dataframe object from an Excel spreadsheet using Anacode Code
Thanks!,
Mike
*Addendum:
I apologize, @Jim_Kitchen , I think I figured out the problem:
Users can’t use “Column:Column” notation to specify the range in the built in “to_dataframe” function in Anaconda Code.
Instead, users should use “Cell:Cell” range notation to specify the range for the “to_dataframe” function. When I do that, Anacode Code correctly converts the Excel spreadsheet values to a dataframe.
Now that I see how to create a dataframe, I do have one additional question:
How does one enlarge the graphical output produced by Anaconda Code and displayed in an Excel code cell? For instance, I used R in Anaconda Code to produce a nice bar chart, but Anaconda Code shrunk the color bar chart and displayed the tiny chart inside of an Excel cell. I’d prefer to have a way to then enlarge (and optimally save the output of plot).
@Mike16 Glad you figured it out! We definitely need support for Column:Column or an error message explaining the issue.
Note that REF does support named Tables. If you convert a range into an Excel Table named MyTable, you can refer to it as REF("MyTable[#All]").
As for the tiny plot, if you right-click → Picture in Cell → Create Reference you will get a moveable picture of the plot that you can resize and place wherever you want. You can also right-click the larger picture and choose Save As Picture.
Thanks, @Jim_Kitchen! Do those right-click options for pictures in cells work for Office 365 that I’m using? I don’t seem to get any of those options when I right click the picture (ggplot2 object). Right-clicking does give me an option to “Show Preview” which nicely enlarges the plot (and which I can then resize the preview image to any size I want), but I don’t seem to be able to save or move that image anywhere and that larger preview image disappears after I select another cell.
Oh, I didn’t realize you were using the online version of Excel. It is definitely missing some key features like chart references. Note that this is also a problem for Python in Excel, which outputs charts the same way as Anaconda Code.
Unfortunately, there until Microsoft adds a fix, there isn’t a good solution other than making the output cell larger by changing the size of the row and column. Of course, this affects all cells in the row and column, so it’s not ideal. But it’s the only solution at the moment.
And there doesn’t seem to be a mechanism to save the image, which is strange. That is easy on the desktop version of Excel. I don’t know why Microsoft wouldn’t have included it for Excel Online.
Thanks, @Jim_Kitchen, that makes sense. It’s been frustrating in the past when Microsoft hasn’t always included desktop features in their online 365 version of their apps.
Please post if in the future you end up creating more of a wiki site for Anaconda Code and where to watch for future Anaconda Code updates.
