• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    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 1.2.7

No permission to download
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. 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 add-in location and rename or 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 refer to Jon Peltier's amazing article - Install an Excel Add-in.

Usage
Locate the custom buttons in the custom ribbon tab called MrExcel. For quicker access, you might consider adding the Capture Range command button on the QAT by right clicking on the command button and select "Add to Quick Access Toolbar".

00-ribbon-buttons.png

MrExcel Ribbon Tab

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

01-capture-range.png

Capture Range

Select the information such as Formulas, Conditional Formatting, Data Validation and Named Ranges you'd like to include with the captured range and click Generate Output. Alternatively, use Select All button to include all possible extra information.

02-additional-info.png

Select additional information

Table Only Output 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.

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

03-confirmation-message.png

Copied to clipboard

Note: Click the Hide Confirmation Message toggle button to suppress the confirmation message that will be used as default choice next time.

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

04-paste-bbcode.jpg

XL2BB code in the message and preview

Note: It is important to not modify the auto-generated code to allow us to maintain XL2BB code portability between forum softwares in the future. Please write your ideas to us instead 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. Formula will be also copied to clipboard with this action.

06-formula-tooltip.png

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 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 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 and Data Validation must be re-defined in Excel as well.

07-copy-to-clipboard.png

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 v1.2 Demo.xlsx
ABCDEFGHIJK
1DateCategorySubCategoryAccountPayeeExpense AmountCategoriesSub CategoriesAcccountsPayees
24-Jan-2020Home/RentLawn MowingDebit CardBest Lawn Mover $ 85.00 Car/AutoElectricityCashBest Food
35-Jan-2020Food/GroceriesRestaurant/Fast foodCredit CardNice Food $ 7.44 DepartmentalFurnitureCredit CardBest Lawn Mover
45-Jan-2020Food/GroceriesGroceriesCredit CardSupermarket $ 19.03 Food/GroceriesGas/HeatingDebit CardElectric Company
55-Jan-2020DepartmentalFurnitureCredit CardFurniture Company $ 241.24 Home/RentGasolineFurniture Company
66-Jan-2020Food/GroceriesGroceriesCredit CardSupermarket $ 125.05 UtilitiesGroceriesGas Company
77-Jan-2020UtilitiesInternetDebit CardPhone Company $ 60.00 InternetGas Station
87-Jan-2020Food/GroceriesGroceriesCredit CardSupermarket $ 38.48 Lawn MowingNice Food
912-Jan-2020UtilitiesGas/HeatingCashGas Company $ 41.49 MobilePest Controller
1017-Jan-2020Home/RentPest ControlDebit CardPest Controller $ 110.00 Pest ControlPhone Company
1118-Jan-2020Food/GroceriesRestaurant/Fast foodCashBest Food $ 16.73 Restaurant/Fast foodSupermarket
1219-Jan-2020Car/AutoGasolineCredit CardGas Station $ 21.13
1321-Jan-2020Car/AutoGasolineCredit CardGas Station $ 13.73
1422-Jan-2020UtilitiesMobileDebit CardPhone Company $ 75.30
1525-Jan-2020UtilitiesElectricityDebit CardElectric Company $ 66.20
16
17Total
$ 920.82
Jan 2020 Expenses
Cell Formulas
RangeFormula
F17F17=SUM(fld_amount)
Named Ranges
NameRefers ToCells
fld_amount='Jan 2020 Expenses'!$F$2:$F$15F17
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F15Cell Value<=20textNO
F2:F15Cell Value>=100textNO
Cells with Data Validation
CellAllowCriteria
B2:B15List=list_category
C2:C15List=list_subcategory
D2:D15List=list_account
E2:E15List=list_payee

