I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones.
Category Archives: Excel Blog
Export a sheet as a PDF
It takes a few clicks but it is possible
I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)
Excel Filter Trick
Saves a few mouse clicks
Do you use the “Filter by Selected Cell’s Value” option? If you do then you will be pleased to know there is a Quick Access Toolbar icon that applies it in one click.
Converting a Tricky Date Format
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
Converting a number to a text number in Excel
An easy formula
If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.
Creating a Hyperlink in Excel Based on a Search
Teleport to a cell in a data table
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
Totalling Tip For Excel
Make the most of SUMIF
I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
Excel Full Screen
Maximise your grid area
There are times in Excel when you want to see as much of the Excel grid as possible. You may be reviewing or presenting a dashboard or looking at a large PivotTable. Excel has a Full Screen mode that shows just the grid and the sheet tabs.
Using Lists in Excel
Make the most of Format as Table
It is common to work with lists in Excel. Lists of departments, names and other categories you frequently use. This blog post covers a few techniques that work really well together to create robust reporting systems.
Excel Cell Comments and Documentation
Macro to Create a Comment Report for a File
I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.
Chart Suggestions
When less is more
Just because you can, doesn’t mean you should. This applies to many thing in Excel and especially to charts. With charts the “less is more” philosophy works well. Have a look at the four charts in the image below.
Excel and pdf
Creating a pdf from a single sheet
In Excel you can us Save As to save a file as a pdf, but it isn’t quite as effective in Excel as it is for MS Word. Often you only want to save a single sheet or a few sheets to pdf. Try this.
Disabling Sheet Unhide in Excel
How to hide a sheet and make it hard to unhide
You can right click a sheet tab and select Hide, but it is just as easy to Unhide the sheet. What if you want to make it harder to unhide the sheet?
VLOOKUP and COLUMN Function Warning
Be very careful using these two together
I saw a technique demonstrated recently with VLOOKUP that I hadn’t seen used before and thought at the time, that’s handy. Upon reflection however, I thought that’s a bit dangerous.
The Handy Functions in Excel – LEFT and RIGHT
Simple Defaults
The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too?
Sorting By Colour in Excel
It is possible
Yes, you can sort by colour in Excel! This feature makes it easy for you to colour code cells and then place them together at the top of your data set.
Power BI – A Chart
The ultimate goal
Dashboard Charts are the ultimate goal of most Power BI reports, so let’s dive in.
Vote For a New UNIQUE Function
Dynamically extract a list of unique entries from a list
The Excel team has a great site that encourages people to post new ideas for Excel. It also encourages people to vote for the new suggested features.
I have just posted an idea for a UNIQUE function that extracts unique entries from a list.
Currently you have to manually maintain a separate list of entries for a drop down lists. You can’t use a list that contains duplicates for a drop down list.
What if that list was updated automatically via a formula from the original data source? So as new items are added at the data source they automatically appear in the drop down list?
Currently a dynamic solution requires a complex array formula or a UDF (User Defined Function – macro).
This would also make creating formula-based reports so much easier.
Please take the time to vote for my suggestion. Feel free to post your own suggestions as well.
Creating a Report in Power BI
Converting Data into Information
In the previous post we extracted the data from a CSV file. Now let’s create a report. It won’t be the greatest report as the data is pretty basic, but at least its a start.
There are lots of Excel blogs out there.
This page lists a lot of the best ones (unfortunately my site isn’t listed yet – but you never know).