No matter what industry you belong to, having a better understanding of Microsoft Excel gives you a huge advantage. If you have a few Excel hacks up your sleeve, you can deal with your data in a more efficient manner.
Here are a few simple hacks that could help you breeze through some of the work that you do in Excel.
How To View Different Sheets In A Single File Simultaneously
In any business, it’s normal to have data spread across different sheets in a single file. But switching from one sheet to another can be inconvenient if you need to view data across different sheets.
What we can do is go to the View ribbon, then click on New Window.
This opens up a duplicate of the current file you’re looking at but in a different window. Now, all you need to do is minimize both windows so that you can view them side by side, with each window showing a different sheet within the same file.
The great thing about this setup is that any change you make on any of these windows will simultaneously reflect on the other window as well. This saves you a lot of time having to switch from one tab to another.
How To Fill Up Blank Cells In Your Data
There are times when your data may have blank cells that you want to fill up. However, doing so one by one would take up too much time. To fill up those blanks, here’s a hack.
Highlight the group of cells with the blank spaces, including the cells that contain data.
Press Ctrl+G, which opens up the Go To window. Click Special.
Choose Blanks, then click OK.
This will lead you back to the table. Without removing the highlight from the chosen cells, type in the equal sign (=) then click on the cell you want to reference.
Then, click Enter.
This will then fill up the blank cells based on the reference cell you chose.
How To Protect Your Formula Cells
When working on a spreadsheet, there’s always a risk of you accidentally deleting or typing over the cells containing your formulas. This can create a few problems, especially if you save over the accidental edit or deletion by mistake.
So what we want to do is to lock in the formula cells so that you (as well as anybody else who has access to the file) won’t be able to change it by accident.
In our example, we’re going to protect the cell containing the total while the other cells remain editable.
To get started, press Ctrl+A to highlight the entire sheet. Then, press Ctrl+1. This will open the formatting window.
Go to the Protection tab, then untick the box that says Locked. Normally, this is ticked by default.
Click OK to go back to the spreadsheet.
Now, click on the cell that you want to protect; in this case, the one containing the formula for Total.
Press Ctrl+1 again to reopen the formatting window, but this time, tick the box that says Locked. You also have the option to click Hidden if you want to hide the formula.
Now, go to the Review ribbon and click Protect Sheet.
Once the window opens up, tick on the box that says “Protect worksheet and content of locked cells”.
Once you click OK, you’ll be directed back to the sheet you’re working on.
Now, if you try to change anything on the formula cell, an error message will pop up telling you that the cell cannot be edited.
***** Related Links *****
Appending Several Sheets In Excel To Power BI
Power BI And Excel: Difference In DAX Formulas
Proportion And Frequency Tables In Excel
Conclusion
Working on Microsoft Excel could be frustrating at times, especially when you’re dealing with huge amounts of data. But the truth is, even the simplest Excel hacks can change the way you work. You can slowly find more efficient ways to work and become more productive.
All the best,
Sam
