Excel has a very useful feature called “cell comments” using which you can add comment to a cell. This is a very good way to gather remarks and review comments when a workbook is shared with colleagues and others. But what if you have typed a ton of cell comments and now want a way to extract them and do something with that data?
Well, no need to select each comment and copy the contents. You can use a simple user defined formula (UDF) to do just that. Here is a one line formula that I wrote.
Function getComment(incell) As String
' aceepts a cell as input and returns its comments (if any) back as a string
On Error Resume Next
getComment = incell.Comment.Text
End FunctionTo make it work,
To make the getComment() UDF work for you, you need to install it first. Here is a step by step guide if you dont know how to install user defined formulas in excel.
The simple and easy version:
- Copy the above vba code
- Open a new excel file
- Right click on any sheet tab name and select “view code” option.
- Don’t be scared of the VBA editor. Take a deep breath.
- Click on “Insert” menu option and select “Module”
- Paste the code in the new module
- Save the workbook and start using the getComment() formula.
The lengthy and more stable version:
- Same as first 6 steps above.
- Press ctrl+s to save the work book. Specify “excel add-in” as the file type.
- Now, go back to excel and install this new add-in
- That is all. Now you can use getComment() formula in your workbooks.
Related: How to change the shape of cell comments from rectangle to any other shape
8 Responses to “Create a Combination Chart, Add Secondary Axis in Excel [15 Second Tutorial]”
[...] Select the “daily completed” column and add it to the burn down chart. Once added, change the chart type for this series to bar chart (read how you can combine 2 different chart types in one) [...]
[...] set the height series to be plotted on secondary axis. Learn more about combining 2 chart types and adding secondary axis in [...]
[...] Excel Combination Charts – What are they? [...]
[...] To show the years, I have used another dummy series and plotted it on secondary axis (related: how to add secondary axis?) [...]
Thanks for this one!
[...] Choisissez la colonne « Daily Completed » et ajoutez-la au graphique. Une fois ajoutée, changez le type de graphique pour cette série à histogramme (lisez comment combiner 2 types de graphiques en un : combine 2 different chart types in one) [...]
How do i create a chart that has negative numbers on axis x and y and plot them correctly? I cannot seem to understand how to do this, please help.
Thanks.
Nat
You can also plot 2 or more Y axes in Excel using EZplot or Multy_Y from Office Expander.com
There is a demo version to try.
Cheers.