• If you would like to post, please check out the MrExcel Message Board FAQ and register here.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
smozgur

XL2BB - Excel Range to BBCode 2.0

No permission to download
Excel Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
  6. 2007
Excel Version (Mac)
  1. 365
Excel 'mini-sheet' in messages - XL2BB

Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual data with formulas and extra information in an Excel-friendly format.

XL2BB captures the cells in the selected range and converts it to a special BB code hash that you can paste into a post. The resulting mini-sheet can be copied and pasted back to an Excel worksheet with formulas and formatting that provides a simple way to exchange sample range between the post and Excel application.

Installation
  • Uninstall the previous version of XL2BB if any.
    • Launch Excel, click the File tab, click Options, and then click the Add-Ins category.
    • Find XL2BB add-in in the Add-ins list and get the installation location in the Location column.
    • In the Manage box, click Excel Add-ins, and then click Go.
      The Add-Ins dialog box appears.
    • Deselect the XL2BB add-in in the Available Add-ins box, click OK and quit Excel.
    • Go to the add-in location and delete the xl2bb.xlam file.
    • Continue installing the new version by following the instructions below.
  • Download the add-in.
  • Launch Excel, click the File tab, click Options, and then click the Add-Ins category.
  • In the Manage box, click Excel Add-ins, and then click Go. The Add-Ins dialog box appears.
  • Click the Browse button to locate the add-in, and then click OK.
Note:
If you have difficulties installing the add-in, please take a look at this amazing article from Jon Peltier - Install an Excel Add-in.

Alternative Installation: Excel Startup folder: XLSTART

You can use the startup folder to load Excel add-ins.
  • Uninstall any previous version of XL2BB.
  • Launch Excel, go to VBA (Alt F11), and open Debug / Immediate window (Ctrl + G). Copy and paste the following in the debug window and press Enter.

    print Application.StartupPath

    This will print the XLSTART folder path. Although it would be different for different Excel versions, It looks like the following for Excel 365:

    Windows:
    %AppData%\Microsoft\Excel\XLSTART

    Mac:
    ~/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/Excel

    Copy this path to be used in the next step and quit Excel.
  • Go to the XLSTART folder and copy and paste the add-in file (xl2bb.xlam) here.
Usage

Click on the custom ribbon tab called Xl2bb where you can find Xl2bb settings and action buttons.

Xl2bb Ribbon Tab

Xl2bb Ribbon Tab


Make sure to select the options such as Formulas, Conditional Formatting, Data Validation, and Named Ranges you'd like to include with the mini-sheet. The selected options will be used to generate the output and can be changed anytime.

Select additional information

Select additional information


Open your workbook and select the range that you want to include in your question, then click the Mini Sheet button in the Capture Range group.

Copy the selected range as a mini-sheet

Copy the selected range as a mini-sheet


Note:
For easy access, you might consider adding the Mini Sheet command button on the QAT by right-clicking on the command button and select "Add to Quick Access Toolbar".

Add to Quick Access Toolbar

Add to Quick Access Toolbar

Table Only button generates table view without row and column headers and formulas. This is useful if you'd like to provide tabular data only. Table content can be copied and pasted back to a worksheet by using the copy button at the bottom of the table.
Note:
Since XL2BB v1.2.8, you can also use the cell context menu button to capture the selected range to bypass the additional information dialog. Simply right-click on the selected range and click XL2BB Capture Range which is the last context menu command button. This shortcut will use the last used additional information options to create XL2BB code.

Cell context menu button

Cell context menu button

Note:
Since XL2BB v2.0, you can select multiple areas to create separated mini-sheets. This is useful if you'd like to post separate ranges by excluding the unrelated rows and/or columns without having to hide them.

As soon as the following confirmation message shows up, the generated XL2BB code will be saved in the clipboard as ready to be pasted to the post.

Saved in the clipboard

Saved in the clipboard

Note:
You can check the Hide Confirmation Dialog option in the Xlbb ribbon tab to suppress the confirmation message that will be also used as the default choice next time.

