Several people have asked about how to see multiple Excel worksheets side-by-side at the same time. Depending what you want to do, here a few tricks you can try to save yourself some toggling back and forth.
Articles Tagged: Sheet Formatting
Customize Excel’s pivot table
excel-pivot-table
Excel is a common business tool that is used in an uncountable number of ways. Some spreadsheets are short, maybe one or two columns and 20 rows. Others can consist hundreds if not thousands of cells. When they get this big, it can be tough to quickly summarize or compare data. That’s where the pivot table comes in, unfortunately it isn’t perfect and may require a few modifications.
Here’s four ways you can modify Excel 2013 for Office 365′s pivot tables to meet your needs.
Make your tables look good
When you first create a new pivot table you may notice that the numbers aren’t formatted properly, or the table didn’t pull the format from the base spreadsheet. They may have too many decimal places, or are lacking currency figures. You can make edit this by:
Selecting any of the numbers in the Value field.
Clicking on the Analyze tab from the tabs above the table.
Pressing on Field Settings in the Active Field group.
This will open the Field Settings panel and allow you to edit the whole Value field, which means that if you change numbers, location, etc, the formatting will stick. Pressing Number Format will bring up the Format Cell window where you can apply numbers, currencies, decimal places, etc. Pressing Ok will apply the formatting you have selected to the whole field.
You can also apply color schemes or styles by pressing the Design tab and selecting the theme that looks good to you. This will not usually change the layout, just the color and style.
Change the name of the table and fields
If you are going to be sending the table to clients or colleagues, you may want to change the name of the table and fields to something a little more user-friendly. You can change the names by:
Clicking on the cell/name you would like to change.
Pressing on the Analyze tab.
Looking at the Active Field area of the Analyze tab and double clicking on the cell’s name. You can also click on the box beside Pivot Table Name if you want to change the table’s name.
Entering a new name.
Add gridlines for easier viewing
If you need to separate the information in the cell, the easiest thing to do is to add gridlines. This can be done by:
Clicking on a cell in the table
Selecting the Design tab and choosing any style aside from the first one.
Looking at the PivotTable Style Gallery and ticking the box that says Banded Rows.
Change blanks to 0s
There may be an issue where the data that’s used to create the table leave blank spaces when they should be 0s. This isn’t a big deal, but it would be a good idea to ensure that, for consistency, blank cells are represented as a 0, especially if other related cells are numbers. You can force Excel to fill blank cells with a 0 by:
Right clicking on the table.
Selecting Options followed by Layout & Format in the pop-up window.
Entering 0 beside the box that says For empty cells show
Pressing Ok.
Excel and the pivot table bring some excellent functionality to the spreadsheet, and having a properly formatted pivot table could go a long way in ensuring the information shown is easier to read.
Courtesy: techadvisory.org
Learn Using “Quick Analysis” in Excel 2013
Microsoft’s spreadsheet program, Excel, is one of the most useful tools that any manager’s disposal. This dynamic tool can be employed in a multitude of ways, from tracking time, to finances and even sales. One of Excel’s most useful functions is the ability to develop graphs and charts from information which can then be easily analyzed. If you use Excel 2013, you have Quick Analysis – a powerful feature that allows you to quickly analyze data.
Below is an overview of the Quick Analysis tool in Microsoft Excel 2013.
What is Quick Analysis?
In older versions of Excel, if you wanted to visually analyze your data, you would have to first create a chart or graph and then format it. This could take some time and also lead to mistakes, not to mention the fact that it can be a challenge to pick the correct type of chart or graph for your data type.
To make it easier to visualize your data, the Quick Analysis tool was introduced with Excel 2013. This feature allows users to instantly create charts and graphs with the click of a button. It even suggests a visualization method that best fits your data, making picking the correct way to show the information far easier. You can even add miniature graphs to single cells – called Sparklines – that allow you to quickly spot trends without having to look at a full graph.
How to use Quick Analysis
Enter your data in a spreadsheet, and if need be include column headings.
Select the data you would like to visualize.
Press Ctrl + Q to open the Quick Analysis gallery. You can also select this by hovering your mouse over the bottom-right corner of the selected cells and clicking the icon that pops up.
Select the tab you want e.g., Charts for suggested charts. Note: The options available to you will change based on the type of data you have selected.
Hover your mouse over an option to see a preview of the selected visualization.
Click on your choice to create the visualization in a new worksheet.
Which Quick Analysis is best?
When you open the Quick Analysis gallery you will notice that you have five different options:
Formatting – Lets you adjust the data you have highlighted. You can set a color for the data to set it apart and quickly see both high and low values, or even remove formatting altogether.
Charts – Lets you select different charts and graphs based on the data you have selected. This can include pie charts, bar graphs, line charts, etc. If you press More Charts you will be able to select from a larger list.
Totals – Lets you calculate numbers in rows or columns. Some useful options include Running Total which keeps a total even when you add more data, and Sum which will total either a column, row or the total sheet. Be sure to pay attention to the colored dots in the icon as they indicate whether a row or column will be calculated.
Tables – Allows you to create tables or sub-tables using just the selected data. You can also create a pivot table. You should also be able to select More if the type of table you need isn’t there.
Sparklines – Allows you to add small charts beside your data. These one to two cell visualizations are great for quickly identifying trends within your data.
If you are looking to learn more about Excel 2013 and how you can leverage it in your business, contact us today.
Courtesy: techadvisory.org
How to create 3D Worksheets in Excel
One of Excel’s greatest tricks is the dimensional or 3D reference. This feature, which allows you to create formulas that refer to the same cell or range on multiple worksheets, simplifies the creation of complex documents like monthly inventories or sales reports. In Excel 2013, Microsoft has beefed it up with enhanced options and formulas. […]
How to Create Great Charts and Graphics in Excel
So, you need some eye-popping visuals to show off your top sales numbers for that meeting in 40 minutes but data, not design, is your forte. No problem. With Excel 2013—even if you’ve never used it—you can pump out a sophisticated, professional chart as fast as you can type. Or, if you can copy and […]
How to quickly compare pricelists in excel
Today I am going to show you how to quickly compare two tables using conditional formatting. We are comparing two price lists from the year 2013 and year 2014. To make things more interesting, price list 2014 is not sorted. new products are also introduced. It is quite common that pricelists are huge and a total […]
12 Basic Shortcuts That Will Make You An Excel Guru And Add Hours To Your Life
Microsoft Excel is a crucial tool for anyone operating in modern business. But there are risks. For one thing, it can be intimidating for beginners. Furthermore, Excel errors can have devastating consequences, such as the one that undermined one of the most famous economics papers of all time.
But it doesn’t have to be this way. There are a few basic skills and shortcuts that can really save you a bunch of time. It takes years to become an Excel expert, but if you’re trying to justify putting it on your resume you should at least know these 12 shortcuts.
How to Print Pages with a Dynamic Header or Footer – Excel
In Excel you can create a header or footer which includes dynamic data such as the pagenumber or date. However, there is no intrinsic function available to add a subtotal for example. This article shows a method for achieving such. It will calculate the subtotal and grand total for a series of numbers and display […]
Excel Keyboard Shortcuts for Financial Modeling
Below is a list of some of the most important and commonly used keyboard shortcuts which are used in Finance. Learning to use these shortcuts will greatly speed up your work in Excel. Basics & General F2 – Edit Cell F4 – Repeat Last Action Ctrl + Z – Undo Last Action Ctrl + O – Open File Ctrl + […]
Add A Character To Every Cell In Microsoft Excel 2010 / 2013
Sometimes we need to add a Prefix&Suffixto existing data to clearly indicate what the data pertains to and make it more understandable to the reader. If you want to add certain text in every selected cell beforeor after the original cell text, you can follow these steps, rather than editing each cell individually. This will save […]