Whenever I meet a User that complains about knowledge and learning Excel better, I find that most of the time it is because they are shown the difficult side of Excel. With the difficult part, I mean they see functions within functions and get told these functions are easy. This is true if you used each function separately or even if you understand a few concepts of how Excel works.
What is Excel?
Excel gets explained as a Spread Sheet Package or Program for Data Analysis and Documentation. This is all true but I think another way of explaining Excel as a References program.
You enter your RAW Data into the Various sheets and from there everything else you do references back to this RAW DATA. Whether this is a section of the Data or the Data as a whole. Even referencing previous References. Before going too far, let’s work through the layout and some basic features of Excel.
Back Stage View
When opening Excel for the first time it opens in the Back Stage View. This allows various commands to control and manipulate the file. These options range from Creating New Files, Opening Existing Files, Saving Files in different Formats, etc…
The Commands on the left side are the available file management commands. When selecting a command on the left, the right side will give additional options for that command.
An Excel file (or Workbook) can consist of one or Multiple Worksheets. You can see which Work Sheet you are working on by looking at the Tabs at the bottom of the screen. Tabs color can be changed depending on your needs. The Spread Sheet (or Work Area) is where the actual data gets used. This consists of multiple Rows and columns where the Rows are marked Numerically and Columns with Letters. The Corresponding cells then have an address based on the Column and Row value, this is called the Name Box. Example Cell A1. If you played chess before this is the same concept as the Chess Piece Location.
Next to the name box, you get the Formula Buttons which allow the quick use of Formulas in the current Cell. This is followed by the Formula Bar which allows you easy access to edit the Cell Formulas. The Cell formula can also be edited directly on the cell but this is dependent on your preferences as you learn to use Excel to your needs.
Moving up you have the Ribbon. The Ribbon consists of the Menu Structure and the commands. For the Exception of File which will take you to the Backstage view. All other Menu options are intended to group Commands together that have some sort of objective in common. This is then grouped more on the commands Menu on a more granular level.
Not all Menu Items are displayed, there will simply not be enough space to display all Menu items. Rather Menu items that are used most frequently will display by default. When an Item is used that requires an additional Menu it will then display once selected. This is one of the context-sensitive features built into Excel.
Lastly at the very top Excel has the Quick Access options. There are the options that are most commonly used by people and you do not have to go to a specific menu to use them. The Quick access menu is customizable depending on your needs, simply Alternate click (Right Click for most users) and choose the option to add to the Quick Access menu. If this is not a Menu Item you require anymore you can Right-click it again and choose to remove it from the quick access menu.
When hovering the mouse cursor over a command it Gives Help on the Command or Shortcut key.
Excel uses a context-sensitive menu structure. This means depending on what you Alternate-Click you will get a menu Item appropriate to the item you alternate clicked on.
As per the example below, it will open a Mini-toolbar which gives you items that are regularly used to format cells. Whilst giving you the option to modify the full row of data.
Navigation is very simple as Excel responds both to the mouse and keyboard. While learning excel it is encouraged to look at the Navigation options or Shortcut keys.
An example is if you want data to show Bold you can simply use the “CTRL + B”. This will allow you to keep your hands on the keyboard without having to move the mouse to the Bold command on the Home Tab.
If you don’t know a command you can hover your mouse over the command for a few seconds and a Tooltip will display details for the command as well as what the shortcut key is to use. You do not need to know all the shortcuts for all the commands. This is not productive at all. Rather for the command used Regularly.
Not all commands are shown on the Menu items but rather only on the Context-Sensitive menu. Take the “Insert Row” you will not find this on the Insert Tab. But Alternate click the Row number you can insert a Row. This is also possible by Highlighting a Row and Pressing “CRTL & +”. This will automatically insert a Row. Similar when Highlighting a column. If you have selected a Cell it will show a popup requesting what you want to have happened with either the row or the column.
To move quickly through the data on a sheet you can use the “CRTL + directional Arrow”. This will take you to the last filled-in cell before there is a blank cell or a limit was reached. “CRTL+Home” always takes you to Cell A1.
If you take one thing from the little bit you read here today, it should simply be Excel is Big and caters to a lot of people in a lot of different environments.
You only need to learn one thing at a time and very soon you will also be able to compile complex formulas. Excel does not need to be challenging to be efficient.