Now go to the board and paste the clipboard content right after the question text.

XL2BB code in the message

XL2BB code in the message


Click preview to see the actual mini-sheet.

Message preview

Message preview

Note:
It is important to not modify the auto-generated code to allow us to maintain XL2BB code portability between forum software in the future. Please write your ideas to us instead of altering the code, we will consider adding it as long as the implementation is possible.

Cells containing formulas are indicated by a yellow triangle in the top left corner. Hover the mouse pointer on these cells to see a quick tooltip showing the formula. Click on the cell to show the actual formula in the formula bar below the workbook name. The formula will be also copied to the clipboard with this action.

Formula cells

Formula cells

Note:
Spilling ranges containing dynamic array formulas are also highlighted on mouse move on the spilling ranges as well as indicated as green in the Cell Formulas table.

The Copy to Clipboard button in the top left corner is the reverse XL2BB (BB2XL) action that copies the entire range from the post to the clipboard. The data can then be pasted to the same top-left cell to Excel, allowing to get the same range with elements such as formulas, formatting (not as conditional formatting as in Excel but current formatting as you can see in the post). Only values and formatting can be copied by clicking on the button with the Alt key pressed.
Note:
Array formulas won't be pasted as array formulas, so those formulas must be re-entered by using CTRL + Shift + Enter in Excel. Similarly, Conditional Formatting rules, Named Ranges, Data Validation, and Tables must be re-defined in Excel as well.

BB2XL - Reverse XL2BB

BB2XL - Reverse XL2BB

Note:
Hold Alt key pressed when you click on the Copy to clipboard button to copy only values and formatting. This is useful when you just need to grab data and formatting without formulas.

Sample XL2BB Mini-Sheet

There are two mini-sheets in the following example representing base data and associated reporting ranges in an Excel workbook.
XL2BB v2.0.0 Demo.xlsx
ABCDEFGHIJK
1DateCategorySubCategoryAccountPayeeExpense AmountCategoriesSub CategoriesAccountsPayees
21/4/2021Home/RentLawn MowingDebit CardBest Lawn Mover85.00Car/AutoElectricityCashBest Food
31/5/2021Food/GroceriesRestaurant/Fast foodCredit CardNice Food7.44DepartmentalFurnitureCredit CardBest Lawn Mover
41/5/2021Food/GroceriesGroceriesCredit CardSupermarket19.03Food/GroceriesGas/HeatingDebit CardElectric Company
51/5/2021DepartmentalFurnitureCredit CardFurniture Company241.24Home/RentGasolineFurniture Company
61/6/2021Food/GroceriesGroceriesCredit CardSupermarket125.05UtilitiesGroceriesGas Company
71/7/2021UtilitiesInternetDebit CardPhone Company60.00InternetGas Station
81/7/2021Food/GroceriesGroceriesCredit CardSupermarket38.48Lawn MowingNice Food
91/12/2021UtilitiesGas/HeatingCashGas Company41.49MobilePest Controller
101/17/2021Home/RentPest ControlDebit CardPest Controller110.00Pest ControlPhone Company
111/18/2021Food/GroceriesRestaurant/Fast foodCashBest Food16.73Restaurant/Fast foodSupermarket
121/19/2021Car/AutoGasolineCredit CardGas Station21.13
131/21/2021Car/AutoGasolineCredit CardGas Station13.73
141/22/2021UtilitiesMobileDebit CardPhone Company75.30
151/25/2021UtilitiesElectricityDebit CardElectric Company66.20
16Total920.82
Jan 2021 Expenses
Cell Formulas
RangeFormula
F16F16=SUBTOTAL(109,'Jan 2021 Expenses'!$F$2:$F$15)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F14Cell Value>100textNO
F2:F14Cell Value<20textNO
Cells with Data Validation
CellAllowCriteria
B2:B15List=list_category
C2:C15List=list_subcategory
D2:D15List=list_account
E2:E15List=list_payee

