If you are an office administrator, you probably use Excel. Ditto if you are a salesman. If you are a financial analyst, of course, you need Excel. For these and many other professions, working with spreadsheets is an essential tool in the daily routine.
I like Excel. I use it daily. In companies and homes all over the world, people use Excel for everything – from a makeshift calculator to tracking massive projects. Given the software’s omnipresence, it’s no wonder so many people try to repurpose it for tasks that Excel was never meant to do. I’ll show you how to create an Excel Gantt chart.
These 10 tips will help you do data entry faster and more accurately so you have time to do things that are more fun and fulfilling! Read on to discover our Top 10 Excel Data Entry tips
Excel is software developed by Microsoft and is one of the most essential parts of Microsoft Office. It is a spreadsheet application capable of performing mathematical calculations, storing data in form of rows and columns and a lot of business applications.
Looking to become an Excel power user? Excel has a number of features that will make it easier for you and your colleagues to enter data into your spreadsheets. If you’re developing a spreadsheet that you’ll use over and over again, inserting a spin button or scrollbar will allow you to choose from a predefined […]
Excel puts lots of great tools on your desktop, but what each one of them does isn’t always obvious, especially since so many Excel features are hidden. Would you like to create multiple lines of text within a cell? There’s a keystroke for that. What if you want to reuse a chart’s formatting on another […]
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
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
When you compile data in a list, you often need to answer questions such as “How much revenue did the West Coast office generate last month?” or “What was the average number of customers served at each office in each quarter last year?” Excel’s PivotTables (Microsoft runs the words together, although you?ll find pivot tables […]
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. […]