Introduction
Microsoft Excel is part of the Microsoft Office software that allows users to operate numerical data entered in spreadsheets. The interface of this program includes a table with a large number of cells for data entry and ribbon tabs that give access to various functions. An Excel file is usually called a workbook that may consist of many pages, worksheets. By means of Excel, individuals can convert digits into useful information that may be applied for many personal and professional needs. Apart from simple calculations, Excel is capable of performing functions and formulas, as well as creating user macros with the help of VBA. These capabilities, as well as Excel’s distinctions from Google Sheets, will be reviewed further.
Brief History
Since its invention, Excel has undergone multiple changes in terms of the variety of functions, performance, and capacity. The history of Excel began in 1985 when Microsoft created the first version of Excel available only on the Apple Macintosh (James, 2016). It was the first spreadsheet that had a visual interface and could be manipulated with a mouse (James, 2016). In 1987, the first Excel version for Windows was launched, which allowed users to change fonts and provided extended features for drawing graphs (James, 2016). Three years later, Excel 3 emerged, and its new features were drawing, 3D charts, outlining, and toolbars (James, 2016).
Excel 4, introduced in 1992, had the AutoFill feature, as well as a larger number of functions and shortcuts (James, 2016). The next year, Excel 5 was released, which supported several worksheets and included VBA that allowed users to create macros (James, 2016). Excel 7 followed the fifth version and became part of the Microsoft Office applications for Windows 95 (James, 2016). It was rewritten to 32-bits, which increased its speed and stability (James, 2016). However, the appearance of the program did not undergo any changes.
Beginning with Excel 7, the following versions were integrated into Windows OS. Excel 8, released in 1997, included the paperclip assistant and improved VBA environment (James, 2016). Excel 2000 and 2003 introduced minor upgrades, such as data import from other applications (James, 2016). In 2007, Excel moved to the Ribbon interface, and its capacity increased to approximately 16,000 columns and 1 million rows (James, 2016). In Excel 2010, pivot tables and the Ribbon interface were improved (James, 2016). Excel 2013 introduced some new functions that allowed users to build complicated data models (James, 2016).
In 2016, the new version of the program obtained the Forecast Sheet function and the ability to present data on a 3D map (Gralla, 2019). The latest upgrade that took place in 2019 introduced sich new features as funnel charts, map charts, several new functions for performing calculations, and new keyboard shortcuts (Gralla, 2019). Thus, since its first release, Excel has developed from a relatively simple spreadsheet tool to complex software with multiple useful features.
What Excel is Used for
Numerical data has a lot of applications in many fields, and Excel provides convenient tools for processing and presenting these data. The most obvious and simple use of this program is for calculations. Excel allows for obtaining computational results of complex equations, create different models and see how they work with diverse inputs (The Science Education Resource Center at Carleton College [SERC], 2018).
This program is also used for a graphical presentation of data and analytical models by means of graphs, charts, diagrams and histograms (SERC, 2018). Excel is a useful tool for statistics because it provides such features as “mean, standard deviation, error bars on graphs, linear and polynomial fits, multivariate analysis, etc.” (SERC, 2018, para. 7). Hence, this software is much more than a table with a complex built-in calculator.
Excel has found its implementation in a business where there is a great need for various computations and planning. For example, this program is a perfect instrument for organizing and storing customer databases that later can be used for marketing campaigns (CLIMB Professional Development and Training [CLIMB], 2017). Furthermore, people may use Excel to plan events or other working processes by entering tasks to be done and persons responsible for their execution into a table (CLIMB, 2017). This software is also a powerful tool for managing a budget due to a number of useful functions that automate calculations, thus saving time and reducing the possibility of mistakes (CLIMB, 2017). The scope of application of Excel is so broad that it makes it an indispensable instrument in many fields.
What a Spreadsheet is
Excel is only one of the spreadsheet programs, which evolved faster than its competitors and managed to take the largest share of the spreadsheet software market. Generally, a spreadsheet is a number of cells that are organized in rows and columns and contain text, numeric values, or formulas (The Data Labs, 2019). With the invention of computers, spreadsheets have replaced paper worksheets (The Data Labs, 2019).
These grids can are traced back to 1971 when Pardo and Landau patented an algorithm that presented a new way of processing data (The Data Labs, 2019). Supposedly, the first spreadsheet was VisiCalc, a program that contributed to the success and proliferation of the Apple II computer (The Data Labs, 2019). Before Microsoft developed its product, the leading spreadsheet program was Lotus 1-2-3 (The Data Labs, 2019). Then, the world saw MS Excel that gained popularity and made Microsoft the major supplier of the spreadsheet software.
Data Types, Formulas, and Functions
As was already mentioned, the Excel spreadsheet consists of cells that are meant for data input. There are four major data types that a cell can hold: text, date, number, and boolean (French, 2019). Alphabetic letters and strings are assigned the text type; they cannot be used in calculations but can be formatted, that is, their font size and style, color, and cell borders can be changed (French, 2019). The boolean type is “a result of a logical comparison, such as TRUE or FALSE” (French, 2019, para. 8). Apart from that, a cell can hold a formula, but the user will see the result of a calculation in its place (French, 2019).
Finally, if there is an incorrect formula or data in a cell, it will obtain an error value (French, 2019). In Excel 2019, new data types were introduced: stocks and geography, which show information about stock markets and countries, respectively (Microsoft, 2019). They are connected with an online source of data and, therefore, are called linked data types (Microsoft, 2019). Data types define how Excel displays and manages the information in the cells.
Excel performs calculations by means of different formulas and functions. A formula is a feature that allows for using Excel as a calculator, that is, for dividing, multiplying, subtracting, and adding numbers (Goodwill Community Foundation [GCF], 2016b). Generally, a formula can be defined as a set of instructions that a user types into a cell. At the beginning of any formula, there should be an equal sign followed by cell addresses and necessary mathematical operators (GCF, 2016b). The mechanism of cell references, meaning that the user types cell addresses instead of values into a formula, allows for changing the cell content without the need for altering the formula (GCF, 2016b). This feature helps people to automate repeated computing operations, thus saving time and increasing productivity.
Developers of Excel have made calculations even more convenient for users by introducing functions. A function refers to a built-in formula that is used for more complex computations (GCF, 2016a). Each function has its function name, for example, SUM or AVERAGE (GCF, 2016a). To apply this feature, the user should enter an equal sign, the function name, and one or several arguments in round brackets (GCF, 2016a). Arguments are either single cells or a set of cells, and if there are several of them, they are delimited by a comma (GCF, 2016a). For instance, if one wants to add the values of cells A1, A2, A3, and E1, one should type =SUM(A1:A3, E1) (GCF, 2016a). This will result in the sum of the numbers typed in the selected cells.
The Software and Features
MS Excel software is available on Windows OS and Mac OS. Apart from that, there are applications for Android and iOS. Despite the availability on the most popular operating platforms, the program functionality is not the same on each of them. For example, the navigation is different, which makes it challenging to switch among operating systems (Ali and Fitzpatrick, 2019). Furthermore, Excel on Mac lacks a number of significant features, such as flash fill, table slicers, timeline filter, pivot tables, power pivot, a complete VBA editor, etc. (Ali and Fitzpatrick, 2019). Thus, the entire spectrum of Excel features is available only on Windows OS.
The already broad functionality of Excel can be expanded with the help of add-ins. These programs add specific features related to data analysis, business, investment, productivity, and other fields (Ozgur et al., 2017). For example, popular add-ins for data analysis include MegaStat, Solver, and Analysis Toolpak (Ozgur et al., 2017). MegaStat adds to Excel such statistical features as frequency distribution, hypothesis tests, probability, and the ability to generate random numbers (Ozgur et al., 2017). Add-ins are a convenient way of making Excel more functional if there is a need for some more professional features.
An Example of Using VLOOKUP Function
Nevertheless, Excel is a powerful tool, even without any additional programs. One of its most useful features is the VLOOKUP function that allows users to search for particular data within a spreadsheet (GCF, 2019). The name of the function stands for “vertical lookup” because the function performs a top-down search (GCF, 2019, para. 18). Its syntax consists of an equal sign, VLOOKUP, and four arguments in parentheses: the name of the item, the cell range that stores the data, the column index, and the type of matches (GCF, 2019). The function always searches the first column in the specified range and retrieves information from the column that is to the right of it (GCF, 2019).
To identify the column index, it is necessary to remember that the left-most column in the specified range is considered the first (GCF, 2019). The fourth argument can have a value of either TRUE or FALSE (GCF, 2019). If the value is FALSE, the function will search for exact matches; otherwise, it will try to find approximate matches (GCF, 2019). This function is important for managing spreadsheets with a large number of data.
An example of applying the VLOOKUP function will help to understand its operating principle better. Supposing there is a pricelist consisting of many rows and three columns. Column A includes items; column B holds prices, and column C has categories. Assuming one wants to find the price of a greeting card that is somewhere on the list. At this rate, the function will be as follows: =VLOOKUP(“Greeting card”, A2:B16, 2, FALSE) (GCF, 2019).
The function will try to find the item with the exact name “Greeting card” in column A. If there is such an item, it will show the value stored in the second column to the right of the sought-for item. To avoid editing the formula for each particular item, one can use a cell reference (GCF, 2019). In the example above, the name “Greeting card” could be entered in a cell E2, and the formula would look like this: =VLOOKUP(E2, A2:B16, 2, FALSE) (GCF, 2019). Although using VLOOKUP function is easy, it significantly facilitates the work with large Excel spreadsheets.
Macro Programming
Excel has one more powerful feature that is called Visual Basic for Applications (VBA). It is a programming language used for writing macros, that is, individual software solutions for repeated tasks (Häcker and Ernst, 2017). There are two ways of making macros in MS Excel. The first method is the use of the macro recorder, a tool that records each user action done with a mouse or a keyboard (Häcker and Ernst, 2017). It is helpful for simple routine tasks without loops and complicated branches in commands (Häcker and Ernst, 2017). The second method is VBA programming in the development environment (Häcker and Ernst, 2017).
It is opened “with the key combination Alt+F11,” after which the user can enter commands for creating macros (Häcker and Ernst, 2017, p. 251). This way is more difficult because it requires some programming skills; yet, it allows for more complex macros able to solve challenging problems (Häcker and Ernst, 2017). Once a macro is created, it can be run with a few clicks or a keystroke if the user added a shortcut key to it.
The use of macro programming makes work in Excel even more convenient. The major benefit of VBA is that it saves much time necessary for executing repetitive tasks (Häcker and Ernst, 2017). Once made, a macro can be used many times, thus reducing the possibility of mistakes and allowing for identical task solutions and result presentations (Häcker and Ernst, 2017). Furthermore, ready-made macros let less experienced users perform difficult operations (Häcker and Ernst, 2017). Another benefit is that the possibility of creating individual solutions eliminates the need for additional software (Häcker and Ernst, 2017). Eventually, VBA is not a very complicated programming language, so it is quite easy to master (Häcker and Ernst, 2017). Thus, macros make Excel a more personalized and timesaving program.
Pivot Tables
One more Excel feature that is worth discussing is pivot tables. A pivot table is an instrument that summarizes large databases and presents data in a way that is comfortable for analysis and comparison (Hai-Jew, 2019). To make a pivot table, it is necessary to select datasets of the initial database, which may be of concern (Hai-Jew, 2019). After choosing the needed rows or columns, the program will draw a report, the main feature of which is interactivity (Hai-Jew, 2019). It means that users can apply filters to explore data, compare different datasets, or make graphs, charts, or histograms based on pivot tables (Hai-Jew, 2019). This feature is a helpful statistics tool that facilitates the analysis of spreadsheets with up to a million rows.
Differences between MS Excel and Google Sheets
Although MS Excel is a leader in the field of spreadsheet applications, in 2006, Google Sheets emerged and became its main rival. This section will review the major differences between the two programs. The first distinction is the cost; while Google Sheets is free, the price of Excel is $6.99 per month as part of the MS Office suite or $129.99 as a separate program (VanSomeren, 2018). Secondly, Google Sheets always saves changes automatically, while Excel has the AutoSave function only for files stored on MS OneDrive, a cloud storage program (VanSomeren, 2018). Thirdly, there is a big difference in the capacity of these two applications.
Google Sheets can store data in 5 million cells at most, but the greatest number of cells in Excel is 17,179,869,184 (VanSomeren, 2018). Furthermore, if Google Sheets has to handle a large database, its processing speed will be quite low, which cannot be said for Excel (VanSomeren, 2018). For this reason, Microsoft’s program is preferable in cases when a lot of data should be processed.
There are also differences in customization and ease of use. In Excel, users can place the most frequently used features on the toolbar to get quick access to them (VanSomeren, 2018). They also may create their own key shortcuts, as in the case with macros. In Google Sheets, users have to content themselves with the basic interface and shortcuts that cannot be changed (VanSomeren, 2018).
As for the ease of use, Google Sheets is considered a more user-friendly program due to a simpler interface, while it usually takes time and effort to learn Excel (VanSomeren, 2018). Despite all the differences, these applications have many things in common. Both of them provide the possibility for collaborative work and keep user data secure (VanSomeren, 2018). Such features as macros, charts, pivot tables, and useful functions for budgeting are available in both programs (VanSomeren, 2018). Overall, Google Sheets seems to be perfect for personal financial planning, while Excel is suitable for large-scale projects.
Conclusion
To sum up, Excel, spreadsheet software developed by Microsoft, is a helpful tool that can be of value to anybody working with statistics, budgeting, data analysis, computations, and event planning. It is quite challenging to master, but, once it is learned, it provides the user with numerous capabilities. Such features as charts and pivot tables enable users to analyze and graphically present large datasets. Multiple built-in functions, as well as VBA programming and macros, save much time and help to avoid errors. Moreover, if one lacks some narrowly-specializes features, one can find various add-ins to expand the functionality of the program.
Reference List
Ali, S. I. and Fitzpatrick, B. D. (2019) ‘Teaching Excel to undergraduate students: do the platform constraints of Windows versus Mac make a difference?’, Journal of Education for Business, 94(7), pp. 1-3.
CLIMB Professional Development and Training (2017) Why Excel is an indispensable business tool. Web.
The Data Labs (2019) History of spreadsheets. Web.
French, T. (2019) The definition of a value in Excel. Web.
Goodwill Community Foundation (2016a) Excel 2016 – functions. Web.
Goodwill Community Foundation (2016b) Excel 2016 – intro to formulas. Web.
Goodwill Community Foundation (2019) How to use Excel’s VLOOKUP function. Web.
Gralla, P. (2019) Excel 2016 and 2019 cheat sheet. Web.
Häcker, J. and Ernst, D. (2017) Financial modeling. London: Palgrave Macmillan.
Hai-Jew, S. (2019) ‘Creating a simple pivot table in Excel 2016’, C2C Digital Magazine, 1(10). Web.
James, J. (2016) Version history of Excel. Web.
Microsoft (2019) Excel data types: stocks and geography. Web.
Ozgur, C. et al. (2017) ‘Selection of statistical software for data scientists and teachers’, Journal of Modern Applied Statistical Methods, 16(1), pp. 753-774.
The Science Education Resource Center at Carleton College (2018) Why use Excel. Web.
VanSomeren, L. (2018) The ultimate Excel vs Google Sheets comparison. Web.