Excel Hack 96: My top 10 keyboard shortcuts for my Excel tasks
Wherever you search, extensive lists of keyboard shortcuts abound for diverse range of Excel tasks. Here, I showcase my ten most commonly used keyboard shortcuts: Number 1 of course is the Ctrl+C and Ctrl + V pair. These make copying and pasting cells and ranges quick and straightforward. They are so seamless that I mostly
Excel Hack 95: Toggle the colors in a table ranking on and off, with the click of a (radio) button
For data reports: sometimes you want your data ranking in relation to each other visible. And sometimes, you don’t want it visible. Microsoft Excel’s conditional formatting feature allows a quick way to rank data relative to one another visually, using in-cell bar charts, icons and heatmaps. However, for some use case or another, you might
Excel Hack 94: Keypad arrows scrolling screen instead of moving to the next cell?
Here’s how to fix a periodic issue that comes up while using keyboards in Microsoft Excel: your keypad arrows start to scroll your screen from top to bottom or left to right, instead of moving your focus cell to the left or to the bottom. This issue comes up when the Scroll Lock is activated,
Excel Hack 93: Compile the names of all worksheets in an Excel workbook into a range…in minutes!
Create a new sheet for the compilation. Leave the sheet name as Sheet1 (or if you choose to rename it, note to change the reference in the below VBA code). Go to the Visual Basic Editor and insert a new module (see Hack 85 on how to do this) Place the VBA code below into
Excel Hack 92: Export sheets as individual Excel workbooks, in minutes!
So you have a workbook with several worksheets, maybe up to 30. And you need each worksheet exported one after the other, so that you now have 30 Excel workbooks instead of one. Is there a way to get this done in less than one minute – which takes out the hard work of right-clicking
Excel Hack 91: Normalize a matrix dataset using Microsoft Excel formulas (no macros needed)
When working with tables of data, the table set up usually encountered is the cross-tabulated or matrix data format. However, this dataset is not optimized for further analytics in Business Intelligence software like Tableau or Power BI. These software require datasets to be set up in a manner that ensures that a row represents a
Excel Hack 90: Perform a dynamic Text-to-Rows operation in Microsoft Excel
One of the standard Excel features available is the Text-to-Columns feature which enables users to separate content within a cell into several columns based on a specified delimiter. What if, however, you wanted your data split into rows, not columns? You could split the text into columns using the Text-to-Column operation, and then select the
Excel Hack 89: Extract file names from a folder into Microsoft Excel
Have you had to work with a folder containing lots of documents, and you wanted to get the list of all the documents? This may be a directory listing all the policy documents in the organization, or the CVs of job candidates, or PDF forms filled by several employees. If you needed to get a
Excel Hack 88: Create dynamic gauge charts with 3 zones in Microsoft Excel
Gauge charts are visualization tools used to show a KPI performance in relation to a gradient demonstrating the ranges of poor, medium, average, and excellent performance. Gauge charts have a reputation for being more aesthetic than functional for data visualization purposes. Some don’t think they deliver that much punch compared to the real estate they
Excel Hack 87: Create a dynamic timescale in Microsoft Excel
Here is how to set up your Excel worksheet with the flexibility to update the time unit as required. In the example below, the flexible timescale has been created for a Project Gantt Chart, and users want to retain the ability to view the project task timelines in days, weeks, or months. This has been
Excel Hack 86: Build a dynamic calendar in MS Excel – in minutes!
A dynamic calendar in Excel is set up to present the calendar for a selected month and year and automatically updates when the month and year are changed. There are preset Excel templates containing dynamic calendars that are accessible to all Excel users from the File>New options. These have been created with complex array formulas
Excel Hack 85: Activate the Developer Tab in the MS Excel Ribbon
Here is a very quick tutorial to demonstrate how to activate the Developer Tab in MS Excel. Step 1: Click on the File tab and select “Options” Step 2: In the window that pops up, locate “Customize Ribbons” in the navigation bar at the left and click it. The view below comes up: Step 3:
Excel Hack 84: VBA to change a specific fill color for all cells in a worksheet to another color – with just one click!
I will demonstrate this with a quick example. I have a worksheet below, named “data”. This worksheet has multiple tables set up with a light yellow fill color for all header rows and first columns, and no fill for the rest of the table. The color scheme for my project just got updated, so I
Excel Hack 83: Generate randomized data (or dummy datasets) in Microsoft Excel
This might appear as an unusual hack for data analysts, but there are a couple of reasons you will need to generate randomized data in Microsoft Excel: you are building a portfolio to demonstrate your data visualization skills, but you do not raw data to analyse and visualize you want to share an Excel template
Excel Hack 82: Create dynamic charts in Microsoft Excel
You may have encountered an Excel worksheet with the below feature – a chart that changes values based on a selection from a drop-down button or combo box. This functionality is powered by a combination of Form Controls (Combo Box in particular) and an Excel chart which takes its source from a dynamic range that
Excel Hack 81: Find and replace several items at once in Microsoft Excel (VBA hack)
Here is a custom macro for finding and replacing several items in an Excel list. This macro loops (runs) through a list of items on a table with two columns – old text and new text – and runs a find and replace operation for each row in the table using the text in the
Excel Hack 80: Count occurrences of a character or string in a cell in MS Excel
This is a short and quick hack that could come in handy while conducting complex text analysis in MS Excel. If you needed to count the number of times a particular character, letter or string occurs within a cell (or a range of cells) in Excel, apply the formula below: This is what it looks
Excel Hack 79: Send emails (with attachments) to multiple users at once with Excel VBA
This is related to an earlier hack published (see Hack 4: Email a document from Excel using VBA), for sending one report to several users. For this use case, you have a list of users, and each user has a specific report that must be sent to them. In our example, there are 8 business
Excel Hack 77: Clean data in MS Excel (reorder first names and last names)
If you have ever needed to perform a lookup function and encountered Not Available (#N/A) errors due to wrong name ordering, you are going to love this hack. The wrong name order issue comes up commonly when working with human names – employee names or customer names – in Excel. Data analysts face this when
Excel Hack 76: Include thumbs-up and thumbs-down icons on KPI dashboards
In MS Excel, the conditional formatting feature allows users to include visual elements on Excel performance reports to use colors and shapes to quickly differentiate good performance from sub-optimal performance. The popular conditional formatting styles are color scales, color bars, and icons. But what if you wanted to explore other visual elements i.e. a more