The Value of Mastering Excel
Stephen Hustedde
Faculty- CIS (Software Development); past Interim Dean of Academic Innovation; and past mCLCTL Division Chair (myCareer, Library and Center for Teaching and Learning) at South Mountain Community College
And using Excel to write programming code.
by Stephen Hustedde
If you’ve taken a basic computing literacy class, you’ve likely explored Microsoft Excel (a bit). But have you mastered it and use it regularly, or are you intimidated by it? Skillset Group, states “If you're looking to enhance your skillset and become more marketable in today's job market, then learning how to use Excel is a must.”[1] Soft skills (teamwork, interpersonal communication, organization, self-learning, and time management) are deemed the most valuable across all industries, but when it come to the technical side, Excel is the most sought-after skill across all job posts.[2] It has been reported that “Recent studies have shown, 82% of jobs require Excel skills and/or experience with productivity software.”[3]
First, a quick history lesson
The first spreadsheet for the personal computer was VisiCalc on the Apple II (1979). The revolutionary power lay in the ability of cell values to automatically update through references to other cells. Watch the 5-minute video, https://youtu.be/2a5ex5QlocQ of the interview with its inventors, Dan Bricklin and Bob Frankston.
?? ???But when IBM launched their first personal computer, they included a similar spreadsheet, Lotus 1-2-3 and it became the spreadsheet king. It is largely regarded as having launched the acceptance of the personal computer, as the critical element in the business sector and especially Wall Street (as the industry driver) embraced Lotus. Stockbrokers and financial analysts saw in both VisiCalc and Lotus a tool that could revolutionize their productivity as well as give them insights in ways they never dreamed of! The “1-2-3” part of the Lotus name referred to three abilities of the program – spreadsheet, graphing/charting, and simple database uses. Version 2.0 of Lotus introduced the ability to write macros (mini programs), adding to its power. My first IT job was as a consultant/trainer at Arizona State University, supporting products such as Lotus 1-2-3, WordPerfect and dBase (database) for faculty and staff. As Microsoft expanded from operating systems (DOS and Windows), to office suite applications, their products took hold. Word overtook WordPerfect, Access surpassed dBase, and Excel knocked Lotus out.? Excel has remained king now for more than two decades. Without the impact and power of spreadsheets the personal computer may have not taken root in business and society.
Excel Skills Checklist
Of course, at South Mountain Community College we offer credit courses focused on Excel that go beyond the short exposure provided in our CIS105 (Survey of Computer Information Systems) course. We offer a three credit Beginning Excel class (CIS114DE) and another three-credit Advanced Excel class (CIS214DE). In a current internship project, I am challenging our students to consider upping their Excel game as a pathway to enhanced employability and both professional and personal productivity. So, what are those “Excel” skills one should have? Here is a checklist for self-assessment:
BASIC EXCEL SKILLS
____ Enter data into cells (numeric and text)
____ Format cells - font, color, size, alignment
____ Format cell displays - general, number, currency, date and time, percent, etc.
____ Change the precision display of decimal values
____ Align cell content horizontally: Left, center, right; Vertically: top, center, bottom
____ Insert/Delete rows, columns and cells
____ Change height of rows and width of columns
____ Add borders to cells
____ Use AutoFill
____ Perform summations and averages on rows, columns, areas
____ Utilize basic formulas and common functions
____ Absolute vs. Relative cell references in formulas
____ Paste special - values, formulas, format, etc.
____ Create basic charts and graphs and format them
____ Sort on Columns (primary, secondary, tertiary sorts)
____ Filter on Columns
____ Save as / Export CSV, etc.
____ Display Formulas
____ Create page breaks for enhanced printing
____ Freeze/split panes
____ Move/Copy/ Add sheets to a workbook
____ Insert shapes and images, textboxes and links
____ Insert and format a Text Box
INTERMEDIATE EXCEL SKILLS
____ Apply Conditional formatting to cells
____ Use Lookup & Reference functions (LOOKUP, VLOOKUP, HLOOKUP, INDEX/MATCH)
____ Use basic Statistical functions (COUNT, COUNTIF, SUMIF, AVERAGEIF COUNTA, COUNTBLANK)
____ Use Text (string) functions (FIND, LOWER, UPPER, LEFT, RIGHT, MID, CONCAT, CODE, CHAR, TEXTSPLIT)
____ Use Logical functions (IF, AND, OR, NOT) and nesting IF functions
领英推荐
____ Use Informational functions (ISBLANK, ISNUMBER, ISTEXT)
____ Perform Date and Time math. Use Data and time Functions (NOW, DAYS, MONTHS, YEARS, TODAY, HOURS, SECOND, WEEKDAYS)
____ Use Financial functions (especially Business, Accounting, Management majors)
____ Concatenation operator (&)
____ Create Pivot Tables
____ Create Pivot Charts
____ VBA macros – recording, editing, writing, assigning
____ Adding Form and ActiveX controls: Textfields, check boxes, radio buttons, etc.
____ Restricting input
____ Protecting cells and ranges
____ Write formulas incorporating data across multiple sheets
____ Perform What if analysis
Using Excel in programming
As a software developer and programming instructor, I use Excel for writing repetitive code for hard-coded data, or to create a small data source (exporting to a CSV Comma Separated Value, or a Tab-delimited). Excel also makes it easy to pre-sort data if you choose.
The repetitive code may be processed using a concatenated equation (or most recently the CONCAT( ) function. Consider that we want to populate a C# list with all the U.S. Presidents as class instances with properties for Number, LastName, FirstName, BirthState, TookOffice (year), and Age. We could populate the presidents list with code such as:
And we could type in the data for all 46 presidents into our code. But, if not using a backend data source, my choice would be to set up the data in Excel and create a formula to generate all the statements. For simplicity, we’ll focus on the first ten presidents.
In cell H4 (the C# statement for the George Washington instance), I wrote a formula of:
? =$B$1 & A4 & $F$1 & C4 & $G$1 & D4 & $G$1 & E4 & $E$1 & "," & F4 & "," & G4 & "));"
It concatenates the values of cells (both relative and absolute references) and a few literal strings to ?add commas and parentheses without quotes. Now that might seem like a lot of work, when it would be easier to just type the result:
? presidents.Add(new President(1,"Washington","George","Virginia",1789,57))
into my C# code directly. And you’d be right. But using the AutoFill to fill the formula down column H downward gives us our 46 presidents (or ten in the case) in a second of time! This process also helps to eliminate any typographical errors or unplanned exclusions.
Now all I need to do is copy the contents of Cells H4:H13 and paste into my code:
Here’s a couple YouTube videos I created on the process:
?
Make An Improvement Plan
How do you get better at Excel, apart from the obvious of taking CIS114DE and CIS214DE at South Mountain Community College?? There are lots of Internet tutorials and videos, as well as video courses at sites like udemy.com and pluralsight.com. Amazon sells many Excel-focused books including some that just focus on VBA macros (a huge topic in itself). Do a Google exploration or search in YouTube for the topic of interest or need, such as “Excel Pivot Table”. A good place to start for the Excel novice, is Microsoft’s Support site where they present a collection of video tutorials: Excel video training - Microsoft Support (https://support.microsoft.com/en-us/office/excel-video-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb ?
END NOTES:
[1] The Most In-Demand Microsoft Excel Skills in 2023 - SkillsetGroup (https://skillsetgroup.com/2023/10/11/the-most-in-demand-microsoft-excel-skills-in-2023/ )
[2] Why Excel Skills Are Important in the Job Market (pryor.com) (https://www.pryor.com/blog/why-excel-skills-are-important-in-the-job-market/)
[3] Excelling in Your Career 9 Top Careers That Require Microsoft Excel Skills (https://www.alliancecareertraining.com/excelling-in-your-career-9-top-careers-that-require-microsoft-excel-skills/)