In this guide, I’ll show you how to automatically remove completely empty columns in your Excel data using Power Query. This powerful technique saves you from tedious manual deletion and works even when columns contain null values, empty cells, and empty text strings (represented by “”).
The approach I’ll demonstrate uses a specific Power Query formula that identifies and removes columns with no meaningful data, greatly improving your data cleaning workflow.
Watch my detailed video walkthrough of this process:
Understanding Empty Columns in Power Query
When working with data in Excel, we often encounter columns that appear empty but can actually contain different types of “emptiness” that Power Query recognizes differently. It’s important to understand that Power Query distinguishes between null values (truly empty cells), and cells containing empty text strings (“”).
An empty text string occurs when a cell contains a formula that returns “” or when you paste such values. These look empty visually in Excel, but Power Query treats them differently than cells with null values. Our goal is to create a solution that identifies and removes columns regardless of which type of “emptiness” they contain, as long as the entire column contains no meaningful data.
Importing Data into Power Query
To begin the process of removing empty columns, we first need to import our data into Power Query. Here’s example how to do this:
Go to the Data tab in Excel
Click on “Get Data” → “From File” → “From Excel”
Locate and select your Excel file
Choose the worksheet containing your data (in my example, Sheet1)
After importing, you’ll notice that some columns might appear empty but contain different types of emptiness — some cells might have null values (shown as “null” in Power Query), while others might be truly empty or contain empty text strings. Our code will handle all these scenarios automatically.
The Power Query Solution
Rather than manually deleting empty columns, we’ll use a custom formula in Power Query that automatically identifies and removes all empty columns. This approach is much more efficient, especially when working with large datasets.
The Complete Code
Here’s the complete Power Query formula that we’ll use to remove empty columns:
To use this code in your own projects, you’ll need to replace #“Change Type” with the name of your previous step in Power Query. This reference appears in three places in the formula, so be sure to update all instances.
Breaking Down the Formula
Let’s break down this formula to understand how it works:
The formula starts with Table.RemoveColumns(), which is the function that will eventually remove our empty columns from the table. This function takes two parameters: the table we’re working with, and a list of column names to remove.
To generate the list of column names to remove, we use List.Select(), which filters a list based on a condition. We’re filtering the list of all column names (obtained using Table.ColumnNames()) to find those that meet our “empty column” criteria.
Extract all values in the column using Table.Column()
Remove any values that are empty strings or null using List.RemoveMatchingItems()
Count how many items remain using List.Count()
Check if this count equals zero (meaning the column is completely empty)
If the count equals zero, the column is considered empty and will be included in our list of columns to remove.
Implementing the Solution
To implement this solution in your Power Query Editor:
Click “Add Step” to create a new transformation step
In the formula bar, paste the complete code shown above
Replace “#“Change Type”” with the name of your previous step (typically the step after promoting headers)
Press Enter to apply the transformation
You should now see your data with all empty columns removed. The transformation happens immediately, leaving only columns that contain at least one non-empty value.
Testing the Solution
To verify that our solution works correctly, we can test it with different types of data. In my video, I demonstrated this by:
1. Starting with a dataset that contained several empty columns (with various types of emptiness)
2. Applying our Power Query solution to remove these columns
3. Modifying the source data to add a new column with a single value
4. Refreshing the query to verify that the column with a value is preserved
The solution correctly identified and retained columns that had at least one meaningful value, while removing columns that were completely empty.
Advanced Usage and Adaptations
This solution can be easily adapted to work with different datasets. The key is to ensure you’re referencing the correct previous step in your Power Query transformation sequence. If your previous step is named differently, simply replace all instances of “#“Change Type”” with the appropriate step name.
When working with very large datasets, this automated approach can save significant time compared to manually identifying and removing empty columns. Additionally, the solution is dynamic — if your source data changes and a previously empty column now contains values, refreshing the query will automatically retain that column.
Loading the Results
After applying our transformation, we can load the results back to Excel:
Click “Close & Load” (or “Close & Load To…” if you want to specify a destination)
Choose your preferred destination (worksheet, data model, etc.)
The cleaned data, with empty columns removed, will be loaded to your specified location
The resulting table will contain only meaningful columns that have at least one value. This makes your data more concise and easier to work with.
When your source data changes, simply refresh the query to apply the same transformations to the updated data. The empty column removal logic will be reapplied automatically, adapting to any changes in your source data’s structure.
Creating a custom sequence of days in Excel can streamline scheduling for specific business needs or project timelines. By using the WORKDAY.INTL function, you can generate sequences that include only certain weekdays, allowing you to build schedules that skip specific days like Fridays or only include particular days like Mondays, Wednesdays, and Fridays.
This technique is especially valuable for planning recurring meetings, shift schedules, or delivery dates that follow non-standard patterns.
In my video, I demonstrate how to create these custom day sequences in Excel:
Standard Weekday Sequences in Excel
Excel provides a simple built-in method for creating standard weekday sequences (Monday through Friday). When you need a list of dates that excludes weekends, you can use Excel’s fill handle with the “Fill Weekdays” option. This approach is straightforward:
Enter your starting date in a cell
Hold the right mouse button and drag down as far as needed
Release and select “Fill Weekdays” from the context menu
This creates a sequence that automatically skips Saturdays and Sundays, giving you only the standard working days. However, this default method is limited to the standard Monday-Friday work week and doesn’t allow for customization.
Using WORKDAY.INTL for Custom Day Sequences
For more flexibility in day selection, Excel’s WORKDAY.INTL function becomes invaluable. This powerful function allows you to define which days of the week should be considered weekends (non-working days), effectively letting you create sequences with only your desired days.
The basic syntax of the WORKDAY.INTL function is:
start_date: The initial date from which to begin counting
days: The number of working days to add to the start date
weekend: A parameter that defines which days are considered weekends
holidays: An optional parameter for specific holiday dates to exclude
Weekend Parameter Options
The weekend parameter is what gives this function its flexibility. Excel offers multiple ways to specify which days should be treated as non-working days:
Using predefined weekend codes (1–7 for different standard weekend patterns)
Specifying a single day of the week as a weekend
Creating a custom 7‑character text string of 1s and 0s
The third option is the most versatile and the focus of my demonstration. The 7‑character string represents the days of the week starting with Monday (position 1) through Sunday (position 7). Using “0” marks a working day, while “1” marks a weekend/non-working day.
Creating Custom Day Sequences
To create a sequence that excludes specific days, we can leverage the text string weekend parameter. For example, if we want to exclude Fridays, Saturdays, and Sundays from our sequence, we would use:
=WORKDAY.INTL(start_date, 1, "0000111")
In this string, the “1“s in positions 5, 6, and 7 represent Friday, Saturday, and Sunday as non-working days, while the “0“s in positions 1–4 indicate Monday through Thursday as working days. By setting the days parameter to 1, each application of the function adds exactly one working day to the sequence.
When this formula is dragged down, it creates a sequence of dates that includes only Monday through Thursday, skipping over the days we’ve designated as “weekends.”
Example: Monday-Wednesday-Friday Sequence
If we want to create a sequence that includes only Mondays, Wednesdays, and Fridays, we would define all other days as non-working days:
=WORKDAY.INTL(start_date, 1, "0101011")
This pattern sets Tuesday, Thursday, Saturday, and Sunday as non-working days (represented by “1“s in positions 2, 4, 6, and 7), while Monday, Wednesday, and Friday remain as working days (with “0“s in positions 1, 3, and 5). When applied and dragged down, this formula creates a sequence that cycles through only the three days we want: Monday, Wednesday, Friday, Monday, Wednesday, Friday, and so on.
Practical Applications
This technique has numerous practical applications in business and personal planning:
Meeting schedules for recurring team meetings on specific days
Class or training schedules that occur on select days of the week
Shift work patterns for employees with custom working days
Delivery schedules for services that operate only on certain days
Payment or billing cycles that follow specific day patterns
By mastering the WORKDAY.INTL function with custom weekend parameters, you can create highly specialized date sequences that match exactly the pattern you need, without having to manually select or filter dates.
Additional Considerations
While the examples in my demonstration focus on the weekend parameter, remember that the WORKDAY.INTL function also accepts a holidays parameter which can further refine your date sequences by excluding specific holiday dates.
For more complex scheduling needs, you might combine this function with other Excel features like conditional formatting to highlight certain dates or custom number formatting to display the dates in your preferred format.
The beauty of using functions like WORKDAY.INTL is that your sequences will automatically adjust if you change the starting date, making this a dynamic solution for scheduling that can be easily updated as needed.
Excel VBA — How Absolute and relative references in macros | Introduction to macros and VBA 05
Today, we will talk about absolute and relative references in macros as well as different ways to run our macro.
Today, we are going to record a macro, where we will put the TODAY function. However this time, we will press ENTER instead of the Ctrl + Enter shortcut. We can start with cell A5. Let’s use the Record button located in the status bar tab this time instead of using the Developer tab (Fig. 1)
Fig. 1 Record button
Let’s call our macro ‘Today2’. We dont’t need any shortcut but let’s try to add a shortcut here by clicking the Ctrl + D combination and see what happens later on. We want to save our macro in the Personal Macro Workbook. When we press OK, Excel won’t allow us to save our macro, because we already have a macro with this shortcut. Let’s delete the shortcut then. When we use a shortcut that already exists in Excel, macro shortcut for macro will be more important than the default shortcut, e.g. Ctrl key. Thus, we shouldn’t use such kind of shortcuts. Let’s press OK (Fig. 2)
Fig. 2 Record Macro window
Now, we just write TODAY in cell A5. It’s important that the Use Relative References isn’t highlighted (Fig. 3)
Fig. 3 Use Relative References not highlighted
After we finished writing TODAY, let’s press ENTER. After writing the formula, we can see that Excel went down one cell by default. Now, we just press STOP on the status bar (Fig. 4)
Fig. 4 Stop on the status bar
After we stopped our macro, how can we run it now? We can go to the Developer tab and press the View Macros command or just click the Alt + F8 key shortcut (Fig. 5)
Fig. 5 View Macros command
In the window that has appeared, we can see all available macros, which are in All Open Workbooks. Now, we have only two. We can see that before the macro name, we have the file name. We can choose macros from many files (Fig. 6)
Fig. 6 File name and files
For us, it’s important to run the Today2 macro, so let’s select it and press the Run button (Fig. 7)
Fig. 7 Run button
Excel has written the TODAY function in our selected cell, which is C5 and has gone to cell A6 (Fig. 8)
Fig. 8 Excel in cell A6
Let’s try a similar thing with the firs macro. Let’s press Alt + F8 shortcut, select the Today2 macro and press RUN. Now, let’s see what exactly our macro has done. Let’s press Alt + F11. We should see our first macro and our new macro Today2, as well as some comments (Fig. 9)
Fig. 9 Our macros
Let’s delete the comments as we don’t need them and let’s locate the lines so that they are clearer to read. Now, we have two lines of the code. The first line of the Today2 macro is very similar to the Today macro. However, instead of ‘Selection’, we have ‘ActiveCell’. It means that our formula is written in the active cell. After the line that contains our macro, Excel will always go to cell A6, We can see it in the next line of ‘Range (“A6”). Select. ‘Select’ means the action, i.e. that we have selected the cell (Fig. 10)
Fig. 10 Code lines
This is how macros are working. If we select a range, the TODAY function will be placed only in the active cell. Then, Excel will go to cell A6.
Let’s try a similar macro. The only difference is that this time we will be using relative references. So let’s do it once again: click on the Record Macro button (1), let’s call it Today3 (2), This Workbook (3) and press OK (4) (Fig. 11)
Fig. 11 Recording a new macro
We’re writing everything once again: TODAY function in cell A7 (1), then Enter (2), then the Stop Recording button (3) (Fig. 12)
Fig. 12 The process of recording
This time we were using relative references. Let’s press Alt + F11. Where is our macro? It’s not in the PERSONAL.XLSB file but in our Macro file, where we record. It means that we have to find Modules (1), where Module 1 should be added (2). Let’s double-click it and we can see our macros (Fig. 13)
Fig. Module 1 with our new macro
We don’t need comments so let’s delete them again. Let’s also adjust the size of the windows so that we can see it clear. We have the TODAY 2 and TODAY 3 functions here. We’ve found our macro (Fig. 14)
Fig. 14 Both macros
There is one more way to find our macros. Let’t go to the Developer (1) tab and go to the View Macros button (2). In the window that appeared we can see that we have many options there like Run, Step into, Edit, Delete and Options (3). For our macro, the Options button is the most significant one, because here we can see shortcuts for our macro (4) (Fig. 15)
Fig. 15 Macro options
If we delete D, this macro will delete the shortcut if I press OK. However, we’re not going to do that. Let’s close the Macro Options window and let’s focus on the Today3 macro. Since this file is still open, we don’t see any names before. Let’s go to the Edit option of this macro (Fig. 16)
Fig. 16 Edit option
This option leads us to the exact window our macro is stored. The first line in the Today3 macro is exactly the same as in Today2 macro. However, the second line is totally different. First of all, both lines start with ‘ActiveCell’. Then, we have Offset (1, 0) — by one row and 0 columns. It means that we went one cell down. Then, VBA tells us that we selected Range A1. In fact, we didn’t select A1 or any other cells. Sometimes, it’s hard to understand VBA due to some inputs. It’s true that we didn’t select A1, but when we went one cell down from the active cell, we selected a range that is the same size as range A1 cell.
Once again. First we start with Active Cell, then there is a dot, which means that we take another step, then choosing the Offset, one row, 0 columns, then we have the Range A1 and what we do with this range, which is selecting the range (Fig. 17)
Fig. 17 Today3 macro
Let’s go back to our worksheet and select cell A8, then go once again to our macro by clicking Alt + F11. This time, we press Play (Run Sub) in our VBA editor or F5 as a shortcut (Fig. 18)
Fig. 18 Run Sub command
What we notice here is that Excel has written the Today function in cell A8 and went one cell below (Fig. 19)
Fig. 19 TODAY function and once cell below
Let’s select a range this time and press F5 (Fig. 20)
Fig. 20 Range selected
What we’re noticing is that the value has been written only in the active cell, although we had selected the whole range. Then, Excel went one cell down (Fig. 21)
Fig. 21 TODAY function and one cell down
Summing up, there is a small difference between relative and absolute references, but still, we have to consider whether we want to use the first one or the second.
In this post, we will see where our macro is saved.
Excel VBA — Where is my macro | Introduction to macros and VBA 04
Let’s go to the Developer tab. On the left, we can see the Visual Basic command. We can also press the Alt + 11 shortcut. So, let’s click it. Now, in the worst case scenario, all we would see is a grey window. In this case we need some more information. Let’s turn on a smaller window by opening the View menu, and select the Project Explorer command (Fig. 1)
Fig. 1 Project Explorer command
What we should see here is all Excel files that are opened, as well as our PERSONAL.XLSB files, which are our personal files for macros (Fig. 2)
Fig. 2 Files
In each file, we should also see some worksheets and similar things, especially modules. Our code should be written as Module 1 by default. Before we click on it, let’s go to the View tab one more time and click on the Properties Window command (Fig. 3)
Fig. 3 Properties Window
This window shows us information about files or worksheets (Fig. 4)
Fig. 4 Worksheet information
Let’s go now to Module 1. After double-clicking on it, we should have a small window opened. Our first macro was very simple, however we have more rows than we need. We don’t need the Option Explicit line, so let’s delete it. Next, we have the Sub key word which starts our macro and the phrase End Sub which stops it. Only the elements that are between those two lines create our macro. Our macro is called TODAY. The other time I mentioned that the TODAY function is a DZIŚ function in Polish, however no matter what version I have on my computer, VBA is American and will use American names and procedures in macros. When we look at our macro, we can see the line with the TODAY function, as well as a few green lines starting with green single quotation marks. They are just comments. In VBA, comments start with a single quotation mark, and are not part of the VBA code (Fig. 5)
Fig. 5 VBA code with comments
Having the above in mind, I can just delete them and leave only the line that is an actual VBA code. Now, it’s quite simple when you read it. ‘Selection’ means that we just selected something, a dot means the next step after selecting. Then, we have the Formula, which means that we put a formula. The R1C1 isn’t important for us. Then, our formula equals the TODAY function. That is the whole VBA code (Fig. 6)
Excel VBA — Recording first macro | Introduction to macros and VBA 03
First of all, we want to open a file with macros. What we have here is a piece of information that says ‘Marcos have been disabled’. You need to click the Enable Content button (Fig. 1)
Fig. 1 Information concerning macros
If you don’t want to click the Enable Content button constantly, like me, go to the Developer tab, then click on the Macro Security command, then the Enable VBA macros radio button (Fig. 2)
Fig. 2 Enable VBA macros command
We can also go to the Trusted Location part and press OK. It will also work for all files and help us with too many notifications or messages (Fig. 3)
Fig. 3 Trusted Location
Today, we want to record very simple macro. We just want to write the TODAY function in cell A2. This cell should be selected before we click the Record Macro command. Since the cell is selected let’s write the equal sign, then TODAY, then click the Ctrl+Enter key combination. It’s important to use this shortcut. That’s all we need in our first macro (Fig. 4)
Fig. 4 Ready function
Now, let’s delete this value and then press the Record Macro. In the window that has appeared, the first thing we do is choosing the macro name. We can use the name of our function’s name. In most cases we should’t do that, however our case is really simple. It’s only macro, not a VBA function. Then, we choose whether we want a shortcut for our macro or not. If the box is empty, it means that our macro hasn’t got a shortcut. If we write there a letter or a sign, this will mean that we have a shortcut. The Ctrl key is a default key for each shortcut. It also applies to the Shift key. That’s why, let’s hold the Shift key and press D at the same time. I’m using D, as ‘today’ is ‘dziś’ in Polish. If we don’t want any shortcuts, we just press the Backspace button. Next, we have the location of our macro. We have a choice of Personal Macro Workbook, New Workbook or This Workbook. Let’s choose the Personal Macro Workbook, as it’s the most significant one. We can also add a description in the next part, however, in most cases the name should be enough to tell us what this macro does. We have already chosen the name, shortcut and storage place, so we can press OK. You need to be careful when pressing OK, because that’s the point when the recording process starts. It means that each action you do in this worksheet will be recorded (Fig. 5)
Fig. 5 Record Macro window
Now, our Record Macro command changed into the Stop Recording command. The button in the bottom left corner of the status bar also changed. It means that we don’t need to go to the Developer tab to stop the recording (Fig. 6)
Fig. 6 Stop Recording button
Let’s write =TODAY and press Ctrl + Enter again. Then press the Stop Recording button. It’s crucial to press this button so that Excel doesn’t record any unnecessary code.
Now, since we added a shortcut to our macro, let’s use it in cell C2 by pressing the Ctrl+Shift+D shortcut. Something actually appeared in the cell, but we need to change the formatting to get the date (Fig. 7)
Fig. 7 Formatting modification
Now, we can see that we have our date, wherever we use the shortcut (Fig. 8)
In this post, we will work on something we should consider before starting recording our first macro.
Excel VBA — Preparation before recording first macro | Introduction to macros and VBA 01
First of all, in the Developer tab, we have the Record Marco command. We can also find this command in the status bar on the left side. It make it easier to start our recording (Fig. 1)
Fig. 1 Record Macro command in the status bar
Then, we should consider relative and absolute reference. We have the Use Relative References command. If it isn’t highlighted, it means that we work with absolute references. For example, if I select cell B9, then select cell B12, Excel will record this as ‘select cell B12′. When I turn on the Use Relative Reference command, then select cell B9, and then cell B12, Excel will record this step as ‘go three rows down’. This issue is very important when recording our macro.
Let’s click on the command one more time to turn it off and work with absolute reference.
There is one more command to look into. It’s called Macro Security. After clicking it, we have a Trust Center window. In the Marco Settings area, we can see an option of Disable VBA macros with notifications (Fig. 2)
Fig. 2 Disable VBA macros with notifications option
This option should be selected by default. It means that when we open a file with macros (with an xmls extension), we will have information that this file contains macro, and some code can be run without our knowledge. However, in most cases, we shouldn’t be afraid of this code. It’s a code which we copy from secure sources, like Google. Google is quite a secure source, as it shows you good websites, not dangerous ones.
When you start working with VBA, you should know something about the VBA code. If you see something unfamiliar, you shouldn’t run this code.
I personally don’t like notifications, especially the ones that show up every time I open a file containing macros. That’s why, in most cases I’m working with the Enable VBA macros option. It’s not recommended, however, from my point of view it’s practical. I’ve been working with macros for many years, and I haven’t come across any dangerous code. The most dangerous thing a VBA code has done to me is rewriting my current data.
Having the above in mind, when you run macros and you don’t fully know what they are doing, you should always create a backup copy before.
The above are the most significant pieces of information you should consider before recording your first macro. In the next post, we will be recording our first macro.
This post starts a series about an introduction to macros and VBA code.
Excel VBA — What is a macro and enabling the Developer tab
What is a macro? In most cases it’s a synonym for VBA code that we run. It’s almost the same thing. Sometimes we differentiate it and we call macros the VBA code that was recorded by Excel — Record Marco command. If we want to use this command, we should turn on the Developer tab. In order to do that, we have to right-click on our ribbon and go the Customize the Ribbon command (Fig. 1)
Fig. 1 Customize the Ribbon command
In the Excel Options window that has appeared, we have to find the Developer tab on the right side, press it and press OK (Fig. 2)
Fig. 2 Developer tab
Now, we can see that in the Developer tab we have the Record Marco command (Fig. 3)
Fig. 3 Record Macro command
This command allows us to record actions we are doing on a worksheet, such as cell formatting, putting values in a cell, size modification, etc. It’s a good place to start learning about the VBA code.
Macros are good for two main reasons.
First, they are good for repeating tasks. If we want to make reports on a daily basis, we can just record all actions we are doing in a worksheet and then we are able to use it again, probably with tiny modifications. We will have a report format by running macro.
Second, the VBA code allows us to do the things that are not possible or very hard when using Excel functions. Those things will be much simpler using the VBA code. It may even happen that somebody has already written a proper code to this task.
If we want to save macro, we have to use the ‘xlsm’ extension, where ‘m’ stands for macros. We can run macros on ‘xlsx’, which is a standard extension for Excel, however Excel won’t save macros in those files. Remember, only ‘xlsm’ works for macros.
If I need to extract only letters from my text, what should I do? Lets find out.
Extract only letters from text
When I need to extract only letters from my text, it means that I also need to extract spaces and enters (new lines).
With Power Query this task is quite easy.
I need to select one cell in my table, then go to the Data tab, and click on the From Table/Range command in order to take our data to Power Query (Fig. 1)
Fig. 1 From Table/Range command
Here, I have four simple pieces of text. One in English and three in Polish. We need to go to the Add column tab, then to the Custom column command (Fig. 2)
Fig. 2 Custom column command
In the Custom Column window, we have the space to name our column. Let’s call it Clean, and below we have the Custom column formula box, where we write the Text.Select function. In the function we need to work with text, so let’s select the right column (1). Then, I need letters. Since there will be string of characters, I have to use curly brackets, where we write upper case letter in the first sequence, which is “A”..“Z”. Then let’s write lower case letters, which is “a”..“z”. Writing two dots between letters means that there is a sequence. Let’s close the curly bracket and the parenthesis. Now, it should be fine (Fig 3)
Fig. 3 Whole function
After pressing OK, the system extracted for us only letters. However, the text presented like this is just gibberish. That’s why we have to add spaces. To do that, we need to go to the View bar, then select the Formula bar checkbox and start modifying our formula. Let’s write a comma and a space in double quotes (1). If we need other signs, we should also write them, e.g. a dot (2). As we can see, we have spaces and dots in our new text (Fig. 4)
Fig. 4 Modifying the formula
However, I don’t need dots in my text. What I really need are enters. We can remove the dot from the formula and write #(lf) in its place. After entering the formula, we can see that we have extracted enters, lower case letters, spaces. But, I didn’t extract Polish letters. In row 4 there are strictly Polish letters. In rows 2 and 3 there are some of them. The question is: how can I extract unusual signs from text using the Text.Select function. First of all, we have to check the Unicode. We can see that letters from A to Z is a sequence of numbers from 65 to 90. It’s similar in lower case letters. The most important lesson from it is that we have a sequence (Fig. 5)
Fig. 5 Unicode
Now, let’s go back to our formula. If I want to have Polish letters, I can write a sequence of “Ó”..“ż”. After entering the formula, we have extracted also Polish signs (Fig. 6)
Fig. 6 Extracting Polish signs
Now, that we have extracted all signs we need, we can go to the Home tab and click on the Close&Load to command to transfer our results to Excel (Fig. 7)
Fig. 7 Loading results to Excel
In the Import Data window, we have to select a cell (Fig. 7)
Fig. 7 Choosing the target cell
As we can see, the text is written in one line, so we need to wrap it. Let’s go to the Home tab and click on the Wrap Text command (Fig. 8)
Fig. 8 Wrap Text command
Now, we clearly see upper case letter, lower case letter, spaces, enters. I removed signs that I didn’t need (Fig. 9)
When we need more space while working in Dashboard in Excel, we can just hide unnecessary options. How? Let’s find out.
Hide scroll bars, formula bars, column headers
The options we can hide are e.g. scroll bars, sheet tabs, formula bars as well as column/row headers. If we want to hide then we just go to the File menu and choose the Options command (Fig. 1)
Fig. 1 Options
In the Excel Options window, we choose the Advanced tag. If you prefer using shortcuts, you can press Alt + T + O, which also opens the window. Lets go to the Display area and uncheck the Show formula bar checkbox. Remember that it works for all Excel files (Fig.2 )
Fig. 2 Show formula bar checkbox
Then, we can go to the Display options for this workbook area. We have to uncheck the Show horizontal scroll bar, Show vertical scroll bar, Show sheet tabs checkboxes (Fig. 3)
Fig. 3 Three checkboxes
We can even go to the Display options for this worksheet area where we can uncheck the Show row and column headers checkbox and press OK (Fig. 4)
Fig. 4 Show row and column headers checkbox
We can also double-click on the Home tab to hide the ribbon, which gives us even more space than before.
Now, I can press Ctrl + Page Down to go to another workbook. Here, we can see that column and row headers are visible. However, when we go back to our workbook by pressing Ctrl + Page Up, we can see that there are no row or column headers. That’s how you make more space in your Dashboard.
How to add a drop-down list in Excel? It’s a really simple task.
First of all, we have to have our list (1). Then, we have to select a cell or cells to which we want to add the drop-down list (2). Then, we’re going to the Data tab (3) and click on the Data Validation command (4) (Fig. 1)
Fig. 1 Preparing data
In the Data Validation window, we have to select the List option in the Allow bar, then we’re selecting the range and write it into the Source bar. Then OK (Fig. 3)
Fig. 2 Data Validation window
Now, we have a drop-down list (Fig. 3)
Fig. 3 A drop-down list
Since our cells with data are in a table, when we add a new line to the table, it also involves the drop-down list.
Even, if we cut and paste our new data, the cells in the selected column will still have the drop-down list. What’s more, in the newest Excel version, we have even Lookup options. We just have to start writing the first letter of a word we’re looking for, then press the Tab key.
Fig. 4 Lookup option
This function, however, doesn’t work when we start from the middle of our text. It works only with the first letter.
