Monday, August 8, 2022

Create a Dynamic Chart Title Sourced from Pivot Table & Slicer

Pivot Tables are one of the best analytical tools in Excel. To enhance and better present the data / information to your audience, you'd need to have a chart to visualizes the data. You may also have a meaningful title, but what if you want to change the Pivot Table? You'd think that you might need to manually change the title to reflect the pivot table chart. Nope! You can use slicers and an additional table to create dynamic text to put into your chart title.

Monday, May 9, 2022

Create Four Column Layout in SharePoint

The available section layouts in modern SharePoint is a three column layout. But what if you what to have more columns, like a 4 column layout? There's actually an option to do this by using the the vertical section option when adding a section to a page. There's some limitations to the view or design of the whole page, but if you want a four column layout on your Share Point page, this is a nice workaround for your design.

Monday, May 2, 2022

Create Custom Sort Order Pivot Table

Did you know you could have a custom sort order for pivot tables? You can customize a sort order when using the drop down sort features to enable a sort like bronze, silver and gold. Or in this example green, blue or diamond; which corresponds with ski slope difficulty in some resorts. You'll have to customize a sort order but it's easy.

Monday, April 25, 2022

Quickly Calculate Investment Return

If you're given an scenario where you have the present value of an investment and a future value of that investment with not periodic investments for five years, could you figure out a rate of return per year for that investment? There's a common business concept of Compounded Annual Growth Rate (CAGR) and and there's an Excel function called RRI that will help you calculate it

Monday, April 18, 2022

Create a Dropdown List

Creating dropdown list is useful when you can to make it easy to select items from a list. Whether you use it for a dashboard or for you stakeholders to make a selection for other types of interactivity, it's fairly easy to create.

Monday, April 11, 2022

Quickly Sum or Average without Formulas

If you wanted to do a quick sum or average of your rows or columns of data, you'd think that selection of the cells and writing a quick SUM or AVERAGE function was what needed to be done. However you can get the sum or average without writing any formulas. Just select your data and look at the taskbar. There a bunch of other things it can do besides sum or average like count, min and max.

Monday, April 4, 2022

Quickly Create Subtotals

Creating sub totals in Excel can be done many ways. One favorite is to use Pivot Tables; but if you don't needs all the extra fancy capabilities of the Pivot Table just use the Sub Total command. All you need to do is just sort a main column for the group you want to subtotal and then use the Sub Total command to take care of the rest.