Author: John

Excel to Jira Table

I use a tool at work called Jira. It’s a tool used for issue tracking and project management in software development. I often need to comment on issues in Jira with large tables of numbers to support my findings. The problem is, that making a table in a Jira comment isn’t as easy as copying and pasting it from Excel. The comment system uses its own markup syntax for things like bold, italics, underlines and tables. Copy and Pasting a Table from Excel When you copy a table from Excel and try to paste it into a Jira comment,...

Read More

How To Automate Sorting, Hiding, Unhiding and Listing Your Sheets

It’s often the case that workbooks get very large and end up with a lot of sheets. Excel doesn’t have a lot of built in functionality to make working with a large number of sheets easy. Simple tasks like listing out all sheets, sorting sheets alphabetically, sorting sheets by colour, hiding and unhiding sheets are all commonly done tasks in a workbook. Unforntunately, the options in Excel to do this are mostly manual. These are some common sheet tasks that we can automate with a bit of VBA code! Download Example File Sorting Sheets Alphabetically You can easily reorder...

Read More

How To Get All Sheet Names From All Workbooks In A Folder

It’s a task I’ve had to do before. Get a list of all the sheet names in a workbook with 100+ sheets in it. With a bit of VBA know-how, it can be done fairly quickly. Writing the code to loop through all the sheet objects in the active workbook and write them out to a sheet would only take a dozen lines of code. What if you needed to this for all workbooks in a given folder? Hmm, a bit more code and it’s possible. You first need to loop through all the workbooks in a folder, then...

Read More

How To Combine And Unpivot With The Pivot Table Wizard

You’ve probably come across data that looks something like this before. It’s usually created by someone who doesn’t know what data should look like and thinks that the data should live in a summarized format because that’s how they want to view it. In this example, the sales amounts are scattered over 4 different ranges when they should all be in one column with a descriptive column heading like Sales Amount. The year should feature as a piece of data too in its own column. Salesperson and product should also be in their own column. If this data was...

Read More


Microsoft MVP



50_Tips_to_Master_Excel_hardbackcoverstanding_300x400 Home


Pin It on Pinterest