April 22, 2019
The power of pivot tables is the ability to rearrange the fields. If your manager decides you should put Regions across the top and products down the side, it is two drags to create the new report.
April 18, 2019
Pivot tables let you summarize tabular data to a one-page summary in a few clicks. Start with a data set that has headings in row 1. It should have no blank rows, blank columns, blank headings or merged cells.
April 17, 2019
A new artificial intelligence feature debuted in Office 365 in early 2018. Originally called Insights, the feature was re-branded as Ideas and moved to the far right side of the Home tab of the Ribbon by September of 2018. Excel Insights will analyze your Excel data to search for patterns and return a series of interesting facts or trends about the data.
April 15, 2019
There are two ancient consolidation tools in Excel. To understand them, say that you have three data sets. Each has names down the left side and months across the top. Notice that the names are different, and each data set has a different number of months.
April 11, 2019
Conditional formatting got a lot of new features in Excel 2007, including icon sets and more than three levels of rules. This allows for some pretty interesting formatting over a large range. But once you format the cells, you might want to quickly see all the ones that are formatted a particular way. In Excel 2007, sorting and filtering were also updated to help you do just that!
April 10, 2019
It is a little odd that Subtotals only bolds the customer column and not anything else in the subtotal row. Follow these steps to format the subtotal rows:
April 8, 2019
Kimberly in Oklahoma City and Sarah in Omaha combined to provide a faster solution to getting the sales rep to appear on the Subtotal rows. Provided you only need the data in the #2 Summary View, this works amazingly well.
April 4, 2019
Say that each customer in a data set is assigned to a single sales rep. It would be great if you could bring the sales rep name down to the subtotal row. Here are the steps:
April 3, 2019
For those of you who have never used subtotals, here is how to set them up.
April 1, 2019
Every day, your IT department sends you a file with the columns in the wrong sequence. It would take them two minutes to change the query, but they have a six-month backlog, so you are stuck rearranging the columns every day.
March 29, 2019
At my last day job, we had three sales regions: East, Central, and West. The company headquarters was in the East, and so the rule was that all reports were sorted with the East region first, then Central, then West. Well, there is no way to do this with a normal sort.
March 28, 2019
Three easy visualization tools were added to the Conditional Formatting dropdown in Excel 2007: Color Scales, Data Bars, and Icon Sets.