PDF and Power BI
Looks like Power BI will soon be able to extract data from tables in PDF documents.
Great to see requests being acted on.
Monthly Archives: August 2017
Katherine S. Rowell, M.S., M.H.A.
2017-08-29
2017-08-29
Reduce Excel File Size
Ctrl + End is your friend
I recently helped a client reduce the size of an Excel file. The file took a while to save which was frustrating and time consuming. I thought I would share this reasonably easy solution.
Excel Filtering for Blanks or Zeroes
Making the filter work across multiple columns
If you want to filter by blanks across multiple columns the standard Filter feature can’t help you. You can use the Advanced Filter but that takes time to set up and most users don’t know how to use Advanced Filter.
Filtering Tip
Make your headings bold.
This tip applies to tables and to the structures you use for charts.
Excel looks for the bold format when it reviews tables and layouts to figure out if your table has a headings row.
You can use Ctrl + Shift + L to add or remove the filter icons to a data table. There is also an icon on Data ribbon tab.
This will work more reliably if the headings are bold.
I use the following keyboard combination on the top left corner of the table.
Ctrl + Shift + right arrow (this selects all the headings)
Ctrl + b (this applies bold to the headings)
Ctrl + Shift + L (to turn on filters)
This combination can be done very quickly.
You can just use Ctrl + Shift + L within the table, but sometimes this applies the filter to the wrong row.
Filter by Cell Value Keyboard Shortcut
Its quicker to use the keyboard
I have found a keyboard shortcut combination to one on my favourite right click options and its quicker to use.
Replace Merged Cells with Center Across Selection
Let the macro do the work
Unfortunately lots of people use the Merge & Center format in their spreadsheets. When working with other people’s files that contain Merged cells I will often remove the Merged cells format and apply Center Across Selection which is the preferred format to use. The macro below will convert Merged cells to Center Across Selection.
If you are ever tempted to start a column (bar) chart at a point other than zero, please read this post.
It also has suggestions for alternative chart types to handle comparisons.