Here's a great feature of Excel that even regular users may have missed:Excel custom views.
Excel custom views allow you to manipulate the display of a spreadsheet. Automatic formatting of data in Excel spreadsheets with conditional formatting Automatic formatting of data in Excel spreadsheets with conditional formatting Excel's conditional formatting feature allows you to format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various daily tasks. Read More
We'll look at four ways to use Excel's custom views to your advantage. Before that though, you need to know how to create one.
Open an Excel workbook and find the View tab at the top of the screen. When you click on that, you will see the option for Custom Views . Click it.
In the dialog that appears, click Add and enter a name for the custom view. If you want, you can include all or part of the name of the open workbook in it. (Taking that approach might make it easier to find a given custom view later!)
You will see a panel with several checkboxes to select or deselect. They relate to document print settings, hidden rows, columns, and filters. Change the setting if necessary depending on the scope of your project.
There you have it:the basic process Use Custom Views in Excel to save specific worksheet layouts Use Custom Views in Excel to save specific worksheet layouts Excel's Custom View settings make it easy to view specific information on a worksheet calculation full of people or to create different layouts for your data. Read more about how to create a custom view in Excel. If you want to save these steps in the future, simply save the workbook as a template. How to Quickly Create a Custom Excel Template to Save Time How to Quickly Create a Custom Excel Template to Save Time Excel templates are universally useful; they can help you keep your finances in order, manage a project and organize your life. We show you how to create your own custom template. Read more !
The custom views feature isn't one of the program's most well-known capabilities, but it's quite useful, giving you an edge over your co-workers who might be less familiar with it.
Excel offers many ways to specify the appearance of a spreadsheet. 5 Resources for Excel Macros to automate your spreadsheets. 5 Resources for Excel Macros to automate your spreadsheets. Looking for Excel Macros? Here are five sites that have what you're looking for. Read More
For example, if you are typing long sentences in a cell, you may want to extend the rows. Doing that makes it easier to see more of the cell's content.
If each cell only contains a couple of numbers, you may not need to change the width. However, you may want to change the height of the row. This is especially true depending on the font chosen and how it looks in an unaltered cell.
A custom view allows you to almost eliminate the time spent setting up worksheets The Best Productivity Templates for Microsoft Excel to Get Things Done The Best Productivity Templates for Microsoft Excel to Get Things Done if you want to use Microsoft Excel for planning, task management and expense tracking, it's best to start with templates. We have compiled several templates that will keep your life, business, and office on track. Read more to meet particular needs.
Instead of going through the same setup process for each spreadsheet, you can create a custom view. Include your specifications and avoid repetitive configuration changes. Also, as I mentioned earlier, you can save this custom view as a template for multiple uses, so you don't even have to create that custom view again.
This simple tip is extremely helpful if you have multiple similar spreadsheets to make. If they all have identical settings but different information in each, create a custom view template first. Then just add the data.
When working with data in a massive spreadsheet, you may need to restrict the print area. Otherwise, others may see strange or sensitive information. 5 Excel document settings. You should never forget to check. 5 Excel document settings. You should never forget to check. When you select an Excel template or share a spreadsheet, it may contain custom settings that you don't have. I don't want anyone to see. Always check these settings. Read More
Excel makes this pretty easy, but you can make it even easier with custom views.
To create a custom view with this goal in mind, simply highlight the cells you want to print. So, go to the Page Layout tab and click Print Area . You selected the Set print area option .
Then follow the steps to create a custom view as explained above. Remember the dialog that appears after you enter a name for the view? Pay attention to Print settings field inside it and make sure it has a check mark.
Cool! Now when you go to print this sheet, you can feel good knowing that only the information contained in the print field will be printed.
This is what my print preview looks like for this sheet:
This custom view is ideal for reporting to clients or your boss. You can keep all your supporting data and calculations in the same Excel sheet as your official report, but only include the most necessary information in your final document.
Professionals often rely on Excel to create reports. But what if you need to use it for a report distributed to several different groups? In that case, you can use a custom view to easily hide or show columns and rows.
This will allow you to efficiently create multiple reports for different audiences, all using the same data. However, each report will only have the appropriate data for each audience. Quite useful, right??
To set up these custom views, save a custom view of your sheet with all rows and columns in flat view. (If you want to keep the tip of the print area selected from the last point, make sure you still have the Print Settings option checked.) I named mine “All Information” to make it easy to find later.
After that, it's easy to use some keyboard shortcuts to hide rows or columns. Ctrl + 0 (zero) hide the columns, while Ctrl + 9 removes rows from the view.
Save a custom view for the different reports you'll need to create, hiding the right rows or columns each time. When you save the custom view, make sure the box for Hidden rows, columns, and filter settings is checked.
The real power of this hack comes from the fact that you can easily switch between all of these custom views. Simply click on the Custom Views button, select the view you want to see and click Show .
Try this trick when working with sensitive data that contains material that is not suitable for everyone to see. Using custom views in this way prevents you from making a dedicated spreadsheet for each group receiving the material, but allows you to keep the necessary information confidential.
For example, if you have to send information to various departments in your company, it may not be appropriate for the sales team to see the marketing team's report or vice versa.
You can also apply this custom view when creating spreadsheets used for training purposes in your office. People are often overwhelmed when initially looking at unknown cells and the data they contain. By filtering out the unnecessary ones, you can help people focus on the most relevant information.
As I have already mentioned, the desired custom view on your screen involves going to the View menu. It's at the top of Excel, in a section also known as "the lasso." How to Optimize the Office 2016 Ribbon or Menu Interface How to Optimize the Office 2016 Ribbon or Menu Interface Do you often look for menu items in Microsoft Office? It's time to reorganize and customize the Ribbon menu for your personal needs. You will be surprised how flexible and easy it is! Read more
The steps we've been using to retrieve our saved custom views do the job. However, they are not as simplified as possible. Add a custom view command to the Excel ribbon to quickly see your custom views in a dropdown format.
To add the command to the ribbon, click File in the upper left corner of the Excel screen, select Options .
Once you see the categories appear on the left, choose Customize the ribbon .
On the right, you will see a section titled Main Tabs . Find the View tab and look for the plus sign (+) to the left of it.
Clicking the plus sign displays a group called Workbook Views . Select it, then click Add New Group (Not to be confused with the Add New Tab option next to it.)
Right click on the new group and select Rename . A title related to custom views is the most sensible so you can find it later.
After selecting your group, click the dropdown menu under Choose Commands in the header at the top left of this main configuration interface. Select Commands not on the ribbon .
Finally, scroll down and find Custom Views . Then click the Add button, so you move that command into your new group. Hit OK to finish the setting.
Now you can quickly select any of your custom views from the main screen. View glass.
This will save you a lot of extra time for sheets and reports that you'll have to recreate each month.
Before reading everything here, you may have doubted that an Excel function could offer such convenience. 3 Crazy Excel Formulas That Do Amazing Things 3 Crazy Excel Formulas That Do Amazing Things Conditional formatting formulas in Microsoft Excel can do amazing things. Here are some Excel formula productivity hacks. Read more . If you're still not convinced, trying any of these suggestions should open your mind.
Share your new insights with co-workers to improve productivity for the entire company. Or keep this information to yourself so you look even better compared to your peers.
What do you most commonly use Excel for at work? How could you apply custom views to make that task easier?