Table Only Output
DateCategorySubCategoryAccountPayeeExpense Amount
1/4/2021Home/RentLawn MowingDebit CardBest Lawn Mover85.00
1/5/2021Food/GroceriesRestaurant/Fast foodCredit CardNice Food7.44
1/5/2021Food/GroceriesGroceriesCredit CardSupermarket19.03
1/5/2021DepartmentalFurnitureCredit CardFurniture Company241.24
1/6/2021Food/GroceriesGroceriesCredit CardSupermarket125.05
1/7/2021UtilitiesInternetDebit CardPhone Company60.00
1/7/2021Food/GroceriesGroceriesCredit CardSupermarket38.48
1/12/2021UtilitiesGas/HeatingCashGas Company41.49
1/17/2021Home/RentPest ControlDebit CardPest Controller110.00
1/18/2021Food/GroceriesRestaurant/Fast foodCashBest Food16.73
1/19/2021Car/AutoGasolineCredit CardGas Station21.13
1/21/2021Car/AutoGasolineCredit CardGas Station13.73
1/22/2021UtilitiesMobileDebit CardPhone Company75.30
1/25/2021UtilitiesElectricityDebit CardElectric Company66.20
Total920.82

Watch Video
Watch the video for XL2BB instructions.
(Thanks to Bill Jelen for creating this video!)

Known XL2BB issues
Please read the following threads about known XL2BB issues and solutions.
Add-in Short Name
XL2BB
Author
smozgur
Downloads
3,126
Views
17,051
First release
Last update
Rating
4.43 star(s) 7 ratings

More Excel articles from smozgur

Latest updates

  1. v2.0 - User interface update and multiple range selection feature

    New features: Multiple areas can be selected in the same worksheet to create separated...
  2. v1.2.9 - Bug fixes

    Bug fixes: Fixed Excel 2007 incompatibility - Restored the XL2BB buttons disappeared in v1.2.8...
  3. v1.2.8 - Bug fixes, and new features added.

    Bug fixes: Data validation in merged cells (#1)- There is no applicable fix since data...

Latest reviews

Can we also capture actual Result(value) and Cell Number Formats?

For example, cell A1 has formula '=1500*0.15784' where the result value will be '236.76' and cell number format is '#,##0', therefore the result displayed on the table is '237'.
Resolved the installation issue and the Add-In is now working great...really a nice and effective Add-In product...keep up the great work!
smozgur
smozgur
Glad it is up and running now. Thanks for the feedback!
Unfortunately it doesn't seem to be working with Excel for MAC. I get the following error:

Run-time error '1004': Method 'Type' of object 'Validation' failed.
Excellent tool. Glad you have fixed the '2 squares' issue. I hope all posters now use this as it makes it so much easier to understand the problem/solution.
smozgur
smozgur
Thanks for the feedback and review.
Excellent feedback from smozgur, constant updating to accommodate the users. Works nicely.
smozgur
smozgur
Thanks for the review and glad you find it useful.
Love it!

Only thing I saw that still an issue for me is Named Ranges never seem to get picked up.correctly picked up. Does it only apply if they are attached to the Range highlighted?
smozgur
smozgur
Thank you for your review, @CSmith.

In the first version, named ranges were certainly an issue. However, I made a really radical change to make it work in v1.2.5. It is supposed to apply all named ranges used in formulas (formulas in the selected range). I am going to send you a private message, that would be really great if you could share a sample workbook with me that you are having trouble with this.

Thank you!
I searched all over this page for a link to download the XL2BB add-in, and didn't realize that's what the orange Download button was for in the top right corner of the page. It would help to explicitly say "Download the XL2BB Add-In" on that button, and also to have one or more links right in the article.
smozgur
smozgur
Thank you for your review, @Jon Peltier. I just updated the article to include the download link as the first item in the installation section as you recommended.
Renaming the button requires customization on the software, but it is a brilliant idea. I will include this one to our board suggestions list as it will really help.
Thank you!

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top