XL2BB v1.2 Demo.xlsx
ABCDEFGHIJKLMNO
1CategoriesSub CategoriesAccountsPayees
2CategoryTotal%Sub CategoryTotal%AccountTotal%PayeeTotal%
3
Car/Auto
$ 34.86
4%
Electricity
$ 66.20
7%
Cash
$ 58.22
6%
Best Food
$ 16.73
2%
4Departmental $ 241.24 26%Furniture $ 241.24 26%Credit Card $ 466.10 51%Best Lawn Mover $ 85.00 9%
5Food/Groceries $ 206.73 22%Gas/Heating $ 41.49 5%Debit Card $ 396.50 43%Electric Company $ 66.20 7%
6Home/Rent $ 195.00 21%Gasoline $ 34.86 4%Furniture Company $ 241.24 26%
7Utilities $ 242.99 26%Groceries $ 182.56 20%Gas Company $ 41.49 5%
8Internet $ 60.00 7%Gas Station $ 34.86 4%
9Lawn Mowing $ 85.00 9%Nice Food $ 7.44 1%
10Mobile $ 75.30 8%Pest Controller $ 110.00 12%
11Pest Control $ 110.00 12%Phone Company $ 135.30 15%
12Restaurant/Fast food $ 24.17 3%Supermarket $ 182.56 20%
13
14Total
$ 920.82
100%
Total
$ 920.82
100%
Total
$ 920.82
100%
Total
$ 920.82
100%
15
Jan 2020 Reports
Cell Formulas
RangeFormula
A3:A7A3=SORT(UNIQUE(list_category))
B3:B7B3=SUMIF(fld_category,A3#,fld_amount)
C3:C7,O3:O12,K3:K5,G3:G12C3=B3#/SUM(B3#)
E3:E12E3=SORT(UNIQUE(list_subcategory))
F3:F12F3=SUMIF(fld_subcategory,E3#,fld_amount)
I3:I5I3=SORT(UNIQUE(list_account))
J3:J5J3=SUMIF(fld_account,I3#,fld_amount)
M3:M12M3=SORT(UNIQUE(list_payee))
N3:N12N3=SUMIF(fld_payee,M3#,fld_amount)
B14:C14,N14:O14,J14:K14,F14:G14B14=SUM(B3:B13)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
fld_account='Jan 2020 Expenses'!$D$2:$D$15J3
fld_amount='Jan 2020 Expenses'!$F$2:$F$15B3, F3, J3, N3
fld_category='Jan 2020 Expenses'!$B$2:$B$15B3
fld_payee='Jan 2020 Expenses'!$E$2:$E$15N3
fld_subcategory='Jan 2020 Expenses'!$C$2:$C$15F3
list_account='Jan 2020 Expenses'!$J$2:$J$4I3
list_category='Jan 2020 Expenses'!$H$2:$H$6A3
list_payee='Jan 2020 Expenses'!$K$2:$K$11M3
list_subcategory='Jan 2020 Expenses'!$I$2:$I$11E3


Table Only Output
DateCategorySubCategoryAccountPayeeExpense Amount
4-Jan-2020Home/RentLawn MowingDebit CardBest Lawn Mover $ 85.00
5-Jan-2020Food/GroceriesRestaurant/Fast foodCredit CardNice Food $ 7.44
5-Jan-2020Food/GroceriesGroceriesCredit CardSupermarket $ 19.03
5-Jan-2020DepartmentalFurnitureCredit CardFurniture Company $ 241.24
6-Jan-2020Food/GroceriesGroceriesCredit CardSupermarket $ 125.05
7-Jan-2020UtilitiesInternetDebit CardPhone Company $ 60.00
7-Jan-2020Food/GroceriesGroceriesCredit CardSupermarket $ 38.48
12-Jan-2020UtilitiesGas/HeatingCashGas Company $ 41.49
17-Jan-2020Home/RentPest ControlDebit CardPest Controller $ 110.00
18-Jan-2020Food/GroceriesRestaurant/Fast foodCashBest Food $ 16.73
19-Jan-2020Car/AutoGasolineCredit CardGas Station $ 21.13
21-Jan-2020Car/AutoGasolineCredit CardGas Station $ 13.73
22-Jan-2020UtilitiesMobileDebit CardPhone Company $ 75.30
25-Jan-2020UtilitiesElectricityDebit CardElectric Company $ 66.20


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

Known XL2BB issues
Please read following threads about known XL2BB issues and solutions.
Excel Version
365, 2019, 2016, 2013, 2010, 2007
Add-in Short Name
XL2BB
Author
smozgur
Downloads
940
Views
5,873
First release
Last update
Rating
4.75 star(s) 4 ratings

Latest updates

  1. v1.2.7 - Bug fixes

    Bug fixes: Copying partial dynamic array formula range issue fixed. Special keywords used as...
  2. v1.2.6 - Bug fixes

    Bug fixes: Unicode character issue fixed. Excel 2007 missing array formulas fixed.
  3. v1.2.5 - Bug fixes, and new features added.

    Bug fixes: Clipboard copy: API used to avoid "2 Squares" in Windows. Conditional formatting...

Latest reviews

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!

Some videos you may like

This Week's Hot Topics

Top