- Excel Version
- 365
- 2021
- 2019
- 2016
- 2013
- 2010
- 2007
- Excel Version (Mac)
- 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
Alternative Installation: Excel Startup folder: XLSTART
You can use the startup folder to load Excel add-ins.
Click on the custom ribbon tab called Xl2bb where you can find Xl2bb settings and action buttons.
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.
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.
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.
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.
Now go to the board and paste the clipboard content right after the question text.
Click preview to see the actual mini-sheet.
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.
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.
Sample XL2BB Mini-Sheet
There are two mini-sheets in the following example representing base data and associated reporting ranges in an Excel workbook.
Table Only Output
Watch Video
Watch the video for XL2BB instructions.
(Thanks to Bill Jelen for creating this video!)
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.
- Extract the xl2bb.xlam file from the download and take note of where you locate it.
- 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.
Click on the custom ribbon tab called Xl2bb where you can find Xl2bb settings and action buttons.
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
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
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
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
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
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
Click preview to see the actual mini-sheet.
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
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
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 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Date | Category | SubCategory | Account | Payee | Expense Amount | Categories | Sub Categories | Accounts | Payees | |||
| 2 | 1/4/2021 | Home/Rent | Lawn Mowing | Debit Card | Best Lawn Mover | 85.00 | Car/Auto | Electricity | Cash | Best Food | |||
| 3 | 1/5/2021 | Food/Groceries | Restaurant/Fast food | Credit Card | Nice Food | 7.44 | Departmental | Furniture | Credit Card | Best Lawn Mover | |||
| 4 | 1/5/2021 | Food/Groceries | Groceries | Credit Card | Supermarket | 19.03 | Food/Groceries | Gas/Heating | Debit Card | Electric Company | |||
| 5 | 1/5/2021 | Departmental | Furniture | Credit Card | Furniture Company | 241.24 | Home/Rent | Gasoline | Furniture Company | ||||
| 6 | 1/6/2021 | Food/Groceries | Groceries | Credit Card | Supermarket | 125.05 | Utilities | Groceries | Gas Company | ||||
| 7 | 1/7/2021 | Utilities | Internet | Debit Card | Phone Company | 60.00 | Internet | Gas Station | |||||
| 8 | 1/7/2021 | Food/Groceries | Groceries | Credit Card | Supermarket | 38.48 | Lawn Mowing | Nice Food | |||||
| 9 | 1/12/2021 | Utilities | Gas/Heating | Cash | Gas Company | 41.49 | Mobile | Pest Controller | |||||
| 10 | 1/17/2021 | Home/Rent | Pest Control | Debit Card | Pest Controller | 110.00 | Pest Control | Phone Company | |||||
| 11 | 1/18/2021 | Food/Groceries | Restaurant/Fast food | Cash | Best Food | 16.73 | Restaurant/Fast food | Supermarket | |||||
| 12 | 1/19/2021 | Car/Auto | Gasoline | Credit Card | Gas Station | 21.13 | |||||||
| 13 | 1/21/2021 | Car/Auto | Gasoline | Credit Card | Gas Station | 13.73 | |||||||
| 14 | 1/22/2021 | Utilities | Mobile | Debit Card | Phone Company | 75.30 | |||||||
| 15 | 1/25/2021 | Utilities | Electricity | Debit Card | Electric Company | 66.20 | |||||||
| 16 | Total | 920.82 | |||||||||||
Jan 2021 Expenses | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F16 | F16 | =SUBTOTAL(109,'Jan 2021 Expenses'!$F$2:$F$15) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F2:F14 | Cell Value | >100 | text | NO |
| F2:F14 | Cell Value | <20 | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B2:B15 | List | =list_category |
| C2:C15 | List | =list_subcategory |
| D2:D15 | List | =list_account |
| E2:E15 | List | =list_payee |
Table Only Output
| Date | Category | SubCategory | Account | Payee | Expense Amount |
| 1/4/2021 | Home/Rent | Lawn Mowing | Debit Card | Best Lawn Mover | 85.00 |
| 1/5/2021 | Food/Groceries | Restaurant/Fast food | Credit Card | Nice Food | 7.44 |
| 1/5/2021 | Food/Groceries | Groceries | Credit Card | Supermarket | 19.03 |
| 1/5/2021 | Departmental | Furniture | Credit Card | Furniture Company | 241.24 |
| 1/6/2021 | Food/Groceries | Groceries | Credit Card | Supermarket | 125.05 |
| 1/7/2021 | Utilities | Internet | Debit Card | Phone Company | 60.00 |
| 1/7/2021 | Food/Groceries | Groceries | Credit Card | Supermarket | 38.48 |
| 1/12/2021 | Utilities | Gas/Heating | Cash | Gas Company | 41.49 |
| 1/17/2021 | Home/Rent | Pest Control | Debit Card | Pest Controller | 110.00 |
| 1/18/2021 | Food/Groceries | Restaurant/Fast food | Cash | Best Food | 16.73 |
| 1/19/2021 | Car/Auto | Gasoline | Credit Card | Gas Station | 21.13 |
| 1/21/2021 | Car/Auto | Gasoline | Credit Card | Gas Station | 13.73 |
| 1/22/2021 | Utilities | Mobile | Debit Card | Phone Company | 75.30 |
| 1/25/2021 | Utilities | Electricity | Debit Card | Electric Company | 66.20 |
| Total | 920.82 | ||||
Watch Video
Watch the video for XL2BB instructions.
(Thanks to Bill Jelen for creating this video!)
- Add-in Short Name
- XL2BB
Also, this is the Review area that is supposed to contain reviews, so I suggest asking for help in the Discussion section instead. In fact, there are many other questions and solutions like yours over there.