There are crucial processes such as accounting, tracking in your e-commerce business. Achieve these with a single program: Microsoft Excel.
As a manager, your general knowledge of Microsoft Excel will be tested in these job interviews. At the same time, you might be asked detailed questions about the functions and the tools Excel contains as well.
Question 1: What are the most used data formats in Microsoft Excel?
Different data formats are one of those questions that you should know the answer no matter whether they ask you in the job interview or not. You will be dealing with these formats of data daily in your workplace as a manager.
A sample answer for different data formats in Excel can be: “Although there are more than 10 data formats in Microsoft Excel, the most used ones are text, number, date, currency and percentage formats. These were the most common ones that me and my team used.”
Question 2: What is conditional formatting?
As you will see later in this blog post, conditional formatting is a must have feature while using Microsoft Excel. Excel thrives on customization; therefore, conditional formatting is one of the best functions in order to utilize some of the customizable options of Excel.
A sample answer on conditional formatting can be: “Conditional formatting is a feature in Microsoft Excel which helps to format and customize your cell. You can format your data in the cell as you can change colors, font types and size.”
Question 3: What are the cell references?
If you are applying for a manager position, it means that you are applying to manage a team in that company. Managing a number of employees in a team consists of multiple tasks; however, one of the most important one is ‘checking’. Checking means spotting the errors your employees -which you are responsible for- made. That means you need to have more knowledge than your employees and also, more experienced in using a program such as Microsoft Excel. That is why these types of very detailed questions are important for the interviewer to see whether you are experienced enough to manage a team.
A sample answer on cell references can be: “Cell references consist of three types: Absolute, relative and mixed cell references. They are used to refer to data located in the same spreadsheet but in a different cell.
An absolute cell reference makes the data in a cell stay where it is, even if the formula used on the data is moved.
A relative reference means that when the formula is moved to another cell, the reference on the data is also changed according to the location of the moved formula.
And a mixed reference is either the row or the column of the cell is changed. All of the changes and the movement of data are represented by the dollar sign ($).”
Question 4: What is the order of mathematical operations in Microsoft Excel?
Excel’s formulas follow the sequence order of PEMDAS.
A sample answer can be: “First, Parentheses are taken into account. Then the order goes as Exponents, Multiplications, Divisions, Additions and Subtractions.”
Question 5: What are the most important functions of Excel to you as a manager?
This question is not a test to question your knowledge of Excel functions. On the contrary, due to the fact that there are not wrong or right answers to this question; it is asked to measure how you operate as a manager. Because the functions you will list as an answer to this question will show the interview where you focus in Excel. This means these will be the functions you will encourage your team to use more and more efficiently.
That is why it is important to list at least 5 functions for this question. You do not have to show off and list the most complicated formulas. Ask these questions to yourself: What are my most used functions? And what are my team’s most used functions?
A sample answer on important functions can be: “I am an avid user of LOOKUP and COUNT, INDEX MATCH and SUM functions. I believe they are one of the most important ones and the functions I, as a manager, use most often with my team.”
Question 6: With which function you can count the number of cells that have data in?
Now, rather than asking about a function and asking you to explain it; the interviewer can ask you the opposite version. They can give you the use of a function or a formula and ask you to name the function. It is important to know and get used to Excel’s functions. The interviewer and later on, your team of employees can also ask the difference between two formulas and functions as well.
A sample answer can be: “Counting all the cells that contain data is done with Excel’s COUNTA function. It scans all the rows and columns for any data and leaves out the empty cells.”
Question 7: What is the difference between the functions of COUNT and COUNTA?
They are testing you with a knowledge question, yes; however, they are also preparing you for your workflow as well. You may have to train new recruits and teach them about Microsoft Excel and its 400+ functions.
A sample answer on the difference of these two functions can be: “COUNT function only counts the cells that have numbers of data in them. On the other hand, COUNTA counts the cells that have numbers, dates, characters, text and any kind of data in it.
Question 8: What do you use a pivot table for?
Pivot tables are the best friends of any financial expert or professional. They are used a lot and they carry a lot of importance in basic data processing. As a manager, you would be using the pivot table tool as well.
A sample answer can be: “The pivot table is a built-in feature of Excel that can sum up and summarize large tables of data. It also provides reports based on the data it processed.”
Question 9: What type of reports does a pivot table provide?
Again, it is important to not only know what pivot table functions for but also how many different functions it can provide to you and your team.
A sample answer on the type of reports a pivot table offers can be: “Pivot table’s reports are presented in three forms: Compact, Outline and Tabular.
Question 10: Which types of data Excel automatically formats by itself?
As a manager, you have to know what Excel can do and cannot do by itself. This will save you time and energy. Also, your team can focus on different tasks rather than manually formatting the data they put into Excel.
A sample answer can be: “Microsoft Excel formats six types of data automatically. These are time, date, currency, percentage, fraction and scientific data.”
Question 11: What does a small red triangle in a cell mean?
These visual cues in Excel are utilized and it helps to save time and avoid any confusion between the members of a team. That is why these small details will come in handy working as a manager as well.
A sample answer can be: “A red triangle located in the upper right corner of a cell indicate that there is a note or a comment attached to that cell.”
Question 12: What is the difference between a comment and a note?
A sample answer on the difference between the two terms can be: “While you can reply to comments attached to the cells, you cannot reply to a note in Excel. The notes function more as a single annotation.
Question 13: How can you protect your data in Microsoft Excel?
If you are managing a team of employees or a department, keeping the data and information of the company safe is one of the tasks you have to accomplish. That is why it is important that you know the protection Excel provides to secure your spreadsheets.
A sample answer can be: “From the Review tab, you can choose to protect your sheet with a password. That way the spreadsheet will be password protected and cannot be opened or copied.”
Question 14: Do you think the level of protection is sufficient to store your sensitive data in an Excel spreadsheet?
This type of question shows how a manager handles the security topic. This question is specifically chosen and asked to you because you applied for the manager job position. They want to see if they can trust you with the company’s sensitive information.
A sample answer on whether the protection is good enough in Excel can be: “I would use and protect my data with a password in Excel; however, if I believe the data that I am dealing with should be checked regularly, I would not use Microsoft Excel for storage. Because Excel does not provide the information about who can access this file, what is copied and how it can be edited.”
Question 15: What are the ways to reduce the size of an Excel file?
A sample answer on reducing the Excel file size can be: “There are many different ways to achieve a small size of a file. I encourage my team to save the file in the .xlsb (Excel Binary Workbook) format. Or the other method I use is to delete all the empty cells, remove all the data formatting and unused data.”
Question 16: Why is reducing the size of an Excel file important to you as a manager?
If you are dealing with vast collection of data sets, reducing the file size would be important. You and your team can save disk space and can send the Excel files through email and drive link in a much easier way.
A sample answer can be: “By reducing the file size, I can send the files faster via email. And I would get rid of unused space in my computer’s and my department’s computers’ hard drives. It will also make opening, saving and closing the file much faster.”
Question 17: What other tools do you use to better your sales numbers as a sales manager?
If you applied for a sales manager position, you need to know additional tools and software in order to help your company’s sales numbers. These can be third party templates (such as some of Someka’s which will be detailed later in the post), other software or online tools.
A sample answer on additional tools to improve sales numbers can be: “I would use employee trackers and increase the number of invoices we send to the buyers. I would achieve these by using templates that I can use in Excel.”
Question 18: What is the importance of charts for you as a manager?
Every number is data. And each data is processed in the Excel program. For presentations, sales pitches and meetings, you can use colorful and interesting charts and graphs in order to impress the opposite party.
A sample answer can be: “Charts and graphs can make the data which consists of numbers more digestible. Data can be understood more easily if it is presented in a visual manner. That is why creating charts and graphs in Excel is an important process in order to gain profit.”
Question 19: Why do you use Microsoft Excel?
You can tell the interviewer about why using Excel is important for you as a manager, as the head of a team of employees in the finance sector. Think of all the advantages Excel offers you, but also do not forget some of the disadvantages that you wish would be improved.
A sample answer on using Excel can be: “Every business operates in Excel. There is no learning curve even for the new recruits who are not very familiar with the program. Everyone who is working in this industry has used and continues to use this software. I would be left behind if me and my team were not using it.”
Question 20: In what areas Excel can improve its user experience?
You should observe and examine all the details that you like and do not like about Excel. You can be asked to change some of the ways you use Excel in your team. That is why you should always consider functions and actions Excel (and you) can improve.
A sample answer can be: “Excel can develop new ways to protect the files in a more secure manner. Microsoft also can integrate a collaboration tool for businesses and teams such as Google Sheets.”
The Purpose of the Questions
Your technical skills and your Excel knowledge are put to the test, sure; but the feature that they are looking for in you is managerial skills. They want to see if you can manage a team in a large company and function working with Excel.
The job interview is not a written test, even though some written details about Excel and its functions can be asked. They are looking for someone who can manage and they can trust. They want to see someone in their job interview with confidence and can show trust in order to get hired.
What Type of Skills Do You Need as a Manager?
Applying to management job opportunities indicate that you are fairly advanced in your career, you gained enough experience and you are able to show your technical and soft skills in the workplace as a manager. You are expected to take responsibility for your team and the projects you develop. Then, you will be making important decisions regarding investments and sales.
You are a crucial asset to the company in order to build and operate a successful department. Your opinion will be valued and the company itself will take actions based on your evaluations and assessments. That is why you should have the necessary skills in order fulfil the requirements of the manager job position and later, advance in the company.
For example, a sales manager has to know how to calculate the weighted average of the company’s sales. Now, this does not mean the sales manager has to know the SUMPRODUCT function in Excel; this means that the sales manager needs the necessary technical skill in order to do the task.
A sales manager also needs to know how to accurately do profitability calculation for their company. Again, this is not specific to a function in Excel, they will need the pivot based on the sales numbers and calculate the company’s gross margin.
A financial analyst needs to operate different worksheets and different kinds of spreadsheets. Also, they need to combine their data together in a sheet as well. They can use third party templates such as Someka’s Financial Models in order to work with different sheets for different areas of work.
An account has to use different types of cell references such as the ones mentioned above in previous chapters, rather than utilizing hard coded values. And he or she needs to know the functions of the dollar sign ($) in Microsoft Excel
Every manager and financial expert or consultant should be used to seeing and using macros. Even though they do not have to learn and use most of the formulas and functions, they should be able to macro record and not hesitate to use them if necessary.
A manager can be the leader of a team or a department. That means there are some features they need to have or obtain in order to execute some of the tasks the position brings with it. A manager as a leader needs to be decisive and dependable. They should also be able to provide constructive criticism to its employees and be fast to bring a resolution to a conflict.
A manager also has to feel empathy for his teammates. A manager needs to mentor and motivate the new recruitments joining his team or department. And if some of the newly joined members of their team cannot develop the necessary skills in the required time, a manager has to show patience for his team members as well.
A manager also should have communication, organizational and strategy skills as well.
Excel Formulas and Functions You Need to Know as A Manager
As it is mentioned throughout this blog post, managers do not need to know all of the formulas and functions built-in to Excel. However, they need to be able to operate with these formulas and functions. Their team of employees will be using the majority of these in their daily workflow. If a problem arises or an error gets put into the data set, you (as a manager) would be able to spot that mistake and fix it with your knowledge.
That is why below you can find some of the basic functions and formulas you should learn and know how to use as a manager:
- Basic Mathematical Operations (addition, subtraction, multiplication and division)
- SUM function
- COUNT function
- AVERAGE function
- MAX and MIN functions
- Date functions
- LEN function
- COUNTA function
- ROUND function
- SUMPRODUCT function
- VLOOKUP function
- IF function
- COUNTIF function
- SUMIF function
- IS functions (ISTEXT, ISNUMBER, ISBLANK)
- CONCATENATE function
- HLOOKUP function
- RIGHT and LEFT functions
- RAND function
- IFERROR function
If you do not trust your level of knowledge on these formulas and functions, you can download and start using Someka’s Excel Formulas Training Kit. It provides you with 100 questions for 30 different training purposes in order to teach you these formulas.
It gives you clear instructions and a test module for you to practice. That means other than you, your new recruits and other employees can use this tool kit. You can track their progress with the template’s own dashboard which provides a progress meter. And it also supports multi-language formulas and functions as well. You can use the tool kit to learn how you can use Excel more efficiently even if your data is not even in English!
Pivot tables might be the tool that saves you the most time while operating in Excel. Because it summarizes an extensive and comprehensive amount of data in a matter of seconds. This means that you can understand and analyze perhaps a million rows of data in just a couple of seconds. This process is thanks to the pivot table function.
With pivot tables’ summarizing, sorting and filtering functions, it would be one of your biggest disadvantages if you cannot operate a pivot table in Excel. It is perhaps the most used function; thus, it also might be the most important tool used in the finance sector.
As a manager you should be supporting your team to use the Recommended Charts tab in Excel. Similar to the pivot table tool, it is designed to save you time and energy, and it also offers a number of different types of charts with a whole lot of customizable options. Let’s look at how many types of charts you can create in Excel:
- Column Chart
- Line Chart
- Pie Chart
- Doughnut Chart
- Bar Chart
- Area Chart
- XY (Scatter) Chart
- Bubble Chart
- Stock Chart
- Surface Chart
- Radar Chart
- Combo Chart
This means that you can create 12 different kinds of colorful and detailed charts and graphs which you can exhibit at presentations or sales pitches. It is one of Excel’s most useful and creative functions for you as a manager and for your employees.
This is a feature in which you can apply specific formatting to a cell of your choosing. This can be highlighting, emphasizing or changing the color of a cell. It is mostly used for differentiating the data on a cell from other cells.
Managers need to have a basic understanding of how this feature functions and to what purpose it can be used.
Even if you have experience in this field and applying for a ‘high title’ job position, you still should study and practice the areas you think you lack. This can be your hard (technical) or soft (social) skills. In order to get comfortable at the interview and show your confidence and enthusiasm at the same time, you need to combine your personality with the ‘things’ you know. However, at the end, your honesty, loyalty and your ability as a manager will get you hired.