Keyboard Shortcuts for Excel
We're going to start our list off with a few super simple keyboard shortcuts. There's a lot of keyboard wizardry that you can employ in Excel that will make working on spreadsheets an absolute doddle. Master these simple shortcuts and you will be an Excel pro in no time.
Ctrl + 1 - This shortcut will allow you to format any selected cells or objects
F4 - Doing something repetitive? The F4 key will simply repeat your last action and eliminate the need to keep clicking over and over again.
Ctrl + Shift : - This handy shortcut will input the current date into any selected cell
Ctrl + Shift + : - Similar to the above, however, this variation will input the current time instead
Shift + F11 - This one will insert a new worksheet into your Excel document
Ctrl + 9 - Pressing this combination will hide the current row you're editing
Ctrl + 0 - Similar to the above, but this one will hide the column instead of the row
Ctrl + Shift + 9 - This one will unhide the previously hidden row, best not to learn one without the other!
Ctrl + Shift + 0 - Again, a quick way to unhide your previously hidden column
Ctrl + Shift + 4 - Use this little hack to format any numbered cell into currency
Ctrl + Shift + 7 - Apply a border around the outline or any selected cells with this shortcut
F9 - Just this one key will recalculate your formulas, it couldn't be easier
Ctrl + Z - If you tend to make a few mistakes whilst working in Excel, this one is invaluable. Clicking Ctrl + Z will undo your last action.
Alt + F8 - This quick shortcut opens up the macros dialogue box
Ctrl + ’ - Use this one to quickly copy the value from the cell that's above it
Ctrl + Shift + 1 - If you need to format a cell into comma formats this shortcut does exactly that
Ctrl + Shift + Page Down Key - This one will quickly tab you across to the next worksheet
Ctrl + Shift + Page Up Key - This one will do the reverse of the above and take you back to the previous worksheet
Alt + = - Select any numbered cells and use this shortcut to auto-sum them
Alt + Enter - If you need to add multiple lines of text in the formula bar, use the Alt key and Enter to avoid tabbing to the cell
Ctrl + A - A quick way to select every cell within a single worksheet, no more dragging. Especially handy if you need to copy your data into another worksheet.
Ctrl + W - Quickly close a spreadsheet
Shift + F2 - You can add comments to cells for your own personal notes. This shortcut will allow you to add new comments or edit existing ones.
Ctrl - Shift - % - This one will add the percentage format to any numbered cell
Ctrl + K - Add a hyperlink to a web page to any cell using this shortcut
F7 - A quick way to run a spellcheck on your active worksheet
Shift + Arrow Keys - This allows you to extend the selection of cells one by one until you have the amount you need
Ctrl + Spacebar - Select the entire column in a worksheet without having to scroll up and click
Shift + Spacebar - Select the entire row in a worksheet without having to scroll across and click
Ctrl + Shift + U - Collapse or expand the formula bar at will using this formula
Ctrl + S - Quickly save your worksheet, it's advisable to do this every so often to avoid losing work should your programme crash
Ctrl + C - Copy the information contained within one or more cells
Ctrl + P - Paste the information that you have copied, in Excel this will be done in the same amount of cells the data was copied from
Alt + H, H - Choose a fill colour for your cell, handy if you like to colour code your worksheets
Ctrl + X - A simple shortcut to help you cut data
Ctrl + B - Make the data contained within the cell (or cells) bold
Ctrl + I - Make the data contained within the cell (or cells) italicised
Ctrl + U - Make the data contained within the cell (or cells) underlined
Alt + A - This shortcut allows you to open the data tab from the top navigation
Alt + W - Open the view tab from the top navigation using this shortcut
Shift + F10 - Open the context menu
Alt + M - Open the Formula tab using this shortcut
Ctrl + 1 - Open the format cells dialogue box
Ctrl + Shift + # - This shortcut applies the date format with day, month and year
Shift + F3 - Insert a function without having to navigate through the menu
Alt + F1 - Create an embedded chart of the data within the selected cell ranges
F11 - Create a chart of the data in a selected range in a separate sheet
Alt + F8 - Use this to create, delete or edit a macro in your spreadsheet
Alt + H - This will open the home tab
Alt + R - Quickly open the review tab
Productivity Hacks for Excel
It's all about saving time whilst working in Excel. There are a lot of ways to waste your time if you don't know the clever hacks that can be employed. Productivity hacks for Excel are essential to ensure you are doing things in the most efficient and simplest way possible.
Autofill - Dates, numbered lists, days of the week etc. Often you will need to input these into an Excel sheet, and if you're doing it one by one you're doing it wrong. This quick hack can save you plenty of time. Simply input the first value, whether it's a date or something else, then select the little + icon in the bottom right of the cell and drag it down, the cells will automatically format.
Copy formulas across multiple cells - You can use the technique in the point above to also copy a formula across multiple cells. Simply insert the formula once and check that it works, tab away and you should see the little + icon in the bottom right corner. Click and drag across the desired cells.
Add multiple new rows or columns at once - If you need to add multiple rows or columns to your worksheet it can be laborious to do it one by one. Add as many as you need by simply highlighting the amount of columns or rows you need and then right-click and insert. You should then have the same amount that you highlighted.
Filter data - If you have a mammoth spreadsheet filled with lots of data then this one can be extremely handy. Simply select the Data tab and click the 'filter' icon that appears in the navigation. This will give each column a clickable drop-down. Click it on the column you want to filter and select the desired options from the drop-down list.
Format Painter - This is another way to save yourself some time from laboriously formatting individual cells in the same way. Select a cell that has the same formatting and click the 'format painter' icon from the top navigation (it's the little paintbrush). Now, click (or drag across) any cell you want to share that formatting and it will automatically update.
Select all cells - If the shortcut option above isn't easy enough for you, you can actually select all cells simply by clicking the button in the top right of your worksheet.
Basic Formulas for Excel
It's essential Excel knowledge to know your formulas. Formulas are functions that you can insert into cells to automate a wide variety of actions. There are so many that you can learn, but for the purposes of this article we're going to focus on a few of the most common you might need to be an Excel pro.
Note: Many of these require selecting cells which you will need to change to suit your needs. We'll just use dummy cells for now as examples, but make sure you edit them for your own purposes.
SUM Formula - This formula does exactly as you would expect and allows you to add the sum of two or more cells together. There are a few ways to do this: =SUM(5, 5) - Will add the two values in brackets. =SUM(A1, B1) - Will add the two values of the cells in brackets. =SUM(A1: B5) - Will add the total value of all cells in the range within the brackets
COUNT Formula - This one will simply count the number of cells within a range that have numbers in them: =COUNT(A1:A10)
LEN Formula - This formula allows you to count the number of characters within a cell (including spaces): =LEN(A1)
TRIM Formula - If you're familiar with Excel, you might know that you often end up with extra spaces in a cell when you copy over text values. Use this formula on one or more cells to remove stray spaces: =TRIM(A1)
IF Statements - An IF statement will allow you to add complex scenarios to sort your data within excel: =IF(Scenario data)
CONCATENATE - This rather daunting term is actually just a fancy way of combining the data in two or more cells into one cell: If you had the name 'Ben' in cell A1 and the surname 'Smith' in cell A2, using =concatenate(A1, A2) would combine them to 'BenSmith' in the chosen cell.
UPPER formula - use =UPPER(A1) to transform the text in a selected cell to upper case, this is handy if you have multiple cells in which you need to change the case.
LOWER formula - Another simple formula that works the opposite way to the UPPER formula. Just use =LOWER(A1), for example, and the case will change to lower.
More Tricks and Tips for Excel
Here are some additional tips and tricks you can learn to use in Excel:
Press Alt + Enter to force a carriage return in a cell instead of wrapping the text
Easily transpose data – Copy data > Paste Special > Transpose
If you want to use the Format Painter more than once all you need to do is double-click the Format Painter icon and when you're done simply press the ESC key.
To combine text from more than one cells you can enter your formula with an ampersand ‘&’ between the cell references e.g. =A1&A2&A3
Delete the blank cells from a row or column by highlighting the column containing the cells you want to edit and press Ctrl + G to open the Go To Dialog Box > Special > Blanks. Delete cells, rows or columns.
Open all of your excel files in one go by highlighting all of the files with your cursor, right-click and select open. No more loading files individually.
If you need to merge your cells you can do so by highlighting the desired cells, right-click and select 'merge cells'. This is handy for organising your spreadsheet.
Fast navigation is especially handy with large worksheets. Whilst holding the Ctrl key use any of the four directional arrows to jump to the edge of your Excel worksheet in the direction of the key you use. Note, this only works if each cell contains data.
When you input values that start with a zero Excel will automatically delete them. To avoid this you can simply add a single quote mark ahead of the zero.
Create your own text shortcuts using the autocorrect function. If there's a value that you often need to input, go to File->Options->Proofing->AutoCorrect Options and set up an autocorrect function. For example, if you have to input the same names a lot you can set up a function that allows you to input initials instead and Excel will autocorrect to the full name.
Organised sheets are the key to an effective workbook. If you have many sheets within one workbook it's essential that you name them appropriately to ensure easy navigation. You can do this simply by double clicking on the desired sheet and by inputting a memorable name. You also have the ability to colour code these tabs.
Chart Types for Excel
You're possibly already familiar with using charts in Excel. If you are fed up of staring at numbers on a screen they can be a great way to visually represent your data in an easy to digest format. They are also incredibly useful if you need to present your data to other people without overloading them with numbers and statistics. Most Excel users are aware of one or two different chart types that are available in Excel, but if you really want to impress people you need to familiarise yourself with all of the different types that you can use.
To use charts, simply highlight the cells that contain information you want to use and select 'charts' from the Insert tab. From there you will have a drop down option that includes the following chart types:
Column Chart - The column chart is one of the most well-known options on Excel, it typically will represent your categories on a horizontal axis and your data values along a vertical axis.
Line Chart - A line chart is ideal for showing data and trends over time as they use an evenly scaled axis to show continuous data.
Pie Chart - Use pie charts for showing representations of percentages in a round chart for a simple visual representation.
Doughnut Chart - Very similar to a pie chart with one main difference, a doughnut chart will allow you to use more than one data series.
Bar Chart - Fantastic for your comparative data needs. Bar charts typically display category data along a vertical axis and your values will be stacked side by side along a horizontal axis.
Area Chart - These are great for illustrating changes over time and representing your data as trends by showing the sum of your plotted values.
XY (Scatter) Chart - This option is best used for comparing values, often used for scientific purposes as the data is displayed at intervals (or clusters).
Bubble Chart - Much like an XY Scatter chart, with the added benefit of a third column to specify the size of your bubbles.
Stock Chart - Named as its key use is to demonstrate fluctuations within the stock market. However, a stock chart can be used to show fluctuations in other types of data too.
Surface Chart - Surface charts are especially useful when you want to find the optimum combinations between two sets of data.
Radar Chart - Comparing aggregate values of several data series is incredibly simple with a radar chart.
Combo Chart - When you have widely varied data it can become necessary to use more than one chart type. A combo chart will allow you to do exactly this. Slightly more advanced but it adds a touch of wow factor in presentations.
Conditional Formatting for Excel
Formatting your cells can have many useful uses for organising a worksheet in Excel. For example, you may want to represent cells above a certain value as one colour, and values below that number with another. However, it can be a long and laborious task to go through and format cells one by one. With conditional formatting, you can create a series of rules that will apply your chosen format on the condition that it meets certain criteria.
In order to use conditional formatting, first, highlight the cells you would like your rules to apply to and select 'conditional formatting' from the format menu. Here are some of the rules you can set:
Cell is Empty/Not Empty - Need to highlight empty or non-empty cells on your Excel worksheet? You can set a rule that will do exactly this.
Text contains - Create a rule that will pick up on a text value within your worksheet and apply formatting if it finds the text you have specified within it.
Text does not contain - The opposite of 'text contains', this allows you to negatively format cells based on them not containing your chosen text value.
Text starts with - If you need to format cells that start with a specific text, be it a single character or more you can do so using the 'text starts with' rule.
Text ends with - Again, a simple way to format cells that all end with a specific character or characters.
Date is - If you're working with date ranges and need to highlight a specific date in your worksheet you can use the 'Date is' rule.
Date is before - Format all cells that contain a date value that's before a date value you have specified.
Date is after - Format all cells that contain a date value that's after a date value you have specified.
Greater than - This will allow you to apply formatting to any value in your selected data that's greater than a number value you have specified.
Greater than or equal to - Using greater than can leave a slight grey area, use 'greater than or equal to' to include the value you have specified as a rule.
Less than - Apply formatting to cells that are less than the numerical value you have specified.
Less than or equal to - Again, if you need to include the value you have specified in your conditional formatting rule, use 'less than or equal to'.
Is equal to - Format cells that contain the exact value you have specified.
Is not equal to - Exclude cells from formatting that do not meet the exact value you have specified.
And there you have it, no less than 101 hacks that you need to learn in order to become an Excel pro. This is by no means an exhaustive list, but if you can learn these you will be well on your way to being an all knowing Excel master.
However, if you would much rather have somebody else take care of your Excel needs, we can help.
Contact us today to see how our Excel experts can help with your business.